Selecting an entity from another table using a Hibernate formula

hibernate

Solution

So, I've found the solution to this particular problem. Annotations on the `latestResult` property are as follows:

@ManyToOne
@JoinColumnsOrFormulas({
    @JoinColumnOrFormula(formula=@JoinFormula(value="(SELECT r.id FROM Result r WHERE resultDate = (SELECT MAX(sqr.resultDate) FROM Result sqr WHERE sqr.test_id = id))", referencedColumnName="id")),
})
private Result latestResult;

Problem

I'm attempting to retrieve an entity from another table using Hibernate's @Formula annotation. Given the following code: ``` @Entity class Test { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "id", updatable = false, nullable = false) private Long id = null; // ... @Formula("(SELECT r FROM Result r WHERE test_id = id AND resultDate = (SELECT MAX(resultDate) FROM Result WHERE test_id = id))") private Result lastestResult; // ... public Result getLatestResult() { return latestResult; } // ... } ``` The Result class looks like this: ``` @Entity class Result { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "id", updatable = false, nullable = false) private Long id = null; @ManyToOne private Test test; // ... } ``` But upon attempting to load a Test entity, I'm getting the error 'Column "TEST0_.RESULTDATE" not found'. I've tried a few other things as well involving the @JoinFormula annotation, but without any success. I also came across this answer, which seems to indicate that what I'm doing should work, but it doesn't. How do I make this work?

Original source