postgresql - rails missing FROM-clause entry for table (counting children) -

i have simple parent has_many children relationship , i'm trying parents have less n children.

parent.select("parents.id").joins(:children).group('parents.id').having('count(children.id) < ?', n).reorder('parents.id') 

the error keeps appearing is:

select parents.id "parents" inner join "children" on "children"."parent_id" = "parents"."id" group parents.id having count("children"."id") < 100 pg::undefinedtable: error:  missing from-clause entry table "children" 

from have read online, should working. i've searched through many posts related questions, none of answers seem relate. there scope on parent-child relationship ordering, , that's why i'm reordering parent id.

what "from-clause" entry need?

running rails 4.2 , postgres

i think issue here having clause references -- children.id -- isn't present in select. having evaluated after group by , select, believe has available parents.id.

see select doc more detail on that.

so, ultimately, need have child id count available select in order call having on it.

one way comes mind accomplish both of these things @ once use count window function.

the sql effect of this:

select parents.id, count(children.id) over(partition children.id) children_id_count "parents" inner join "children" on "children"."parent_id" = "parents"."id" count(children.id) over(partition children.id) < 100 group parents.id 

note: window function needs repeated in where clause since alias has not yet been created @ point.

afaik, active record not natively support window functions, you'll need use raw sql that.