Hi,

I feel like I’m missing something here. I’m trying to use the criteria API to perform a query on a many to many association. I have a many to many relationship between Listing and Area. At the start of my query, I have a collection of Area objects. I want to simply return all listings that are located in any of these areas.

Logically I want:
from Listing l where l.areas in (:areas)

Where the :areas parameter would be replace by a collection of Area objects. If I were writing it in SQL, it would be:

select l.*
from Listing l inner join Listing_Area la on l.listing_id = la.listing_id
where la.area_id in (?,?,?,?)

I’ve tried several variations. Here are some below:

Variation 1:

Variation 2:



Hibernate version:
2.1.8

Mapping documents:
Listing.hbm.xml:
<hibernate-mapping>
<class name="Listing" table="LISTING">
<id name="id" column="LISTING_ID" type="int">
<generator class="seqhilo">
<param name="sequence">LISTING_SEQ</param>
<param name="max_lo">25</param>
</generator>
</id>
<set name="areas" lazy="false" table="LISTING_AREA">
<key column="LISTING_ID"/>
<many-to-many class="Area" column="AREA_ID"/>
</set>
</class>
</hibernate-mapping>

Area.hbm.xml:
<hibernate-mapping package="com.cleanoffer.domain.mls">
<class name="Area" table="AREA">
<id name="id" column="AREA_ID">
<generator class="assigned"/>
</id>
</class>
</hibernate-mapping>



Full stack trace of any exception that occurs:

Name and version of the database you are using:
Oracle 10g

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

I tried the following in HQL:



This query appeared to work fine. I can't get the same query to work with the criteria API. Any suggestions?

Hi All,

I am having the same problem too. I have a class Field which has a many-to-many relationship to a class Datasource. I have tried to write the criteria queries the same way. Does anyone know how to do a in query with a many-to-many association?

Thanks,
Kevin

Use
or


I'm not sure the exact difference between the two. It seems that if the list doesn't duplicate objects, they act the same.

Hi all!

I think this Criteria can help you



You can read Hibernate In Action for more infomation

Cheers :)

Hm, i have nearly the same problem, the setResultTransformer didn't help any.

I use something like:



Which does not work at all, giving an error like "didn't pass a value for parameter 1"

But when i do something like



where the longs are the primary keys for the area, it works perfectly, but its not very usefull (have to traverse through the set to get an array of the keys ...)

[/code]

Did you try setResultTransformer with DISTINCT_ROOT_ENTITY? I was only using ROOT_ENTITY until some cases came up in which is still had duplicates. I changed to DISTINCT_ROOT_ENTITY and its been working fine.