sql - 如何计算 NHibernate 中属于多个标签的所有帖子？

Question

我有一个多对多的关系:

• 一个帖子可以有多个标签
• 一个标签可以有多个帖子

模型:

public class Post
{
    public virtual string Title { get; set; }
    public virtual string Content{ get; set; }
    public virtual User User { get; set; }
    public virtual ICollection<Tag> Tags { get; set; }
}

public class Tag
{
    public virtual string Title { get; set; }
    public virtual string Description { get; set; }
    public virtual User User { get; set; }
    public virtual ICollection<Post> Posts { get; set; }
}

我想计算属于多个标签的所有帖子，但我不知道如何在 NHibernate 中执行此操作。我不确定这是否是最好的方法，但我在 MS SQL 中使用了这个查询:

SELECT COUNT(*) 
    FROM 
    (
    SELECT Posts.Id FROM Posts
    INNER JOIN Users ON Posts.UserId=Users.Id 
    LEFT JOIN TagsPosts ON Posts.Id=TagsPosts.PostId 
    LEFT JOIN Tags ON TagsPosts.TagId=Tags.Id 
    WHERE Users.Username='mr.nuub' AND (Tags.Title in ('c#', 'asp.net-mvc')) 
    GROUP BY Posts.Id 
    HAVING COUNT(Posts.Id)=2
    )t

但是 NHibernate 不允许在 from 子句中使用子查询。如果有人能告诉我如何在 HQL 中执行此操作，那就太好了。

Answer 1

我找到了一种无需子查询即可获得此结果的方法，该方法适用于 nHibernate Linq。这实际上并不那么容易，因为 nHibernate 支持的 linq 表达式的子集......但无论如何

查询:

var searchTags = new[] { "C#", "C++" };
var result = session.Query<Post>()
        .Select(p => new { 
            Id = p.Id, 
            Count = p.Tags.Where(t => searchTags.Contains(t.Title)).Count() 
        })
        .Where(s => s.Count >= 2)
        .Count();

它产生以下 sql 语句:

select cast(count(*) as INT) as col_0_0_ 
from Posts post0_ 
where (
    select cast(count(*) as INT)
    from PostsToTags tags1_, Tags tag2_ 
    where post0_.Id=tags1_.Post_id 
    and tags1_.Tag_id=tag2_.Id 
    and (tag2_.Title='C#' or tag2_.Title='C++'))>=2

我希望你应该能够在其中建立你的用户限制。

以下是我的测试设置和生成的随机数据

public class Post
{
    public Post()
    {
        Tags = new List<Tag>();
    }
    public virtual void AddTag(Tag tag)
    {
        this.Tags.Add(tag);
        tag.Posts.Add(this);
    }
    public virtual string Title { get; set; }
    public virtual string Content { get; set; }
    public virtual ICollection<Tag> Tags { get; set; }
    public virtual int Id { get; set; }
}

public class PostMap : ClassMap<Post>
{
    public PostMap()
    {
        Table("Posts");

        Id(p => p.Id).GeneratedBy.Native();

        Map(p => p.Content);

        Map(p => p.Title);

        HasManyToMany<Tag>(map => map.Tags).Cascade.All();
    }
}

public class Tag
{
    public Tag()
    {
        Posts = new List<Post>();
    }
    public virtual string Title { get; set; }
    public virtual string Description { get; set; }
    public virtual ICollection<Post> Posts { get; set; }
    public virtual int Id { get; set; }
}

public class TagMap : ClassMap<Tag>
{
    public TagMap()
    {
        Table("Tags");
        Id(p => p.Id).GeneratedBy.Native();

        Map(p => p.Description);
        Map(p => p.Title);
        HasManyToMany<Post>(map => map.Posts).LazyLoad().Inverse();
    }
}

测试运行:

var sessionFactory = Fluently.Configure()
    .Database(FluentNHibernate.Cfg.Db.MsSqlConfiguration.MsSql2012
        .ConnectionString(@"Server=.\SQLExpress;Database=TestDB;Trusted_Connection=True;")
        .ShowSql)
    .Mappings(m => m.FluentMappings
        .AddFromAssemblyOf<PostMap>())
    .ExposeConfiguration(cfg => new SchemaUpdate(cfg).Execute(false, true))
    .BuildSessionFactory();

using (var session = sessionFactory.OpenSession())
{
    var t1 = new Tag() { Title = "C#", Description = "C#" };
    session.Save(t1);
    var t2 = new Tag() { Title = "C++", Description = "C/C++" };
    session.Save(t2);
    var t3 = new Tag() { Title = ".Net", Description = "Net" };
    session.Save(t3);
    var t4 = new Tag() { Title = "Java", Description = "Java" };
    session.Save(t4);
    var t5 = new Tag() { Title = "lol", Description = "lol" };
    session.Save(t5);
    var t6 = new Tag() { Title = "rofl", Description = "rofl" };
    session.Save(t6);
    var tags = session.Query<Tag>().ToList();
    var r = new Random();

    for (int i = 0; i < 1000; i++)
    {
        var post = new Post()
        {
            Title = "Title" + i,
            Content = "Something awesome" + i,
        };

        var manyTags = r.Next(1, 3);

        while (post.Tags.Count() < manyTags)
        {
            var index = r.Next(0, 6);
            if (!post.Tags.Contains(tags[index]))
            {
                post.AddTag(tags[index]);
            }
        }

        session.Save(post);
    }
    session.Flush();

    /* query test */
    var searchTags = new[] { "C#", "C++" };
    var result = session.Query<Post>()
            .Select(p => new { 
                Id = p.Id, 
                Count = p.Tags.Where(t => searchTags.Contains(t.Title)).Count() 
            })
            .Where(s => s.Count >= 2)
            .Count();

    var resultOriginal = session.CreateQuery(@"
       SELECT COUNT(*) 
        FROM 
        (
        SELECT count(Posts.Id)P FROM Posts
        LEFT JOIN PostsToTags ON Posts.Id=PostsToTags.Post_id 
        LEFT JOIN Tags ON PostsToTags.Tag_id=Tags.Id 
        WHERE Tags.Title in ('c#', 'C++')
        GROUP BY Posts.Id 
        HAVING COUNT(Posts.Id)>=2
        )t
    ").List()[0];

    var isEqual = result == (int)resultOriginal;
}

正如您在最后看到的那样，我针对您的原始查询(没有用户)进行了测试，它实际上是相同的计数。