时间戳

首页 > 解决方案 > 查找 hql 中的有序列表，最大日期值之间的最大差异

sql - 查找 hql 中的有序列表，最大日期值之间的最大差异

问题描述

我在 postgres 有一张桌子

CREATE TABLE employer_visit(
  employer_id                     INTEGER REFERENCES employer,
  visit_counter                   INTEGER NOT NULL, -- counter on current date
  visit_before_date_total_counter INTEGER NOT NULL, -- total, since service has started
  date                            DATE,
  PRIMARY KEY(employer_id, date)
);

我需要通过最近 30 天的访问来找到顶级雇主 - （employer_visit按最后一条记录中的 visit_before_date_total_counter 和 30 天前的最后一条记录之间的差异排序列表）

我试过这个 hql 查询

Query<EmployerVisit> query = getSession().createQuery(
    "SELECT ev FROM EmployerVisit ev " +
        "WHERE ev.employerVisitId.date = (SELECT MAX (groupedEv.employerVisitId.date) FROM EmployerVisit groupedEv " +
        "WHERE groupedEv.employerVisitId.employerId = ev.employerVisitId.employerId) " +
        "AND " +
        "ev.employerVisitId.date = (SELECT MAX (groupedEv2.employerVisitId.date) < :date FROM EmployerVisit groupedEv2 " +
        "WHERE groupedEv2.employerVisitId.employerId = ev.employerVisitId.employerId) " +
        "ORDER BY (groupedEv.visitCounter + groupedEv.visitBeforeDateTotalCounter) - (groupedEv2.visitCounter + groupedEv2.visitBeforeDateTotalCounter) DESC"
).setMaxResults(size).setParameter("date", calendar.getTime());

但它与

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'groupedEv.visitCounter'

因为该表groupedEv在之后不存在ORDER BY

在hibernate中可以为这种情况使用deprecated SqlQuery，但我更喜欢hql，请帮助，谢谢。

更新！- 没有 setter 和 getter 的实体和 EmbeddedId 类

@Entity
@Table(name = "employer_visit")
public class EmployerVisit {

  @EmbeddedId
  private EmployerVisitId employerVisitId;

  @Column(name = "visit_counter")
  private Integer visitCounter;

  @Column(name = "visit_before_date_total_counter")
  private Integer visitBeforeDateTotalCounter;

  public EmployerVisit() {
    employerVisitId = new EmployerVisitId();
    visitCounter = 0;
    visitBeforeDateTotalCounter = 0;
  }
}

@Embeddable
public class EmployerVisitId implements Serializable {

  @Column(name = "employer_id")
  private Integer employerId;

  @Column(name = "date")
  private Date date;

  public EmployerVisitId() {
  }
}

标签： sqlhibernatehql

解决方案

这是答案

Query<Object> query = getSession().createQuery(
            "SELECT ev, SUM(ev.visitCounter) AS counter FROM EmployerVisit AS ev " +
            "WHERE ev.employerVisitId.date > :date "         +
            "GROUP BY ev.employerVisitId.employerId, ev.employerVisitId.date " +
            "ORDER BY counter DESC").setMaxResults(size).setParameter("date", calendar.getTime());

List<EmployerVisitDto> top = new ArrayList<>();
for (Object result : query.list()) {
      Object[] dividedResult = (Object[]) result;
      EmployerVisit employerVisit = (EmployerVisit) dividedResult[0];

      EmployerVisitDto employerVisitDto = getEmployer(employerVisit.getEmployerId()).toEmployerVisitDto();
      employerVisitDto.setPeopleVisited(((Long) dividedResult[1]).intValue());
      top.add(employerVisitDto);
}
return top;

推荐阅读