Search code examples
ruby-on-railsactiverecordactive-record-query

Rails ActiveRecord return records where id exists in related table

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
Solution

  • 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)")