I have the following mapping.

<class name="Thing" table="things">

<id name="id"><generator class="native"/></id>

<list name="myStrings" cascade="all" table="thing_strings">
<key column="thing_id"/>
<list-index column="idx"/>
<element type="string" column="name"/>
</list>

</class>

I'd like to retrieve all 'Things' which have a 'myString' within a given list of strings. What I'm basically looking for is the hql to create the following sql , where 'foo','bar' is a List parameter.

select * from things t, thing_strings s where s.thing_id = t.id and s.name in ('foo','bar')

I've tried a number of queries which don't seem to work. For example, this hql generates the following sql. Strangely, if I run the sql it returns the correct result, but I get nothing back from hibernate. This query also seems like it could get very expensive considering it creates multiple subqueries.

Hql:
from Thing where 'foo' in elements(myStrings) or 'bar' in elements(myStrings)

Generated sql:
select
thing0_.id as id0_, thing0_.name as name0_ from things thing0_
where
'foo' in (select mystrings1_.name from thing_strings mystrings1_ where thing0_.id=mystrings1_.thing_id)
or 'bar' in (select mystrings2_.name from thing_strings mystrings2_ where thing0_.id=mystrings2_.thing_id)


Any suggestions on how to build this hql?





class:
-----------------
public class Thing {

private long id;
private List myStrings;

public long getId() {
return id;
}

public void setId(long id) {
this.id = id;
}

public List getMyStrings() {
return myStrings;
}

public void setMyStrings(List myStrings) {
this.myStrings = myStrings;
}
}

tables:
-----------------
CREATE TABLE `things` (
`id` bigint(20) NOT NULL auto_increment,
PRIMARY KEY (`id`)
)

CREATE TABLE thing_strings (
thing_id bigint(20) PRIMARY KEY auto_increment,
name varchar(255) NOT NULL,
idx int(11) NOT NULL,
KEY `FK_THING` (`thing_id`),
CONSTRAINT `FKD50031882E08D` FOREIGN KEY (`thing_id`) REFERENCES things (`id`)
)



Using:
-----------------
Hibernate v3.2.1
Mysql v5.0

Hi

use
sess.createSQLQuery("select t.* from Things t, thing_string s where s.thing_id = t.id and s.name in ('Thing1','Thing2')").list();


Amila
(Don't forget to rate if helps)