如何包含针对匹配记录计数的 nHibernate 标准

发布于 2024-12-15 04:25:15 字数 871 浏览 3 评论 0原文

背景：

我有主表和明细表，M 和 D；
M 包含订单，D 包含订单详细信息以及各种尺寸（S、M、L、XL）的零件。
给定订单可能包含任意数量的零件。
95% 的所有订单至少包含一件尺寸为 S 的商品
新要求是将来可以添加尺寸，因此对 SMLXL 列表进行硬编码不再有效

我正在寻找一个返回所有订单列表 M 的 nHibernate 查询仅包含特定尺寸的零件。

换句话说，返回仅包含部分尺寸 S 的所有订单，并排除包含混合尺寸的所有订单。

我正在使用：

matching_orders.Add(
 Expression.Conjunction()
 .Add(Subqueries.WhereProperty<Orders>(o => o.OrderId).In(DetailQueryOver(S)))
 .Add(Subqueries.WhereProperty<Orders>(o => o.OrderId).NotIn(DetailQueryOver(M)))
 .Add(Subqueries.WhereProperty<Orders>(o => o.OrderId).NotIn(DetailQueryOver(L)))
 .Add(Subqueries.WhereProperty<Orders>(o => o.OrderId).NotIn(DetailQueryOver(XL)))

一定有更好的方法。像“where count(DISTINCT SIZES) = 1”之类的东西

，但我不确定如何在 nHibernate 中实现它。

建议？

原文

BACKGROUND:

I have Master and Detail tables, M and D;
M contains orders and D contains order detail with parts of various sizes (S, M, L, XL)
A given order might contain any number of parts.
95% of all orders contain at least one item of size S
New requirement is that sizes can be added in the future, so hard coding the list for SMLXL no longer works

I am looking for a nHibernate query that returns a list of all orders M that only contain parts with a specific size.

In other words, return all order containing only parts of size S, and exclude all orders that contain a mixture of sizes.

I was using:

matching_orders.Add(
 Expression.Conjunction()
 .Add(Subqueries.WhereProperty<Orders>(o => o.OrderId).In(DetailQueryOver(S)))
 .Add(Subqueries.WhereProperty<Orders>(o => o.OrderId).NotIn(DetailQueryOver(M)))
 .Add(Subqueries.WhereProperty<Orders>(o => o.OrderId).NotIn(DetailQueryOver(L)))
 .Add(Subqueries.WhereProperty<Orders>(o => o.OrderId).NotIn(DetailQueryOver(XL)))

There must be a better way. Something like "where count(DISTINCT SIZES) = 1"

But I'm not sure how to implement this in nHibernate.

Suggestions?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

当爱已成负担 2024-12-22 04:25:15

正如 dotjoe 所建议的，我相信 having 子句是通过将投影存储在临时变量中，然后在投影列表和限制列表中使用它来完成的，尽管我只使用 做到了这一点ICriteria 查询，而不是 QueryOver。

编写此查询的另一种方法是使用两个子查询 - 一个表示您要查找的大小，另一个表示所有其他大小。类似...

select *
from Orders o
where
    exists (
        select d1.Id
        from OrderDetail d1
        where
            d1.Order_id = o.Id
            and d1.Size = @size)
    and not exists (
        select d2.Id
        from OrderDetail d2
        where
            d2.Order_id = o.Id
            and d2.Size <> @size);

我们可以进一步将此答案转换为 QueryOver 查询，但我不想破坏您的乐趣。这足以让您指明正确的方向吗？

As dotjoe suggested, I believe having clauses are accomplished by stashing a projection in a temporary variable, then using it in both the projection list and the restriction list, though I've only done that with ICriteria queries, not QueryOver.

Another way to write this query is to use two subqueries - one to represent the size you are looking for, another to represent all the other sizes. Something like...

select *
from Orders o
where
    exists (
        select d1.Id
        from OrderDetail d1
        where
            d1.Order_id = o.Id
            and d1.Size = @size)
    and not exists (
        select d2.Id
        from OrderDetail d2
        where
            d2.Order_id = o.Id
            and d2.Size <> @size);

We could take this answer another step further and translate this into a QueryOver query, but I don't want to spoil your fun. Is that enough to get you pointed in the right direction?

回复收藏 0 原文

~没有更多了~