Rails ActiveRecord return records where id exists in related table

Question

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

Sori

does @clients = Client.joins(:products) works ? I think it'll do an INNER JOIN, which is (i think) what you want – Tristan

That's a good question, I will test in console – Sori

@Tristan It seems to return all of the products joined to the clients therefore I end up with duplicated client names. Is there a way to limit the results to just the clients? – Sori

@Tristan For example I only want the 4 records, not 214 which is the number of products I have in the products table. The 214 are a combination of those 4 clients but I only want to return the 4 clients, not the 214 products those clients are joined to. I hope that doesn't confuse anyone. – Sori

I get what you want, but the query Client.joins(:products) is returning a collection of clients right ? the problem is that you have duplicate entry that's it ? – Tristan

I'm going to edit my answer to illustrate exactly what it returns – Sori

I have an idea, might not be the best, but you can try it : Product.joins(:client).map(&:client).uniq – Tristan

You were closer the first time. simply needed to add uniq to your original suggestion. @client= Client.joins(:products).uniq Thanks for helping me brainstorm all this – Sori

Glad I somehow helped you :) – Tristan

Answer 1

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

Azaleeazan

That's a much better answer, doesn't' return unnecessary columns that a join statement would include. – Sori

I really like this answer. The second query allows me to compare against a non-active record table that's in the same DB. – Modify

Answer 2

Here's another solution. It's a subquery like Valery's second solution, but without writing out the sql:

Client.where(Product.where(client_id: Client.arel_table[:id]).exists)

Lh

this does not work for me (using postgresql adapter); keeps interpreting the arel_table param as a prepared value, and then erroring because it isn't one. – Elfland

I just learned, that .exists is not part of the public API. This used to work until 4.1, but no more on 4.2. – Procurer

Answer 3

Here is the solution which uses Where Exists gem (disclosure: I'm its author):

Client.where_exists(:products)

Diode

Nice gem! Looks elegant! – Assignment

Answer 4

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.

Beaudoin

Answer 5

Also not the fastest but is concise:

Client.where(:id => Product.pluck(:client_id))

Syndic