sql.netnhibernateicriteria

NHibernate many-to-many criteria


I have a list of questions, each linked to a list of tag.

And the following data :

Question1 : Tag1
Question2 : Tag1, Tag2
Question3 : Tag1, Tag2, Tag3
Question4 : Tag1, Tag3

The following criteria :

var tagsIds = new int[] { tag1, tag2 };

var res = session.CreateCriteria<Question>()
    .CreateCriteria( "Tags" )
    .Add( Restrictions.In( "id", tagsIds ) )
    .List<Question>();

returns (I understand why, the "in" acts like an OR)

Question1, Question2, Question3, Question4

Or I would like to get only

Question2, Question3

as they both have tag1 AND tag2. I there a way to do it ?

In SQL, I would do something like :

SELECT *
FROM Question q
WHERE EXISTS (
    SELECT *
    FROM QuestionsToTags qtt
    WHERE qtt.Question_id = q.Id
    AND qtt.Tag_id IN ( 1, 2 )
    GROUP BY qtt.Question_id
    HAVING COUNT( qtt.Question_id ) >= 2 
)

Solution

  • Using hql :

    var q = NHibernateSession.CreateQuery(
    @"from Question question 
        where exists( 
            select q.id from Question q
            join q.Tags t
            where 
                t.id in (:ids)
                and q.id = question.id
            group by q.id
            having count(t.id)=:c_count )");
    
    q.SetParameterList("ids", tagIds);
    q.SetInt32("c_count", tagIds.Length);
    

    And using an ICriteria :

    // here is the exists part
    var dCriteria = DetachedCriteria.For<Question>("q")
        .SetProjection(Projections.GroupProperty(Projections.Id()))
        .Add(Restrictions.Eq(Projections.Count(Projections.Id()), tagIds.Length))
        // here we filter on the "parent" criteria
        .Add(Restrictions.EqProperty("q.id", "question.Id"))
        .CreateCriteria("Tags")
        .Add(Restrictions.In("id", tagIds));
    
    var crit = NHibernateSession
        .CreateCriteria<Question>("question")
        .Add(Subqueries.Exists(dCriteria));