Hi Farrukh,
I think you are on the right track. However, I would like to propose
some changes:
- The path expression following the keyword JOIN must not contain
multiple navigations, so thing1.name.localizedString is not allowed. You
need to split it into multiple JOIN clauses. This should answer the
second question of your original email, because now each JOIN clause is
defined as an outer join.
- The pattern value following the keyword LIKE is a string literal or an
input parameter, so using the function UPPER is not allowed here. I
propose you upper case the value in Java before you pass it as an actual
parameter by calling Query.setParameter.
- Did I understand you model correct that class InternationalString has
a collection of LocalizedString instances called localizedStrings? Then
this field name need to be used in the JOIN clauses.
Please try the following:
SELECT thing FROM Thing thing
LEFT OUTER JOIN thing.name name LEFT OUTER JOIN
name.localizedStrings name_ls
LEFT OUTER JOIN thing.description desc LEFT OUTER JOIN
desc.localizedStrings desc_ls
WHERE ((:name = '%') OR (UPPER(name_ls.value) LIKE :name))
AND ((:description = '%') OR (UPPER(desc_ls.value) LIKE :description))
Hope this helps!
Regards Michael
>
> I think I have figured it out as follows:
>
> SELECT OBJECT(thing1) from
> Thing thing1 LEFT OUTER JOIN thing1.name.localizedString name_ls,
> Thing thing2 LEFT OUTER JOIN thing2.description.localizedString
> desc_ls
> WHERE
> (thing1.id = thing2.id) AND
> ((:name = '%') OR (UPPER(name_ls.value) LIKE UPPER(:name)))
> AND ((:description = '%') OR (UPPER(desc_ls.value) LIKE
> UPPER(:description)))
>
> Let me know if there is a better way. Thanks.
>
> Farrukh Najmi wrote:
>> Dear Colleagues,
>>
>> This is probably a very simple question but I am having trouble
>> coming up with the correct query.
>>
>> The Object Model for Entity classes looks as follows.
>>
>> ObjectModel for Entity Classes
>>
>>
>> I am trying to determine a parameterized query that will match all
>> things that have a name and description that match specified pattern
>> using :name and :description
>> query parameters. I want to make sure that if a Thing has 0 Name (or
>> Description) then if :name (or :description is '%') then the Thing
>> should match the query.
>>
>> I am not exactly sure how to deal with the fact that we have to
>> navigate thru multiple levels of collection attributes for each of
>> the following:
>>
>> * thing.name.localizedString
>> * thing.description.localizedString
>>
>> Also I am not sure how to cope with the possibility that:
>>
>> * this.name and this.description could be null
>> * thing.name.localizedString and
>> thing.description.localizedString could be null
>>
>> Any help on this query would be greatly appreciated. Thanks.
>> --
>> Regards,
>> Farrukh Najmi
>>
>> Web: http://www.wellfleetsoftware.com
>>
>>
>
>
> --
> Regards,
> Farrukh Najmi
>
> Web: http://www.wellfleetsoftware.com
>
>
--
Michael Bouschen
akquinet tech_at_spree GmbH
Tempelhofer Ufer 23-24, 10963 Berlin
Bülowstraße 66, 10783 Berlin
Tel.: +49/(0)30/235 520-33 Fax.: +49/(0)30/217 520-12
Geschäftsführung: Martin Weber, Hendrik Saly, Prof. Dr. Christian Roth
Amtsgericht Berlin-Charlottenburg HRB 86780 B
USt.-Id. Nr.: DE 225 964 680