nhibernate - 使用子查询进行计数

Question

因此，我在 Specialism 和 SpecialismCombo 之间存在多对多关系。我想做的是获取 ids 的 int[] 并检查是否已经存在包含这些 ids 的专业的组合。

我很接近，但不太正确。 假设我有 Id 1 和 3 的专业，我用这些专业创建了一个组合。

如果我通过 3 & 1 然后它返回预期的组合 id。

如果我传入 1，那么它会返回同时具有 1 和 3 的组合 id。

我不能只依赖于与组合相关的专业总数。因为如果一个组合有两个项目，1 和 4，并且匹配的项目是 1 和 3，我不希望它作为匹配的组合返回。

所以就像我确实需要这个结果的计数，并匹配与组合相关的总专业计数。我不太明白我是否在执行子查询或分离标准，或者如何使用 nhibernate 标准获得我想要的结果。感谢您的帮助！

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

生成的sql是：

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

编辑 - 看来我要么需要像...

HAVING count(s1_.SpecialismId) = (select count(SpecialismId) 来自specialismComboSpecialism 其中 SpecialismComboId = y0 group by SpecialismComboId) == @p2

或者也许比这更简单，我需要排除SpecalismCombos，其中combo.specialisms不在id集合中。

IE。如果组合具有专长 1 和 3，但集合只有 1.. 那么我们可以根据 3 不在集合中排除此组合...

编辑 8/8/2011 回到关注如何在 SQL 中获得我需要的结果 - 我相信这个查询是有效的。

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 CustomQuery.SpecialismComboId 
        HAVING count(scs.SpecialismId) = 2

所以现在我只需要弄清楚如何从我的 nhibernate 代码中调用这个过程并传入适当的值:)

我还在这个过程中发现我的映射类是错误的 - 因为它在映射表中放入了错误的值（即.specialismid 最终进入了specialismcomboid 领域！）

Answer 1

您的解决方案实际上应该运行良好。专业是按 id 过滤的，不应该有任何东西没有被搜索到，所以 count 应该可以工作。除非你有同样的专长加入多一次。这个 currentCriteria lastCriteria 东西看起来有点奇怪，可能有错误。只需使用 Expression.In 或 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()));

应该会产生这样的查询：

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

---

在 HQL 中也是如此

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

---

您也可以根据要查找的 ID 多次加入专业：

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

应该创建如下查询：

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

Answer 2

因此，我最终创建了一个存储过程并使用 SQL CTE，以便仅获得具有正确专业数量的专业组合。发布此内容以防其他人遇到类似的问题。

使用 nhibernate 8 个月后重新发现我忘记了很多 SQL 的东西:)

 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