如何调整ActiveRecord的（或直SQL）查询，以包括零计数记录在一个WHERE子句JOIN [英] How to adjust ActiveRecord (or straight SQL) query to include zero count records on a JOIN with a WHERE clause

问题描述

我有一个关于形成ActiveRecord的查询问题，而且还提供了SQL对于那些不熟悉的ActiveRecord。

我有以下的模型类：

 类鞋＆LT;的ActiveRecord :: Base的
  的has_many：采购

  高清self.available_shoes
    #show已超过num_in_stock购买更少的所有鞋
    num_in_stock = 3
    Shoe.includes（：购买）
        。集团（purchases.shoe_id）
        .having（COUNT（purchases.shoe_id）＆LT;，num_in_stock）
  结束

  高清self.available_shoes_for_user（用户）
    #show一个用户尚未购买的所有available_shoes
    Shoe.available_shoes.where（purchases.user_id！=？，user.id）
  结束
结束
 

方法 Shoe.available_shoes 按预期工作，它会返回已购买更少的时间比在现货供应量所需的全部鞋（在本例中3），包括已购买的零次鞋。

当我打电话 Shoe.available_shoes_for_user（用户），它会显示有现有的所有购买鞋子的问题就出现了，但它并不显示具有零可用鞋购买。

我已经提取的原始SQL如下：

 ＃Shoe.available_shoes
。选鞋*，*购买从鞋子LEFT OUTER JOIN购买ON purchases.shoe_id = shoes.id GROUP BY shoe_id HAVING COUNT（purchases.shoe_id）＆LT; 3

＃Shoe.available_shoes_for_user（User.find（5））
。选鞋*，*购买从鞋子LEFT OUTER JOIN购买ON purchases.shoe_id = shoes.id WHERE GROUP BY shoe_id HAVING COUNT（purchases.shoe_id）LT（purchases.user_id = 5！）; 3
 

问题1：我怎样才能获得 Shoe.available_shoes_for_user（用户）工作作为购买不到3意图（即，显示所有的鞋倍（包括零倍），该客户端没有已经购买？

问题2：从长远来看，这将是对这一问题的最佳解决方案时，有几十万/百万鞋

在此先感谢！

============================ ：一种解决方案：（仅在MySQL中没有PostgreSQL的作品） 感谢@Frederick祥为指向的方式

 类鞋＆LT;的ActiveRecord :: Base的
  的has_many：采购

  高清self.available_shoes
    #show已超过num_in_stock购买更少的所有鞋
    num_in_stock = 3
    Shoe.includes（：购买）
        。集团（purchases.shoe_id）
        .having（COUNT（purchases.shoe_id）＆LT;，num_in_stock）
  结束

  高清self.available_shoes_for_user（用户）
    #show一个用户尚未购买的所有available_shoes
    Shoe.available_shoes
         .joins（LEFT OUTER JOIN购买purchased_by_user ON purchased_by_user.shoe_id = shoes.id和purchased_by_user.user_id ='＃{user.id}'）
         。凡（purchased_by_user.id IS NULL）
  结束
结束
 

解决方案

如果没有购买的鞋，在左连接意味着鞋企的结果集将有NULL从采购表中的所有列。

您要申请一个，其中purchases.user_id！= 5 子句该用户删除购买但也过滤掉空行。你可以改变这种状况，以

 其中purchases.id为空或purchases.user_id！= 5
 

但我认为这仍然不会做你想要什么：如果鞋子已经由客户和其他一些客户已经购买了此只想让报告的数量是1，而不是2，而不是完全删除的行

您可以通过加入购表第二次​​做到这一点。

 左外连接的购买上purchases.shoe_id = shoes.id
左外连接购买purchased_by_user上purchased_by_user.shoe_id = shoes.id和purchased_by_user.user_id = 5
 

您where子句则需要只是确保 purchased_by_user.id为null ，表示该数据库找不到购买该鞋与USER_ID

I have a question about forming a query in ActiveRecord but also provided the SQL for those that aren’t familiar with ActiveRecord.

I have the following model class:

class Shoe < ActiveRecord::Base
  has_many :purchases

  def self.available_shoes
    #show all shoes that have been purchased less than num_in_stock
    num_in_stock = 3
    Shoe.includes(:purchases)
        .group("purchases.shoe_id")
        .having("COUNT(purchases.shoe_id) < ?", num_in_stock)
  end

  def self.available_shoes_for_user(user)
    #show all available_shoes that a user hasn’t already purchased
    Shoe.available_shoes.where("purchases.user_id != ?", user.id)
  end
end

The method Shoe.available_shoes works as expected in that it will return all shoes that have been purchased less times than the amount available in stock (in this case 3) including shoes that have been purchased zero times.

The problem arises when I call Shoe.available_shoes_for_user(user), it will show all shoes that have existing purchases, but it doesn’t show available shoes that have zero purchases.

I have extracted the raw SQL below:

#Shoe.available_shoes
SELECT shoes.*, purchases.* FROM shoes LEFT OUTER JOIN purchases ON purchases.shoe_id = shoes.id GROUP BY shoe_id HAVING COUNT(purchases.shoe_id) < 3

#Shoe.available_shoes_for_user(User.find(5))
SELECT shoes.*, purchases.* FROM shoes LEFT OUTER JOIN purchases ON purchases.shoe_id = shoes.id WHERE (purchases.user_id != 5) GROUP BY shoe_id HAVING COUNT(purchases.shoe_id) < 3

Question 1: How can I get Shoe.available_shoes_for_user(user) to work as intended (ie, show all shoes purchased less than 3 times (including zero times) that the client hasn’t already purchased?

Question 2: In the long run, what would be the optimal solution to this problem when there are hundreds of thousands/millions of shoes?

Thanks in advance!

============================ A SOLUTION (only works in MySQL not PostgresSQL) Thanks to @Frederick Cheung for pointing the way

class Shoe < ActiveRecord::Base
  has_many :purchases

  def self.available_shoes
    #show all shoes that have been purchased less than num_in_stock
    num_in_stock = 3
    Shoe.includes(:purchases)
        .group("purchases.shoe_id")
        .having("COUNT(purchases.shoe_id) < ?", num_in_stock)
  end

  def self.available_shoes_for_user(user)
    #show all available_shoes that a user hasn’t already purchased
    Shoe.available_shoes
         .joins("LEFT OUTER JOIN purchases purchased_by_user ON purchased_by_user.shoe_id = shoes.id AND purchased_by_user.user_id = '#{user.id}'")
         .where("purchased_by_user.id IS NULL")
  end
end

解决方案

If there are no purchases for a shoe, the left join means that for that shoe the result set will have NULL for all the columns from the purchases table.

You're applying a where purchases.user_id != 5 clause to remove purchases by that user but that is also filtering out the NULL rows. You could change that condition to

where purchases.id is null or purchases.user_id != 5

But i think this will still not do what you want: if a shoe had been purchased by that customer and by some other customer this would just make the reported count be 1 rather than 2 instead of removing the row altogether

You could do this by joining the purchases table a second time

left outer join purchases on purchases.shoe_id = shoes.id
left outer join purchases purchased_by_user on purchased_by_user.shoe_id = shoes.id and purchased_by_user.user_id = 5

Your where clause then needs to just ensure that purchased_by_user.id is null, signifying that the database could find no purchases for that shoe with that user_id

这篇关于如何调整ActiveRecord的（或直SQL）查询，以包括零计数记录在一个WHERE子句JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！