Hibernate Query

Hibernate ORM
1

I have been struggling with this query for about a week now so any help is appreciated:

SELECT MAX(h.catchDateTime), AVG(l.beakLength), AVG(l.tarsusLength), AVG(l.tarsusWidth), b.name, b.id FROM LengthMeasurements l
JOIN l.healthCheck h
JOIN h.bird b
LEFT JOIN (SELECT MAX(w.healthCheck.catchDateTime), AVG(w.weight), w.healthCheck.bird.id as bid FROM WeightMeasurements w WHERE
w.healthCheck.bird.id=1) AS x
ON x.bid = b.id
WHERE b.id=1
GROUP BY b.id, h.id ORDER BY l.healthCheck.catchDateTime DESC LIMIT 1

The query throws an error which complains about Semantics, but not more specific than that:

[2023-08-13 22:29:11] org.hibernate.query.SemanticException: A query exception occurred [SELECT MAX(h.catchDateTime), AVG(l.beakLength), AVG(l.tarsusLength),  AVG(l.tarsusWidth), b.name, b.id FROM LengthMeasurements l
[2023-08-13 22:29:11] JOIN l.healthCheck h
[2023-08-13 22:29:11] JOIN h.bird b
[2023-08-13 22:29:11] LEFT JOIN (]
[2023-08-13 22:29:11] A query exception occurred [SELECT MAX(h.catchDateTime), AVG(l.beakLength), AVG(l.tarsusLength), AVG(l.tarsusWidth), b.name, b.id FROM LengthMeasurements l
[2023-08-13 22:29:11] JOIN l.healthCheck h

In order to debug the query I tried to beak it down:
This works (I removed the inner select:

SELECT MAX(h.catchDateTime), AVG(l.beakLength), AVG(l.tarsusLength),  AVG(l.tarsusWidth), b.name, b.id FROM LengthMeasurements l
JOIN l.healthCheck h
JOIN h.bird b
WHERE b.id=1
GROUP BY b.id, h.id ORDER BY l.healthCheck.catchDateTime DESC LIMIT 1

And the INNER JOIN works also on its own:


SELECT MAX(h.catchDateTime), AVG(l.beakLength), AVG(l.tarsusLength),  AVG(l.tarsusWidth), b.name, b.id FROM LengthMeasurements l
JOIN l.healthCheck h
JOIN h.bird b
WHERE b.id=1
GROUP BY b.id, h.id ORDER BY l.healthCheck.catchDateTime DESC LIMIT 1

So what could be the issue?
Ideally I want my query to return:
| catchDateTime | AVG beakLength | AVG tarsusLength | AVG tarsusWidth | b.name | b.id | AVG weight |

MODEL SETUP

@Entity
@Data
@Table(name="bird")
public class Bird {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Long id;
    private String name;
    @Enumerated(EnumType.STRING)
    private Status status;
    @Enumerated(EnumType.STRING)
    private Sex sex;

    @OneToOne(optional = true)
    @Fetch(FetchMode.JOIN)
    @JoinColumn(name = "current_transmitter_id", nullable = true)
    private Transmitter currentTransmitter;

    @OneToOne(optional = true)
    @JoinColumn(name = "current_pit_id")
    private Pit currentPit;

    @JsonBackReference
    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "bird")
    private List<HealthCheck> listHealthCheck = new ArrayList<>();

Health Check

@AllArgsConstructor
@NoArgsConstructor
@Entity
@Data
@JsonIgnoreProperties({"hibernateLazyInitalizer", "handler"})
@Table(name="HEALTH_CHECK")
public class HealthCheck {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Long id;

    @JsonManagedReference
    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    private Bird bird;

    @JsonManagedReference
    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy="healthCheck")
    private List<Task> tasks;

    private LocalDateTime catchDateTime;

   // constructors and getters & setters

Abstract Class Task

@AllArgsConstructor
@NoArgsConstructor
@Entity
@Data
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
@Table(name="TASK")
public abstract class Task {

    @Id
    private Long taskId;

    @JsonBackReference
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    private HealthCheck healthCheck;

   // constructors and getters & setters

}

And task can have collections of either LengthMeasurements or WeightMeasurements:

WeightMeasurements

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
public class WeightMeasurements extends Task {

    private Double weight;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    private HealthCheck healthCheck;

   // constructors and getters & setters
}

And finally
LengthMeasurements

@AllArgsConstructor
@NoArgsConstructor
@Entity
@Data
public class LengthMeasurements extends Task {

    private Double beakLength;
    private Double tarsusLength;
    private Double tarsusWidth;
    private Double tarsusDepth;

   // constructors and getters & setters

Do the relationships between HeatlhCheck and Tasks need to be bi-directional?

2

With some thanks to other members of the hibernate team I got to the bottom of this.

Everything in the select statement needs an alias, i.e: MAX(w.healthCheck.catchDateTime), AVG(w.weight) becomes MAX(w.healthCheck.catchDateTime) AS weight_date, AVG(w.weight) AS avg_weight

Putting it all together this works:

    @Override
    public Object customQuery(Long id) {
        return entityManager.createQuery(
                        "SELECT MAX(h.catchDateTime), AVG(l.beakLength), AVG(l.tarsusLength),  AVG(l.tarsusWidth), b.name, b.id, weight_date, avg_weight FROM LengthMeasurements l " +
                                "JOIN l.healthCheck h " +
                                "JOIN h.bird b " +
                                "LEFT JOIN(SELECT MAX(w.healthCheck.catchDateTime) AS weight_date, AVG(w.weight) AS avg_weight, w.healthCheck.bird.id as bid FROM WeightMeasurements w WHERE w.healthCheck.bird.id=:id) AS x " +
                                "ON b.id = x.bid " +
                                "WHERE b.id=:id " +
                                "GROUP BY b.id, h.id ORDER BY l.healthCheck.catchDateTime DESC LIMIT 1")
                .setParameter("id", id)
                .getSingleResult();
    }