nhibernate - getting having count working with a subquery

So I have a many to many relationship between something known as Specialism and SpecialismCombo. What I'm trying to do is take an int[] of ids and check if there is a combo already that contains the specialisms with those ids.

I was close but not quite right. Say I have specialisms with Ids 1 and 3 and I create a combo with those specialisms.

If I pass in 3 & 1 then it returns the expected combo id.

If I pass in 1 then it returns the combo id that has both 1 and 3.

I can't just rely on total number of specialisms associated with the combo. Because if a combo has two items, 1 and 4 and the items being matched on are 1 and 3 I don't want this coming back as a matched combo.

So it's like I do need the count of this result, and match the count of total specialisms associated to the combo. I don't quite get whether I'm after a subquery or detatchedcriteria or how to get the result I want using nhibernate criteria. Thanks for your help!

int[] SpecialismIds = ArrayExtensions.ConvertArray<int>(idCollection.Split(new char[] { '|' }));

    ICriteria query = m_SpecialismComboRepository.QueryAlias("sc");
        query.CreateAlias("sc.Specialisms", "s", NHibernate.SqlCommand.JoinType.InnerJoin);

    ICriterion lastCriteria = null;

    foreach(int i in SpecialismIds)
    {

         ICriterion currentCriteria = Restrictions.Eq("s.SpecialismId", i);
        if (lastCriteria != null)
                        lastCriteria = Restrictions.Or(lastCriteria, currentCriteria);
                    else
                        lastCriteria = currentCriteria;
    }

    if (lastCriteria != null)
                    query.Add(lastCriteria);

    IProjection IdCount = Projections.Count("s.SpecialismId").As("IdCount");

    query.SetProjection(
        Projections.GroupProperty("sc.SpecialismComboId"),
        IdCount 
        );

    query.Add(Restrictions.Eq(IdCount, SpecialismIds.Count()));

    var comboId = query.List();

The sql being generated is:

SELECT this_.SpecialismComboId as y0_, count(s1_.SpecialismId) as y1_ 
FROM dbo.SpecialismCombo this_ 
inner join SpecialismComboSpecialism specialism3_ on this_.SpecialismComboId=specialism3_.SpecialismId 
inner join dbo.Specialism s1_ on specialism3_.SpecialismComboId=s1_.SpecialismId WHERE s1_.SpecialismId = @p0 
GROUP BY this_.SpecialismComboId HAVING count(s1_.SpecialismId) = @p1',N'@p0 int,@p1 int',@p0=3,@p1=1

EDIT - It seems like I either need the having to be something like...

HAVING count(s1_.SpecialismId) = (select count(SpecialismId) from specialismComboSpecialism where SpecialismComboId = y0 group by SpecialismComboId) == @p2

Or maybe it's simpler than that and I need to exclude SpecalismCombos where the combo.specialisms are not in the collection of ids.

Ie. if the combo has specialisms 1 and 3 but the collection only has 1.. then we could exclude this combo based on 3 not being in the collection…

Edit 8/8/2011 Went back to focusing on how to get the result I needed in SQL - and I believe this query works.

WITH CustomQuery AS
        (
        SELECT sc.SpecialismComboId,
        count(s.SpecialismId) AS ItemCount
        FROM SpecialismCombo sc 
        inner join SpecialismComboSpecialism scs on sc.SpecialismComboId = scs.SpecialismComboId
        inner join Specialism s on s.SpecialismId = scs.SpecialismId
        GROUP BY sc.SpecialismComboId 
        HAVING count(s.SpecialismId) = 2
        )

        SELECT CustomQuery.SpecialismComboId FROM CustomQuery
        INNER JOIN SpecialismComboSpecialism scs on CustomQuery.SpecialismComboId = scs.SpecialismComboId
        WHERE scs.SpecialismId in (1,4)
        GROUP BY CustomQ开发者_如何学编程uery.SpecialismComboId 
        HAVING count(scs.SpecialismId) = 2

So now I just need to figure out how to call this procedure from my nhibernate code passing in the appropriate values :)

I also discovered in the process that my mapping class was wrong - as it was putting the wrong values in the mapping table (ie. the specialismid was ending up in the specialismcomboid field !)

---

Your solution should actually work well. The specialisms are filtered by id and there shouldn't be anything left that is not searched for, so count should work. Unless you have the same specialism joined more the once. This currentCriteria lastCriteria stuff looks a bit strange, may be there is an error. Just use Expression.In or Conjunction.

IProjection IdCount = Projections.Count("s.SpecialismId").As("IdCount");

IQuery query = session
  .CreateCriteria<SpecialismCombo>("sc")
  .CreateCriteria("Specialism", "s");

  .Add(Expression.In("s.SpecialismId", SpecialismIds));

  .SetProjection(
    Projections.GroupProperty("sc.SpecialismComboId"),
    IdCount);

  .Add(Restrictions.Eq(IdCount, SpecialismIds.Count()));

Should result in a query like this:

select ...
from 
  SpecialismCombo sc 
  inner join -- linktable ...
  inner join Specialism s on ...
where
  s.SpecialismId in (1, 4)
Group By sc.SpecialismComboId
having count(*) = 2

---

The same in HQL

from SpecialismCombo sc 
  join sc.Specialism s
where s.id in (:ids)
group by sc
having count(*) = :numberOfIds

---

You could also join the specialism as many times as you have ids to find:

IQuery query = session.CreateCriteria<SpecialismCombo>("sc")

int counter = 0;
foreach(int id in ids)
{
  string alias = "s" + counter++;
  query
    .CreateCriteria("Specialism", alias);
    .Add(Expression.Eq(alias + ".SpecialismId", id));
}

should create a query like this:

select ...
from 
  SpecialismCombo sc 
  inner join -- linktable ...
  inner join Specialism s0 on ...
  inner join -- linktable ...
  inner join Specialism s1 on ...
where
  s0.SpecialismId = 1
  and s1.SpecialismId = 4

---

So I ended up creating a stored proc and using SQL CTE in order to get only the specialism combos with the correct count of specialisms. Posting this in case someone else comes across a similar issue.

Rediscovered after 8 months of using nhibernate that I'd forgotten a lot of SQL stuff :)

 DECLARE @IdCollectionCount         INT
    , @IdCollection             VARCHAR(250)
    , @CollectionDelimiter      NVARCHAR

    SET @IdCollectionCount = 2;
    SET @IdCollection = '1,4';
    SET @CollectionDelimiter= ',';

    WITH CustomQuery AS
        (
        SELECT sc.SpecialismComboId,
        count(s.SpecialismId) AS ItemCount
        FROM SpecialismCombo sc 
        inner join SpecialismComboSpecialism scs on sc.SpecialismComboId = scs.SpecialismComboId
        inner join Specialism s on s.SpecialismId = scs.SpecialismId
        GROUP BY sc.SpecialismComboId 
        HAVING count(s.SpecialismId) = @IdCollectionCount
        )

        SELECT Top 1 CustomQuery.SpecialismComboId FROM CustomQuery
        INNER JOIN SpecialismComboSpecialism scs on CustomQuery.SpecialismComboId = scs.SpecialismComboId
        INNER JOIN dbo.fn_SplitDelimited(@IdCollection,@CollectionDelimiter) AS ids
              ON scs.SpecialismId = CAST(ids.ListValue AS INT)
        GROUP BY CustomQuery.SpecialismComboId 
        HAVING count(scs.SpecialismId) = @IdCollectionCount