sql - JPQL and selecting 1:n relationships using Hibernate -


i have entry menuitem has language. have device needs menuitems have language selected device. (then there accessgroup determines if device has access menuitem)

i have query works accessgroup selection, cannot think how formulate query include language selection.

simplified:

menuitem - id - published - language - accessgroup - app  device - id - list<languages> - user  accessgroup - id - universal - <list>accessgrouplink  accessgrouplink - id - device - user - accessgroup - perpetual - validto 

how do in jpql. have/tried (using play! framework):

return find("select distinct m menuitem m " +                     "inner join m.accessgroups ag " +                     "left outer join ag.accessgrouplinks agl " +                     "where m.app = ? , m.language in ? , (ag.universal = true or ((agl.device = ? or agl.user = ?) , (agl.validto >= ? or agl.perpetual = true))) , m.published = true", device.app, device.languages, device, device.user, new date()).fetch(); 

i added m.language in device.languages, doesn't work. guess have add join, cannot see how reason. appreciated.

edit: error query above is:

a play.db.jpa.jpabase$jpaqueryexception has been caught, error while executing query select distinct m menuitem m inner join m.accessgroups ag left outer join ag.accessgrouplinks agl m.app = ? , m.language in ? , (ag.universal = true or ((agl.device = ? or agl.user = ?) , (agl.validto >= ? or agl.perpetual = true)))and m.published = true: org.hibernate.propertyaccessexception: not field value reflection getter of models.language.id

but don't understand error, model.language.id part of entity.

you avoid in clause using below query

return find("select distinct m menuitem m " +                     "inner join m.accessgroups ag " +                     "left outer join ag.accessgrouplinks agl " +                     "left outer join agl.device device " +                     "left outer join device.languages language " +                     "where m.app = ? , m.language = language  , (ag.universal = true or ((agl.device = ? or agl.user = ?) , (agl.validto >= ? or agl.perpetual = true))) , m.published = true", device.app, device, device.user, new date()).fetch(); 

Comments