Help with a HQL search of collection item field

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

Hi,

I have a parent object POF with a collection lineItems. I am attempting to issue a 1 query search that returns all POF objects where a line item's description has a likeness to a string.

The following fails with The column prefix 'li' does not match with a table name or alias name used in the query. I know why but my HQL is not good enough to express what I wish to do.

from POF as p left outer join fetch p.lineItems as li where lower(li.description) like '%abc%'

Your help appreciated!

anthony · **Posted:** Wed Nov 24, 2004 2:23 pm

Code:

from POF as p left, Line  as l
where lower(l.description) like '%abc%'
and l in elements (p.lineItems)

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

thanks :) i also found this to work

select p from package.to.POF p join p.lineItems li where lower(li.function) like '%c%'

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

hi anthony,

your own suggestion failed with

outer or full join must be followed by path expression [from com.qas.newmedia.intranet.pof.dto.POF as p left join LineItem as li where lower(li.description) like '%hib%' and li in elements(p.lineItems)]

I assume because it does not seem to be able to dereference LineItem into it's full class package path?