Hi

I am trying to put together a query with a subquery for the purpose of comparing elements of collections with elements from another collection.

The elements are of the same type. The elements in the first collections must not exist in the elements of the subquery.

Table relations:
booking -n:1- resourceGroup -n:m- resources

This is the narrowed version of my full query:

from com.yadayada.ResourceGroup as resGroup where
elements(resGroup.resources)
not in
(select elements(booking.resourceGroup.resources) from com.yadayada.Booking as booking)

Hibernate translates this to sql but I have not seen any examples of comparing the elements() in the documentation so I do not know if this will work.

I have not been able to test it since I am using MySql version 4.0.15, which doesn't support subqueries and I could not get version 4.1.0 alpha working (which supports subqueries). A stable version of 4.1.0 seems to be months away so I would be grateful if someone could help me rewrite the query to a simple query.


My full query looks like this:

from com.yadayada.ResourceGroup as resGroup where
resGroup.resourceCategory.resourceCategoryId = :paramCategory AND elements(resGroup.resources)

not in

(select elements(booking.resourceGroup.resources) from com.yadayada.Booking as booking where

((booking.startingDate > :paramStartingDate) AND (booking.startingDate < :paramEndingDate) OR
(booking.startingDate < :paramEndingDate) AND (booking.endingDate > :paramEndingDate) OR (booking.endingDate > :paramStartingDate) AND (booking.endingDate < :paramEndingDate)))

Thank you
johan