hibernate-devel

[Hibernate] Binary Expressions in Collections

[Christian B. <chr...@bl...>]

Gavin, the new dereferencing of Collections (my forum post) works great
in CVS. I had to comment out the new Calendar stuff to compile it, though.

But I found another query structure that is not possible currently:

I need to compare the values of a collection (a List of Strings) with a
binary operator (= > < like, and so on).

Foo has a List of Strings named Bar.

List bars = foo.getBar();

The query I need:

select foo from my.Foo
    where foo.bar.values like "baz%"

The 'values' special property doesn't exist (of course). We have the
'elements' and 'indices' special properties only (along with 'size' and
other collection related properties).

I had a look at the generated SQL queries when using 'elments' and
'indices' and doubt adding 'values' is an easy task. 

'in', 'some', 'all' and other subselect related expressions only allow
things like

select foo from my.Foo
    where "a_String" in foo.bar.elements

Of course, no substring searching is possible with this where clause.
The only solution I found is using the existing query facilities and do
substring search in memory/Java.

-- 
Christian Bauer
tu...@in...

Re: [Hibernate] Binary Expressions in Collections

[Gavin K. <ga...@ap...>]

Perhaps if you explain the actual SQL you want generated for

select foo from my.Foo
     where foo.bar.values like "baz%"

then I will understand your requirements better. As far as I can tell, what
you would really want is either

select foo from my.Foo
     where "baz%" like all foo.bar.elements

or

select foo from my.Foo
     where "baz%" like some foo.bar.elements



----- Original Message -----
From: "Christian Bauer" <chr...@bl...>
To: <hib...@li...>
Sent: Friday, November 22, 2002 9:04 AM
Subject: [Hibernate] Binary Expressions in Collections


> Gavin, the new dereferencing of Collections (my forum post) works great
> in CVS. I had to comment out the new Calendar stuff to compile it, though.
>
> But I found another query structure that is not possible currently:
>
> I need to compare the values of a collection (a List of Strings) with a
> binary operator (= > < like, and so on).
>
> Foo has a List of Strings named Bar.
>
> List bars = foo.getBar();
>
> The query I need:
>
> select foo from my.Foo
>     where foo.bar.values like "baz%"
>
> The 'values' special property doesn't exist (of course). We have the
> 'elements' and 'indices' special properties only (along with 'size' and
> other collection related properties).
>
> I had a look at the generated SQL queries when using 'elments' and
> 'indices' and doubt adding 'values' is an easy task.
>
> 'in', 'some', 'all' and other subselect related expressions only allow
> things like
>
> select foo from my.Foo
>     where "a_String" in foo.bar.elements
>
> Of course, no substring searching is possible with this where clause.
> The only solution I found is using the existing query facilities and do
> substring search in memory/Java.
>
> --
> Christian Bauer
> tu...@in...
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> hibernate-devel mailing list
> hib...@li...
> https://lists.sourceforge.net/lists/listinfo/hibernate-devel

Re: [Hibernate] Binary Expressions in Collections

[Christian B. <chr...@bl...>]

On 22 Nov (11:54), Gavin King wrote:

> then I will understand your requirements better. As far as I can tell, what
> you would really want is either
> 
> select foo from my.Foo
>      where "baz%" like all foo.bar.elements
> 
> or
> 
> select foo from my.Foo
>      where "baz%" like some foo.bar.elements

Hm, this is equivalent for this purpose. This would be the SQL
(simplified):

Table FOO { FOO_OID, BAR_OID }
Table BARS { BAR_OID, POSN, VALUE }

select a.FOO_OID from FOO a, BARS b
    where a.BAR_OID = b.BAR_OID
    and b.VALUES like "baz%"

-- 
Christian Bauer
tu...@in...