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

Question

I have a simple parent has_many children relationship and I'm trying to get all parents that have less than n children.

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

The error that keeps appearing is:

SELECT parents.id FROM "parents" INNER JOIN "children" ON "children"."parent_id" = "parents"."id" GROUP BY parents.id HAVING count("children"."id") < 100
PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "children"

From what I have read online, this should be working. I've searched through many posts with related questions, but none of the answers seem to relate. There is a scope on the parent-child relationship for ordering, and so that's why I'm reordering by parent id.

What is the "FROM-clause" entry that I need?

Running Rails 4.2 and Postgres

What happens if you run the raw SQL in PgAdmin or psql? Does it work or do you get the same error? Does the children table definitely have both parent_id and id columns? — khampson
same error in my psql. I confirmed that children does have parent_id and id columns. — stevenspiel

khampson khampson · Accepted Answer · 2015-07-14T01:22:00

I think the issue here is that your HAVING clause references something -- children.id -- that isn't present in your SELECT. HAVING is evaluated after the GROUP BY and the SELECT, so I believe what it currently has available to it is just parents.id.

See the SELECT doc for more detail on that.

So, ultimately, you need to have the child id count available from the SELECT in order to then call HAVING on it.

One way that comes to mind to accomplish both of these things at once would be to use count as a window function.

The SQL would then look something to the effect of this:

SELECT parents.id, count(children.id) over(partition by children.id) as children_id_count
FROM "parents"
INNER JOIN "children" ON "children"."parent_id" = "parents"."id"
WHERE count(children.id) over(partition by children.id) < 100
GROUP BY parents.id

Note: The window function needs to be repeated in the WHERE clause since the alias has not yet been created at that point.

AFAIK, Active Record does not natively support window functions, so you'll need to use raw SQL for that.

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

1 Answers