Hi all,

i've a problem to create a HQL statement for a M to N mapping.
Ok lets explain:

We have a table named: [account] and a table named: [coupon] between both are a m to n mapping and Hibernate generate the account_coupon crossing table. OK thats fine.

The coupon object has a account collection and a boolean member global.

The HQL select statement:

[SELECT c FROM Coupon c LEFT JOIN c._accounts a WHERE a._id = :pAccount AND c._global = false]

gives me all coupons where are not global and has a account-mapping for the accountId on crossing table.

OK, thats fine !

But which HQL statement gives me all coupon which are global (OK, that's easy) AND have NO account-mapping for the given accountId ?

This HQL-Statement don't work:

[SELECT c FROM Coupon c LEFT JOIN c._accounts a WHERE a._id != :pAccountId AND c._global = true]

The correspondent SQL statement which works show like this:

[SELECT * FROM coupon WHERE coupon_id NOT IN (SELECT coupon_id FROM coupon_account WHERE account_id = 1) AND is_global = 1]

But i can not translate it into HQL ;(

Can anybody help me ? It were very funny !

Best regards

soe

select c from Coupon c where size(c.accounts) == 0

or with a subquery, have a look in the reference documentation. I am sure that this is described.

Hi all,

it's a little bit crazy to write in german forum in english :) - but I start it, I finish with it.

OK, thanks sebastian for your answer, but it is not a realy good solution because i search for coupons which have accounts but not from the specific. Thats the reason why size == 0 don't go.

I haven't found a solution or example on hibernate.org

But I have try many select statements and at last I've do the best - I translate the "normal" sql-statement into HQL :)

Here are my solution - may be it's not the best and fastest but it works:

SELECT c FROM Coupon c WHERE c._global = true AND c._id NOT IN
(SELECT cp._id FROM Coupon cp LEFT JOIN cp._accounts a WHERE a._id = :pAccountId AND cp._global = true)

Thanks for help and reading this posting.

Best regards - and next time in german :)

soe