ruby on rails - How to adjust ActiveRecord (or straight SQL) query to include zero count records on a JOIN with a WHERE clause -

I have a question about creating a query in ActiveRecord but for those who have been provided with SQL Are not ActiveRecord

I have the following model squares:

  square shoe < ActiveRecord :: Base is available: buy def self.available_shoes # Show all the shoes that have been purchased num_in_stock num_in_stock = less than 3. Shoeine includes (purchase) .group ("Buy Shoe_id"). Howing ("COUNT (purchases.shoe_id)", "Num_in_stock"), End DIF self.available_shoes_for_user (user) # All available_shops show that a user Has already purchased shoe ".vailable_shore." ("Purchases.user_id! =?", User.id) End of End   
 method  Shoe.available_shoes  Works as expected that it will return all the shoes that have been purchased in less than the amount available in the stock (case 3) You have purchased it.  
 When I call  Shoe.available_shoes_for_user (user) , then this problem occurs, they will show all the shoes which are current purchases, but it is available shopping shoes Is not available which do not have zero purchases.  
 I have removed the raw SQL below:  
  #Shooter. Available. *, Purc Swan. * Join the purchase on the upper outs purchase from shoes. Shoe_id = Shoes.id Shoes by Shoes iiDeX (Buy Shoe_id) & lt; 3 # shoe.available_short_for_user (user.fund (5)) Choose shoes. *, Shopping. * Get involved in shopping on top outreach purchases from shoes. Shoe_id = shoes.id where (buy user_id! = 5) Shoes IID Hoving COUNT (purchase. Shoe_id) by group & lt; 3   
  Question 1:  How can I get the  Shu.available_short_for_user (user) , working as (i.e., Purchased all the shoes less than 3 times (including zero times) that the customer has not already bought?  
  Question 2:  In the long run, will it be the optimum solution when this problem is hundreds Thousands / millions of shoes?  
 Thanks in advance!  
 =========================== =  One Solution  (Only in MySQL, PostGraseQ Thank you for the way Frederick Cheung to point out  
  square shout  Active Records :: near the base_memo: buy DF self.available_shoes # All shoes that Num_in_stock num_in_stock = less than 3 shoe.includes (purchase) group ("purchase. Shoe_id"). ("COUNT (purchases.shoe_id)", "Num_in_stock"), end dif self.available_shoes_for_user (user) Show all available_shops that a user has already purchased Shoe.available_ shoes ("Buy OUTER left Bought purchases purchased by purchased by the buyer _ Bay_ugr. Shao_ID = Boots.Edit and purchasedAdvertiser.User_ID = '# {user.id}' "). Where (" purchased_by_user.id IS NULL ") Ending    
 < If there is no purchase for shoe, then the left joining means that the result for that shoe will be set to zero for all the columns from the purchase table.  
 You are applying a , where the purchase.User_ID! = 5  section is for the user to remove the purchase, but also filter the tap rows. You can change that condition to  
 , where the purchases are empty or are purchased. User_id! 5 =   
 But I think this Still would not want to do it for you: If a shoe was purchased by that customer and by another customer, it would be 1 rather than 2 instead of the reported count 2 instead of 2  
 You can do this by joining the purchase table for a second time  
  Purchase left outer engagement on R. Shoe_id = shoes.id External shipping purchase left by the buyer purchased by the purchase purchased was purchased. Bid.id and buy_by_user.user_id = 5   
 Your section where only the need to ensure that  purchased_by_user.id is empty , indicates that the database With that user no one could afford to buy that shoe