Как настроить запрос ActiveRecord (или прямой SQL) для включения записей нулевого счета в JOIN с предложением WHERE

У меня вопрос о формировании запроса в ActiveRecord, но также предоставлен SQL для тех, кто не знаком с ActiveRecord.

У меня есть следующий класс модели:

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 

Метод Shoe.available_shoes работает так, как ожидается, в том, что он вернет все туфли, которые были приобретены меньше, чем количество, доступное на складе (в данном случае 3), включая туфли, которые были приобретены в нулевое время.

Проблема возникает, когда я вызываю Shoe.available_shoes_for_user(user) , она покажет все туфли, у которых есть существующие покупки, но она не показывает доступные ботинки, у которых есть нулевые покупки.

Я извлек необработанный SQL ниже:

 #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 

Вопрос 1: Как я могу заставить Shoe.available_shoes_for_user(user) работать в соответствии с назначением (т. Shoe.available_shoes_for_user(user) Показывать все туфли, приобретенные менее 3 раз (включая нулевые), которые клиент еще не приобрел?

Вопрос 2: В конечном итоге, каким будет оптимальное решение этой проблемы, когда есть сотни тысяч / миллион обуви?

Заранее спасибо!

=====================================================================================================================================================================================================================================================

 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 

Если для обуви нет покупок, левое соединение означает, что для этой обуви результирующий набор будет иметь NULL для всех столбцов из таблицы покупок.

Вы применяете предложение where purchases.user_id != 5 , чтобы удалить покупки этого пользователя, но также фильтрует строки NULL. Вы можете изменить это условие на

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

Но я думаю, что это все равно не будет делать то, что вы хотите: если обувь была куплена этим клиентом и каким-то другим клиентом, это просто сделало бы сообщенный счетчик равным 1, а не 2 вместо того, чтобы полностью удалить строку

Вы могли бы это сделать, присоединившись к таблице покупок во второй раз

 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 

В этом случае предложение where должно просто гарантировать, что файл purchased_by_user.id is null , что означает, что база данных не может найти никаких покупок для этой обуви с помощью этого user_id