hql join - 连接的预期路径

发布于 2024-09-26 14:49:27 字数 1174 浏览 0 评论 0原文

我有以下 hql.如果我不尝试包含“OrderItem”实体，则效果很好。据我了解，hql 中没有“on”子句。加入 orderItem 的最佳方式是什么

 var hql = new StringBuilder();
    hql.Append(
    @"select p.Id, p.Name, p.Description, p.ProductKey, p.CustomerSku, p.ImageUrl, p.ImageThumbUrl, p.ImageLargeUrl,COUNT(oi.Quantity) as quantity 
            from ProductCampaign pc
            join pc.Product p 
            left join OrderItem oi with oi.Product.Id = p.Id
            where pc.Campaign.Id = :CampaignId ");

   hql.Append(@"group by p.Id, p.Name, P.Description, p.ProductKey, p.CustomerSku, p.ImageUrl, p.ImageThumbUrl, p.ImageLargeUrl ");
   var results = _session.CreateQuery(hql.ToString());
   results.SetParameter("CampaignId", campaignId);

这是我希望实现的 sql。

select p.Id, p.name, p.description, p.ProductKey, p.CustomerSku, p.ImageUrl, p.ImageThumbUrl, p.ImageLargeUrl,COUNT(oi.Quantity) as quantity from ProductCampaign pc
inner join Products p on pc.ProductId = p.Id
left join orderitems oi on pc.ProductId = oi.ProductId
where pc.CampaignId = 1
group by  p.Id, p.name, p.description, p.ProductKey, p.CustomerSku, p.ImageUrl, p.ImageThumbUrl, p.ImageLargeUrl

原文

I have the following hql. This works fine if i don't try and include the "OrderItem" entity. I understand that there is no "on" clause in hql. What is the best way to join orderItem

 var hql = new StringBuilder();
    hql.Append(
    @"select p.Id, p.Name, p.Description, p.ProductKey, p.CustomerSku, p.ImageUrl, p.ImageThumbUrl, p.ImageLargeUrl,COUNT(oi.Quantity) as quantity 
            from ProductCampaign pc
            join pc.Product p 
            left join OrderItem oi with oi.Product.Id = p.Id
            where pc.Campaign.Id = :CampaignId ");

   hql.Append(@"group by p.Id, p.Name, P.Description, p.ProductKey, p.CustomerSku, p.ImageUrl, p.ImageThumbUrl, p.ImageLargeUrl ");
   var results = _session.CreateQuery(hql.ToString());
   results.SetParameter("CampaignId", campaignId);

Here is the sql i wish to achieve.

select p.Id, p.name, p.description, p.ProductKey, p.CustomerSku, p.ImageUrl, p.ImageThumbUrl, p.ImageLargeUrl,COUNT(oi.Quantity) as quantity from ProductCampaign pc
inner join Products p on pc.ProductId = p.Id
left join orderitems oi on pc.ProductId = oi.ProductId
where pc.CampaignId = 1
group by  p.Id, p.name, p.description, p.ProductKey, p.CustomerSku, p.ImageUrl, p.ImageThumbUrl, p.ImageLargeUrl

分享到QQ

分享到微博

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

发布评论

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

开始看清了 2024-10-03 14:49:27

为了使用 HQL 左联接，您必须映射关系，因为需要来自“from”表之一的路径（请参阅13.3. 关联和联接)

尝试将 OrderItems.Product id 更改为正确的产品 many -to-one，并使用右连接。

作为旁注，您的实体名称应该是单数。看起来您只是将表结构复制为类。

回复收藏 0 原文

九八野马 2024-10-03 14:49:27

尝试使用 where 而不是 with：

select p.Id, p.Name, p.Description, p.ProductKey, p.CustomerSku, p.ImageUrl, p.ImageThumbUrl, p.ImageLargeUrl, COUNT(oi.Quantity) as quantity 
            from ProductCampaign pc
            join pc.Product p             
            left join OrderItem oi where oi.Product.Id = p.Id 
            and pc.Campaign.Id = :CampaignId

NHibernate 非映射连接

Try using a where instead of a with:

select p.Id, p.Name, p.Description, p.ProductKey, p.CustomerSku, p.ImageUrl, p.ImageThumbUrl, p.ImageLargeUrl, COUNT(oi.Quantity) as quantity 
            from ProductCampaign pc
            join pc.Product p             
            left join OrderItem oi where oi.Product.Id = p.Id 
            and pc.Campaign.Id = :CampaignId

NHibernate Non-Mapped Joins

回复收藏 0 原文

~没有更多了~