How to write Hibernate Session Query JOIN Unrelated Entities on Field with ManyToOne mapping

Question

I have a grails 3.3.2 project I am working on using Hibernate 5.1.5.Final, and I have mapped some JPA entity classes to existing Oracle tables. I can create a session Query using Hibernate and have it return results of a query on two tables without any issue if the second entity doesn't reference the first as a ManyToOne joined column mapping. If I create the entity with that column as a ManyToOne mapping instead of just a String I get errors when trying to run the query.

Here is the error when trying to execute the Query.

Caused by: org.hibernate.hql.internal.ast.InvalidWithClauseException: with clause can only reference columns in the driving table [select t.term as term from edu.utica.jpa.entity.general.validation.Term t JOIN edu.utica.jpa.entity.ar.MealPlanDeadline pd ON pd.term = t.term where pd.deadline is not null]

Here is the query in it's current form, this works if the "pd.term" is mapped as a string instead of a ManyToOne relationship in its entity class.

  List getMealPlanTerms() {

    List terms = []
    Term.withSession{ session ->
        Query query = session.createQuery("""select t.term as term
                                               from Term t
                                                    JOIN MealPlanDeadline pd ON pd.term = t.term
                                              where pd.deadline is not null""")
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE)
        terms = query.list()
    }
 }

Current mapping of term in the MealPlanDeadline entity class

@ManyToOne
@JoinColumns([
        @JoinColumn(name = "TYRCMPD_TERM", referencedColumnName = "STVTERM_CODE")
])
Term term

If this is instead mapped like, the query works and returns what I would expect.

@Column(name = "TYRCMPD_TERM")
String term

Does anyone have any ideas on how to get this to work with a ManyToOne style join column?

Thank you

I just re-wrote this with the tables/classes the other direction and it worked. But I am not entirely sure why it works this way and not the other. Probably something to do with the ManyToOne mapping on the MealPlanDeadline class. In SQL both directions would work fine, so this almost seems like a bug in hibernate. Or just goofy behavior.

    List getMealPlanTerms() {

    List terms = []
    Term.withSession{ session ->
        Query query = session.createQuery("""select t.term as term
                                                   ,t.description as description
                                                   ,t.startDate as startDate
                                                   ,t.endDate as endDate
                                                   ,t.aidYear as aidYear
                                                   ,t.acyr as acyr
                                                   ,t.id as id
                                                   ,t.version as version
                                               from MealPlanDeadline pd
                                                    JOIN Term t ON t.term = pd.term
                                              where pd.deadline is not null   
                                              """)
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE)
        terms = query.list()
    }
}

Answer 1

Have you tried this?

    Query query = session.createQuery("""select t.term as term
                                               ,t.description as description
                                               ,t.startDate as startDate
                                               ,t.endDate as endDate
                                               ,t.aidYear as aidYear
                                               ,t.acyr as acyr
                                               ,t.id as id
                                               ,t.version as version
                                           from MealPlanDeadline pd
                                                JOIN Term t ON t.term.id = pd.term.id
                                          where pd.deadline is not null   
                                          """)

If that doesn't work, you will have to use Hibernate 5.2+ which adds supports for that kind of usage.