JPA GROUP BY实体 - 这可能吗？

Is possible to select data in JPA with grouping by referenced entity?

是否可以通过引用实体进行分组来选择JPA中的数据？

I mean: I have two entities - insurance and referenced many-to-one vehicle. Insurance entity has validTill field (and vehicle field of course).

我的意思是：我有两个实体 - 保险和参考多对一车辆。保险实体有validTill字段（当然还有车辆字段）。

I'd like to select vehicle and it's latest insurance. The query below doesn't work:

我想选择车辆，这是最新的保险。以下查询不起作用：

SELECT DISTINCT v.vehicle, 
                max(v.validTill) as lastValidTill 
FROM TraInsurance v 
     GROUP BY v.vehicle 
     ORDER BY lastValidTill

The query above fails with error:

上面的查询失败并显示错误：

ERROR: column "travehicle1_.id_brand" must appear in the GROUP BY clause or be used in an aggregate function

This is because JPA adds all fields from referenced vehicle to query and not to GROUP BY. Is here something I do wrong? Or maybe it's just not possible to do this?

这是因为JPA将引用车辆的所有字段添加到查询而不是GROUP BY。这是我做错了吗？或者也许只是不可能这样做？

EDIT:

编辑：

TraInsurance entity

TraInsurance实体

@Entity
@Table(name = "TRA_INSURANCES", schema="public")
@SequenceGenerator(name = "TRA_INSURANCES_SEQ", sequenceName = "TRA_INSURANCES_SEQ", allocationSize = 1)
public class TraInsurance implements EntityInt, Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "id", nullable = false)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "TRA_INSURANCES_SEQ")
    private Long                    id;

    @NotNull
    @ManyToOne
    @JoinColumn(nullable = false, name = "id_vehicle")
    private TraVehicle              vehicle;

    @NotNull
    @Column(name = "valid_from", nullable = false)
    private Date                    validFrom;

    @Column(name = "valid_till", nullable = false)
    private Date                    validTill;

    @NotNull
    @ManyToOne
    @JoinColumn(nullable = false, name = "id_company")
    private Company                 company;

    @Column(name = "policy_no", nullable = true, length = 50)
    private String                  policyNumber;

    @Column(name = "rate", nullable = true, precision = 12, scale = 2)
    private BigDecimal              rate;

    @Column(name = "discount_percent", nullable = true)
    private Float                   discountPercent;

    @Column(nullable = true)
    private String                  description;    

    public TraInsurance() {}

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public TraVehicle getVehicle() {
        return vehicle;
    }

    public void setVehicle(TraVehicle vehicle) {
        this.vehicle = vehicle;
    }  

    public Date getValidFrom() {
        return validFrom;
    }

    public void setValidFrom(Date validFrom) {
        this.validFrom = validFrom;
    }

    public Date getValidTill() {
        return validTill;
    }

    public void setValidTill(Date validTill) {
        this.validTill = validTill;
    }

    public Company getCompany() {
        return company;
    }

    public void setCompany(Company company) {
        this.company = company;
    }

    public String getPolicyNumber() {
        return policyNumber;
    }

    public void setPolicyNumber(String policyNumber) {
        this.policyNumber = policyNumber;
    }

    public BigDecimal getRate() {
        return rate;
    }

    public void setRate(BigDecimal rate) {
        this.rate = rate;
    }

    public Float getDiscountPercent() {
        return discountPercent;
    }

    public void setDiscountPercent(Float discountPercent) {
        this.discountPercent = discountPercent;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        result = prime * result
                + ((validFrom == null) ? 0 : validFrom.hashCode());
        result = prime * result + ((vehicle == null) ? 0 : vehicle.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (!(obj instanceof TraInsurance))
            return false;
        TraInsurance other = (TraInsurance) obj;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        if (validFrom == null) {
            if (other.validFrom != null)
                return false;
        } else if (!validFrom.equals(other.validFrom))
            return false;
        if (vehicle == null) {
            if (other.vehicle != null)
                return false;
        } else if (!vehicle.equals(other.vehicle))
            return false;
        return true;
    }  

}

3 个解决方案

#1

Please explicitly use JOIN in this use case:

请在此用例中明确使用JOIN：

SELECT ve, MAX(v.validTill)
FROM TraInsurance v JOIN v.vehicle ve
GROUP BY ve
ORDER BY MAX(v.validTill)

#2

Apparently the JPA spec allows that but at least Hibernate's implementation does not support it (see HHH-2436 and HHH-1615).

显然JPA规范允许，但至少Hibernate的实现不支持它（参见HHH-2436和HHH-1615）。

#3

If you pass an entity inside the GROUP BY, Hibernate automatically adds its id to the transformed SQL of the underlying DB. In addition, the values in the GROUP BY must exist in the SELECT clause. Thus, instead of select the whole object, you can select its id, then from those ids, you can retrieve the object again.

如果在GROUP BY中传递一个实体，Hibernate会自动将其id添加到底层数据库的转换后的SQL中。此外，GROUP BY中的值必须存在于SELECT子句中。因此，您可以选择其ID，而不是选择整个对象，然后从这些ID中，您可以再次检索该对象。

 SELECT DISTINCT v.vehicle.id, max(v.validTill)
 FROM TraInsurance v 
 GROUP BY v.vehicle.id
 ORDER BY max(v.validTill)

If it takes time and requires DB hits to retrieve Vehicle objects from their ids, you can select all of Vehicle's attributes in the SELECT and put them in the GROUP BY. Then you can construct the Vehicle object from those attributes without accessing to DB.

如果需要时间并且需要DB命中从其ID中检索Vehicle对象，则可以在SELECT中选择所有Vehicle的属性并将它们放入GROUP BY中。然后，您可以从这些属性构造Vehicle对象，而无需访问DB。

#1