Databases
StackOverflow2013
Postsdbo
PONHibernate Many to Many Criteria Task Tag Example
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONHibernate Many to Many Criteria Task Tag Example
    text
    copied!<p>I have a simple many to many relationship set up between tasks and tags. A task may have many tags and a tag may be assigned to many tasks. I use three tables to manage this - Task, Tag, and TaskTag. So far so good.</p> <p>I am trying to write a criteria query for the following example...</p> <pre><code>Find all tasks that are tagged with 'Apple' (id = 1718) AND 'Orange' (id = 1717) </code></pre> <p>I found the following example which I tried to follow.</p> <p><a href="https://stackoverflow.com/questions/4407737/nhibernate-many-to-many-criteria">NHibernate many-to-many criteria</a></p> <pre><code>// Create sample list of tag ids. var tagIDs = new List&lt;long&gt;(); tagIDs.Add(1718); tagIDs.Add(1717); // Create criteria and detached criteria. var criteria = Session.CreateCriteria&lt;Task&gt;(); var detachedCriteria = DetachedCriteria.For&lt;Task&gt;("t") .SetProjection(Projections.GroupProperty(Projections.Id())) .Add(Restrictions.Eq(Projections.Count(Projections.Id()), tagIDs.Count)) .Add(Restrictions.EqProperty("t.ID", "ID")) .CreateCriteria("Tags") .Add(Restrictions.In("id", tagIDs.ToArray&lt;long&gt;())); criteria.Add(Subqueries.Exists(detachedCriteria)); </code></pre> <p>This is the SQL it produces...</p> <pre><code>SELECT this_.ID as ID4_0_, [MORE COLS HERE] FROM dbo.[Task] this_ WHERE exists ( SELECT this_0_.ID as y0_ FROM dbo.[Task] this_0_ inner join dbo.TaskTag tags3_ on this_0_.ID=tags3_.TaskID inner join dbo.[Tag] tag1_ on tags3_.TagID=tag1_.ID WHERE this_0_.ID = this_0_.ID and tag1_.ID in (2, 1718) &lt;------ 1 GROUP BY this_0_.ID HAVING count(this_0_.ID) = 1717 &lt;------ 2 ) </code></pre> <p>For some reason I cannot clearly understand, the parameters are getting misplaced.</p> <ol> <li>Notice that it puts the count (2) in the "in" list incorrectly, and</li> <li>It then puts the second tag id where the count should go.</li> </ol> <p>When I modify the SQL to put the parameters in the correct locations, it works.</p> <p>I must have the criteria incorrect, but I cannot see it.</p> <p>I appreciate any help. Thanks!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload