Find the rows where the child collection contains all the items in the list.

Question

Given a simple blog engine that has posts and tags related to posts. There are two tables in the database: Postand Tag, as well as a table PostTagfor many-to-many relationships.

I have a list of tags and I want to find all posts that have all of these tags (so .IsIn()it doesn't work here)

Question: how can I achieve this using nhibernate? (ideally using the method .QueryOver<>())

The problem here is that I don’t even know where to start and how to implement it in pure SQL. I have 2 ideas:

• Retrieve all messages and then filter them using LINQ (i.e. using a function .IsSupersetOf())
• When using SQL WHERE EXISTSfor each item in a list

But I believe there is a more elegant way

Table structure

    CREATE TABLE Post (
        Id INT PRIMARY KEY, 
        Title NVARCHAR(255) NOT NULL
    );

    CREATE TABLE Tag (
        Id INT PRIMARY KEY, 
        Tag NVARCHAR(50) NOT NULL
    );

    CREATE TABLE PostTag (
        PostId INT NOT NULL REFERENCES Post(Id),
        TagId INT NOT NULL REFERENCES Tag(Id)
    );

    INSERT INTO Post(Id, Title) VALUES (1, 'Post A');
    INSERT INTO Post(Id, Title) VALUES (2, 'Post B');
    INSERT INTO Post(Id, Title) VALUES (3, 'Post C');

    INSERT INTO Tag(Id, Tag) VALUES (1, 'tagA');
    INSERT INTO Tag(Id, Tag) VALUES (2, 'tagB');

    INSERT INTO PostTag (PostId, TagId) VALUES (1, 1);
    INSERT INTO PostTag (PostId, TagId) VALUES (2, 2);
    INSERT INTO PostTag (PostId, TagId) VALUES (3, 1);
    INSERT INTO PostTag (PostId, TagId) VALUES (3, 2);

And I want to receive a message with identifier 3 on the specified list of tag identifier: (1, 2)

Answer 1

With Queryover, the solution should look like this:

Tag tagAlias = new Tag();
Post postAlias = new Post();

Tag tagAliasInner = new Tag();
Post postAliasInner = new Post();

var subQuery = QueryOver.Of(() => postAliasInner)
    .JoinAlias(() => postAliasInner.Tags, () => tagAliasInner)
    .Where(Restrictions.EqProperty(Projections.Property(() => postAliasInner.Id),
        Projections.Property(() => postAlias.Id)))
    .Where(Restrictions.In(Projections.Property(() => tagAliasInner.Id), ids.ToArray()))
    .Select(Projections.Count(Projections.Property(() => tagAliasInner.Id)));

var query = session.QueryOver(() => postAlias)
    .JoinAlias(() => postAlias.Tags, () => tagAlias)
    .Where(Restrictions.In(Projections.Property(() => tagAlias.Id), ids.ToArray()))
    .WithSubquery.WhereValue(ids.Count).Eq<Post>(subQuery);

var results = query.List();

As a result of SQL,

SELECT this_.Id as Id3_1_,
 this_.Title as Title3_1_,
 tags3_.Post_id as Post1_,
 tagalias1_.Id as Tag2_,
 tagalias1_.Id as Id5_0_,
 tagalias1_.Text as Text5_0_ 
FROM "Post" this_
 inner join PostTag tags3_ on this_.Id=tags3_.Post_id 
 inner join "Tag" tagalias1_ on tags3_.Tag_id=tagalias1_.Id 
WHERE tagalias1_.Id in (?, ?) 
 and ? = (SELECT count(tagaliasin1_.Id) as y0_ 
            FROM "Post" this_0_
            inner join PostTag tags3_ on this_0_.Id=tags3_.Post_id 
            inner join "Tag" tagaliasin1_ on tags3_.Tag_id=tagaliasin1_.Id 
            WHERE this_0_.Id = this_.Id and tagaliasin1_.Id in (?, ?))

Answer 2

LINQ solution (nhibernate should be able to translate it)

var tags = new[] { 1 , 2 };

var postIds = PostTags
    .Where(pt => tags.Contains(pt.TagId))
    .GroupBy(pt => pt.PostId)
    .Where(g => g.Count() == tags.Length)
    .Select(g => g.Key);

SQL solution:

SELECT PostId
FROM (
    SELECT COUNT(*) AS count, PostId
    FROM [PostTag]
    WHERE TagId IN (1, 2) --List of tags
    GROUP BY PostId
    ) as t1
WHERE [t1].[count] = 2 --Length of list

Explanation: We are filtering PostTagto include only the tags that we care about. Then we group by mail. If the number of groups is equal to the length of the tag list, then the message contains all the tags.