1

Assume that a tree has many apples and an apple belongs to only one tree.

I am trying to run a query that returns me trees that have greater than 2 apples. I am stuck on this:

Tree.joins(:apples).where('count(tree.apples) >= 2')

(pry) output error: #<ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "tree"

I don't think I can do tree.apples since apples isn't an actual column on tree but an association.

Jwan622
  • 11,015
  • 21
  • 88
  • 181

1 Answers1

2

Since this is a one to many relationship, a tree doesn't know how many apples it has. Since that's the case, you'll have to go backwards and see which apples share the same tree.

select * from apple where tree_id in (
    select tree_id from apple group by tree_id having count(*) > 1
)

This will return a table with all the apples grouped by their respective trees if the tree has more than 1 apple. Or you can just do 

select tree_id from apple where tree_id in (
    select tree_id from apple group by tree_id having count(*) > 1
)

To return only a table of the tree_ids.

Helpful Source: Find rows that have the same value on a column in MySQL

Community
  • 1
  • 1
waynchi
  • 144
  • 1
  • 7