Returning Lists after checking entity names in inner entities lists with Groovy/JPQL

Support
#1

Dear community. I’m completely new to any Query Languages. I’m sorry for bothering you with my newbie (i guess) question, but I can’t understand how to make a proper JPQL request to my database.

I have an entity "a list of items",
which has a list of "one item entry",
which has an entity "item",
which eventually has a field "name".

How could i get all Lists-Of-Items that contain a certain ‘item’.name?

I’m currently stuck at using IN and MEMBER OF clauses, but can’t combine them in a proper way. My progress so far is

SELECT l FROM sample$ListOfItems AS l WHERE l.entries.item.name=?1

but i guess it doesn’t work cos entries is not an entity, but a list…

So that i created two queries:


def name = params['item'].name
def preQuery = em.createQuery('SELECT e FROM sample$ItemEntry AS e WHERE e.item.name=:name')
preQuery.setParameter("name", name)
def query = em.createQuery('SELECT l FROM sample$ListOfItems AS l WHERE l.entries IN :elems')
query.setParameter("elems", preQuery)

But i get an error as i click “Run Report” after choosing an item for a parameter.


No signature of method: com.sun.proxy.$Proxy263.createQuery() is applicable for argument types: (java.lang.String) values: [SELECT l FROM sample$ListOfItems AS l WHERE l.itemEntries IN :elems]
Possible solutions: createQuery(java.lang.String), createQuery(), createQuery(java.lang.String, java.lang.Class)

I guess that it is some kind of a synthax problem, but not sure, of course

#2

Hi Ivan,
This JPQL should work:

SELECT l FROM sample$ListOfItems l join l.entries e WHERE e.item.name=?1
1 Like
#3

does all JPQL synthax work?
Will this work fine?
select e from sample$Message e where e.receiver.id = session$userId AND e.sender.id = session$userId

#4

It will if you add colons to parameters:
select e from sample$Message e where e.receiver.id = :session$userId AND e.sender.id = :session$userId