Hi Michael,
Thank you for your kind help. 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?
> - 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.
> - 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.
> 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
>>
>>
>
--
Regards,
Farrukh Najmi
Web: http://www.wellfleetsoftware.com