Finding all records that has at least one association from associated models (tables)

Question

So let's say I have this:

class Tree < ActiveRecord::Base
  has_many :fruits
  has_many :flowers
end

class Fruit < ActiveRecord::Base
  belongs_to :tree
end

class Flower < ActiveRecord::Base
  belongs_to :tree
end

How can I make an efficient query that would get all the Tree instances that would have at least one Flower or Fruit instance, or both? The idea is not getting the Tree that don't have any Flower and Fruit at all.

Similar question, for finding all records with at least one associated record present from a SINGLE association: https://stackoverflow.com/q/43007150/12484 — Jon Schneider, Aug 01 '22 at 15:36

score 5 · Accepted Answer · answered Sep 07 '20 at 10:07

I'd use such query:

Tree.left_joins(:fruits, :flowers).where('fruits.id IS NOT NULL OR flowers.id IS NOT NULL').distinct

it will produce this SQL:

SELECT DISTINCT "trees".* FROM "trees" LEFT OUTER JOIN "fruits" ON "fruits"."tree_id" = "trees"."id" LEFT OUTER JOIN "flowers" ON "flowers"."tree_id" = "trees"."id" WHERE (fruits.id IS NOT NULL OR flowers.id IS NOT NULL)

score 0 · Answer 2 · answered Sep 07 '20 at 09:52

A very basic way to get all Trees that have at least one Flower or Fruit is like this

flowers = Flower.pluck(:tree_id)
fruits  = Tree.pluck(:tree_id)

and then the Trees you want

Tree.where(id: [flowers, fruits].flatten)

This results in 3 queries to your db, which might or might not be a problem depending on your use case.

A more advanced method would be to do this, which results in only one query. Note the use of select instead of pluck which makes it possible for Rails to issue just one query in the end.

flowers = Flower.select(:tree_id)
fruits  = Fruit.select(:tree_id)

Tree.where(id: flowers).or(Tree.where(id: fruits))

Finding all records that has at least one association from associated models (tables)

2 Answers2