HQL query with many-to-many and IN clause

[welzie]

I have an Audit object that has a IList<AuditType>. There is a many-
to-many relationship between Audit and AuditType, which means there is
an "in between table" named Audit_AuditTypes. I need to select all
the audits that are associated to certain AuditType's.

What would the HQL for this look like?

If Audit only had one reference to AuditType then I could simply do
something like this:
"Select audit from Audit audit where audit.auditType.Id in
(1,2,3,4,etc)"

I have tried this but it fails with a "Path expected for join"
exception:
"Select audit From Audit audit
join audit_auditTypes on audit_auditTypes.auditId = audit.Id
where 0 < (select count(*) from AuditType auditType where auditType.Id
= audit_auditTypes.auditTypeId and auditType.Id IN (:auditTypeIds))"

Here are the mappings:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Entity"
namespace="ClaimAudit.Entity">
<class name="ClaimAudit.Entity.Audit" lazy="false" table="audits">
<id name="Id">
<generator class="native"/>
</id>
<property name="Active" type="Boolean"/>
<property name="DateAudited"/>
<bag name="AuditTypes" table="audit_auditTypes" lazy="false">
<key column="auditId"/>
<many-to-many column="auditTypeId" class="AuditType"/>
</bag>
</class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Entity"
namespace="ClaimAudit.Entity">
<class name="ClaimAudit.Entity.AuditType" lazy="false"
table="audittypes">
<id name="Id">
<generator class="native"/>
</id>
<property name="Active" type="Boolean"/>
<property name="Name"/>
<property name="Description"/>
</class>
</hibernate-mapping>

[Graham Bunce]

Something like;

SELECT au FROM Audit au JOIN au.AuditTypes at
WHERE at.id IN (:ids)

I think

[Graham Bunce]

Oh, and unless you have no choice, try not to use the native
generator. Try HiLo or something. Native will be pain sooner or
later...

[welzie]

Thanks for the suggestion, but that creates this SQL which does NOT
join the tables using the "in between table". I end up with duplicate
results.
select audit0_.Id as Id9_,
audit0_.Active as Active9_,
audit0_.DateAudited as DateAudi3_9_,
audit0_.AuditorId as AuditorId9_,
audit0_.ClaimId as ClaimId9_,
audit0_.SpecialProjectId as SpecialP6_9_
from audits audit0_, audittypes audittype1_
where audittype1_.Id in (1,2,3,4,5)

What I really need is HQL to create SQL that looks like the below.
Any more suggestions?
select *
from audits
join audit_audittypes on audit_audittypes.auditId = audits.id
join auditTypes as at on at.id = audit_audittypes.auditTypeId
where at.id IN (1,2,3,4,5,6,7)

[Graham Bunce]

Are you sure?

Using your mappings (except I use a GUID.COMB generator instead of
Native) My SQL equivalent is:

select audit0_.Id as Id0_, audit0_.Active as Active0_,
audit0_.DateAudited as DateAudi3_0_
from audits audit0_ inner join audit_auditTypes audittypes1_
on audit0_.Id=audittypes1_.auditId
inner join audittypes audittype2_ on
audittypes1_.auditTypeId=audittype2_.Id
where audittype2_.Id in (@p0)

I'm using NH 2.1.2. I seem to recall that NH 1.2 produces different
SQL similar to yours but it still produces the right result as it
JOINs on the WHERE clause instead of via INNER JOINS (don't quote me
though - we still use NH 1.2 and I'm pretty sure the above works but I
haven't looked at it for a while)

The above SQL might well produce duplicates but usually I just add a
DISTINCT clause to the HQL

SELECT DISTINCT au FROM Audit au JOIN au.AuditTypes at WHERE at.id IN
(:ids)

which produces

select distinct audit0_.Id as Id0_, audit0_.Active as Active0_,
audit0_.DateAudited as DateAudi3_0_
from audits audit0_ inner join audit_auditTypes audittypes1_ on
audit0_.Id=audittypes1_.auditId
inner join audittypes audittype2_ on
audittypes1_.auditTypeId=audittype2_.Id
where audittype2_.Id in (@p0)

[Fabio Maulo]

http://fabiomaulo.blogspot.com/2009/09/nhibernate-queries.html

Note: SQL is one of your options

2010/1/20 welzie <wel...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

--
Fabio Maulo

[welzie]

I totally understand that native sql is one of my options. However I
need this join as part of a complex search query. I did not include
the rest of the query simply for the sake of brevity.

So it seems that join does not work for many-to-many. Is that
correct?

> > "nhusers" group.> To post to this group, send email tonh...@googlegroups.com.
> > To unsubscribe from this group, send email to>nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com>

[Fabio Maulo]

2010/1/21 welzie <wel...@gmail.com>

So it seems that join does not work for many-to-many.  Is that
correct?

If you have a failing test, with correct mappings and entities-implementation, then: Yes, that is correct.

--
Fabio Maulo

[welzie]

My latest attempt was this HQL (simplified the query to focus on the
m2m relationship):
select audit.Id
from Audit audit join audit.AuditTypes as at
where at.Id IN (1,2,3,4,5,6,7)

This was the resulting SQL (the in between table WAS referenced, but
the where clause was DROPPED!):
select audit0_.Id as col_0_0_

from audits audit0_ inner join audit_auditTypes audittypes1_ on
audit0_.Id=audittypes1_.auditId
inner join audittypes audittype2_ on
audittypes1_.auditTypeId=audittype2_.Id

[Graham Bunce]

I refer you to my previous post.

As far as I'm concerned, the HQL I provided works fine with your
mappings as I've listed the SQL that is generated. Are you doing
anything wierd? (old version of NH, using NH Fluent or something?)

[welzie]

Yes Graham you were right. I thought I had tried that and failed,
turns out it works. The below 100% works. Sorry for all the dumb
questions. I truly appreciate your time and help.

HQL:
select distinct audit
from Audit audit join audit.auditTypes at
where at.Id IN (:auditTypeIds)

Generated SQL:
select distinct audit0_.Id as col_0_0_

from audits audit0_
inner join audit_auditTypes audittypes1_ on
audit0_.Id=audittypes1_.auditId
inner join audittypes audittype2_ on
audittypes1_.auditTypeId=audittype2_.Id

where audittype2_.Id in (1,2,3,4,5,6,7);