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

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]}))

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.