I have a Client model and a Product model where a Client has many Products and a Product belongs to a CLient.
I need to find a query that only returns Clients if they have a record in the Product table
clients table
id | name
--------------
1 | Company A
2 | Company B
3 | Company C
products table
id | name | client_id
---------------------------
1 | Product A | 1
2 | Product B | 1
3 | Product C | 3
4 | Product D | 3
5 | Product E | 1
I only need Clients 1 3
For example something like
@clients = Client.where("client exists in products") #something to this effect
Simplest but not the fastest:
Client.where(:id => Product.select(:client_id).map(&:client_id))
SQL subquery (more faster):
Client.where("EXISTS(SELECT 1 from products where clients.id = products.client_id)")
Another gem that exists to do that: activerecord_where_assoc (I'm the author)
With it:
Client.where_assoc_exists(:products)
If you had to also specify some of the products, when you could do it like this:
Client.where_assoc_exists(:products, id: my_products.map(&:id))
Doing it without a gem makes it easy to do mistakes.
Read more in the documentation. Here is an introduction and examples.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With