Rails 5 select from two different tables and get one result

Question

I have 3 models, Shop, Client, Product.

A shop has many clients, and a shop has many products.

Then I have 2 extra models, one is ShopClient, that groups the shop_id and client_id. The second is ShopProduct, that groups the shop_id and product_id.

Now I have a controller that receives two params, the client_id and product_id. So I want to select all the shops (in one instance variable @shops) filtered by client_id and product_id without shop repetition. How can I do this??

I hope I was clear, thanks.

ps: I'm using Postgresql as database.

Answer 1

I feel like the best way to solve this issue is to use sub-queries. I'll first collect all valid shop ids from ShopClient, followed by all valid shop ids from ShopProduct. Than feed them into the where query on Shop. This will result in one SQL query.

shop_client_ids = ShopClient.where(client_id: params[:client_id]).select(:shop_id)
shop_product_ids = ShopProduct.where(product_id: params[:product_id]).select(:shop_id)
@shops = Shop.where(id: shop_client_ids).where(id: shop_product_ids)

#=> #<ActiveRecord::Relation [#<Shop id: 1, created_at: "2018-02-14 20:22:18", updated_at: "2018-02-14 20:22:18">]>

The above query results in the SQL query below. I didn't specify a limit, but this might be added by the fact that my dummy project uses SQLite.

SELECT  "shops".* 
FROM "shops" 
WHERE 
  "shops"."id" IN (
    SELECT "shop_clients"."shop_id" 
    FROM "shop_clients" 
    WHERE "shop_clients"."client_id" = ?) AND 
  "shops"."id" IN (
    SELECT "shop_products"."shop_id" 
    FROM "shop_products" 
    WHERE "shop_products"."product_id" = ?) 
LIMIT ?  
[["client_id", 1], ["product_id", 1], ["LIMIT", 11]]

Combining the two sub-queries in one where doesn't result in a correct response:

@shops = Shop.where(id: [shop_client_ids, shop_product_ids])
#=> #<ActiveRecord::Relation []>

Produces the query:

SELECT  "shops".* FROM "shops" WHERE "shops"."id" IN (NULL, NULL) LIMIT ?  [["LIMIT", 11]]

note

Keep in mind that when you run the statements one by one in the console this will normally result in 3 queries. This is due to the fact that the return value uses the #inspect method to let you see the result. This method is overridden by Rails to execute the query and display the result.

You can simulate the behavior of the normal application by suffixing the statements with ;nil. This makes sure nil is returned and the #inspect method is not called on the where chain, thus not executing the query and keeping the chain in memory.

edit

If you want to clean up the controller you might want to move these sub-queries into model methods (inspired by jvillians answer).

class Shop
  # ...
  def self.with_clients(*client_ids)
    client_ids.flatten! # allows passing of multiple arguments or an array of arguments
    where(id: ShopClient.where(client_id: client_ids).select(:shop_id))
  end
  # ...
end

Rails sub-query vs join

The advantage of a sub-query over a join is that using joins might end up returning the same record multiple times if you query on a attribute that is not unique. For example, say a product has an attribute product_type that is either 'physical' or 'digital'. If you want to select all shops selling a digital product you must not forget to call distinct on the chain when you're using a join, otherwise the same shop may return multiple times.

However if you'll have to query on multiple attributes in product, and you'll use multiple helpers in the model (where each helper joins(:products)). Multiple sub-queries are likely slower. (Assuming you set has_many :products, through: :shop_products.) Since Rails reduces all joins to the same association to a single one. Example: Shop.joins(:products).joins(:products) (from multiple class methods) will still end up joining the products table a single time, whereas sub-queries will not be reduced.

Answer 2

Just to riff on the answer provided by Prince Bansal. How about creating some class methods for those joins? Something like:

class Shop
  has_many :shop_clients
  has_many :clients, through: :shop_clients
  has_many :shop_products
  has_many :products, through: :shop_products

  class << self 

    def with_clients(clients)
      joins(:clients).where(clients: {id: clients})
    end

    def with_products(products)
      joins(:products).where(products: {id: products})
    end

  end 

end

Then you could do something like:

@shops = Shop.with_clients(params[:client_id]).with_products(params[:product_id])

By the way, I'm sure someone is going to say you should make those class methods into scopes. And you certainly can do that. I did it as class methods because that's what the Guide recommends:

Using a class method is the preferred way to accept arguments for scopes.

But, I realize some people strongly prefer the aesthetics of using scopes instead. So, whichever pleases you most.

Answer 3

Below sql query possibly gonna work for you.

-- 
-- assuming 
-- tables: shops, products, clients, shop_products, shop_clients
--  

SELECT DISTINCT * FROM shops
  JOIN shop_products 
    ON shop_products.shop_id = shops.id
  JOIN shop_clients 
    ON shop_clients.shop_id = shops.id
WHERE  shop_clients.client_id = ? AND shop_products.product_id = ?

If you'll face difficulties while creating an adequate AR expression for this sql query, let me know.

Btw, here is a mock

Answer 4

Below query will work for you.

class Shop
  has_many :shop_clients
  has_many :clients, through: :shop_clients
  has_many :shop_products
  has_many :products, through: :shop_products
end

class Client
end

class Product
end


class ShopClient
  belongs_to :shop
  belongs_to :client
end

class ShopProduct
  belongs_to :shop
  belongs_to :product
end

@shops = Shop.joins(:clients).where(clients: {id: params[:client_id]}).merge(Shop.joins(:products).where(products: {id: params[:product_id]}))