JPA GROUP BY entity - is this possible?

Question

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

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

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?

EDIT:

TraInsurance entity

@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;
    }  

}

Answer 1

Please explicitly use JOIN in this use case:

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

Answer 2

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

Answer 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.

 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.