Hi Farrukh,
> Hi Michael,
>
> Thank you for your kind help. Please see inline below...
you're welcome! Please see inline below ...
>
> Michael Bouschen wrote:
>> 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.
>
> I prefer your syntax because it avoids having to declare thing1 and
> thing2 separately and then joining them on their id in WHERE clause.
> However, FWIW, I had no problems with 'thing1.name.localizedString'
> path using Hibernate EntityManager. In fact the query worked as
> expected. Are you sure that is not allowed?
Well, the Java Persistence query language as defined in the JPA spec
does not support it, so this is an extension of the Hibernate
implementation.
>
>
>> - 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.
>
> Respectfully, I believe i am doing case insensitive matching right.
> UPPER is just a string function that can work on parameter values as
> well as string literals.
> Doing it this way I guarantee that no matter what case the client
> supplies I will be able to match it in a case insensitive manner.
> I have been using this for a very long time without any problems.
Yes, the UPPER function works on an input parameter. The issue is that
the Java Persistence query language as defined in the spec does not
support UPPER as part of the LIKE pattern value. You are fine as long as
the JPA implementation you are using supports this as an extension, but
there is no guarantee that your query still compiles if you switch to a
different implementation.
>
>> - 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.
>
> That is correct.
>
>>
>> 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
>
> I get a syntax error for above line:
>
> unexpected token: desc near line 3, column 50 [ SELECT OBJECT(ro) FROM
> org.freebxml.omar.jaxb.bindings.rim._4_0.RegistryObjectType ro LEFT
> OUTER JOIN ro.name name LEFT OUTER JOIN name.localizedStrings name_ls
> LEFT OUTER JOIN ro.description desc LEFT OUTER JOIN
> desc.localizedStrings desc_ls WHERE ((:name = '%') OR
> (UPPER(name_ls.value) LIKE UPPER(:name))) AND ((:description = '%') OR
> (UPPER(desc_ls.value) LIKE UPPER(:description))) ]
>
> Note column number is for my variant of above query so its not exact.
> It is basically not liking the 'desc' after thing.description in the
> line above.
>
> Any idea why? Thanks again for your terrific help.
Oops, my bad. As you already figured out, desc is a keyword (used in an
ORDER BY clause to define descending ordering). So using a different
name is the right solution.
Regards Michael
>
>> 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