Search code examples
sqlhibernateplayframeworkjpql

Selecting items in a ManytoMany relationship with JPQL/Hibernate

How can I easily select records of model Article where the Category is a category with id x. There is a ManytoMany relationship between Article and Category. (the link field int eh Article model is categories.)

I have to build the query, as it provides a list with available articles according to a set of variables. E.g. I tried something like (but clearly doesn't work):

    if(categoryFilter != null){
        sql += "ANY categories.id = " + categoryFilter.id + " AND " ;
    }

And then for the query:

("site.app = ? AND " + sql + "removed = 0", app)

I have done it before but cannot remember. I am using Hibernate on the Play! Framework.

Solution 
  • select article from Article article
inner join article.categories category
where category.id = :categoryId

    See the Hibernate documentation on HQL and associations and joins.