查找子集合包含列表所有元素的行

本文关键字：元素 子集合 包含 列表 查找 | 更新日期: 2023-09-27 18:30:25

给定简单的博客引擎，其中包含与帖子关联的帖子和标签。数据库中有 2 个表：Post 和 Tag 表，也PostTag表用于多对多关系。

我有标签列表，我想找到所有具有所有这些标签的帖子（所以.IsIn()在这里不起作用）

问题：如何使用 nhibernate 实现它？（最好使用.QueryOver<>()方法）

这里的问题是我什至不知道从哪里开始以及如何在纯SQL中实现它。我有两个想法：

  1. 获取所有帖子，然后使用 LINQ 过滤它们（即使用 .IsSupersetOf() 函数）
  2. 在 SQL 中，对列表中的每个项目使用WHERE EXISTS

但我相信还有更优雅的方式

表结构 

    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);

我想通过给定的标签 id 列表获得 id 为 3 的帖子：（1， 2）

查找子集合包含列表所有元素的行

使用 Queryover 解决方案应如下所示：

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();

这导致 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 (?, ?))

LINQ 解决方案（nhibernate应该能够翻译它）

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 解决方案：

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

说明：我们过滤PostTag，只包含我们关心的标签。然后我们按邮寄分组。如果分组计数等于标签列表的长度，则帖子包含所有标签。

相关文章：