Count number of SQLAlchemy relationships inside a filter

Question

I've been trying to google this but I'm only really coming up with how to retrieve the count rather than filter by it. The closest result I found was this answer, but I'm constructing the query without a session so using object_session raises UnmappedInstanceError.

Given Parent and Child models, connected by Parent.children, how could I query which parents have a certain amount of children?

I've tried session.query(Parent).filter(func.count(Parent.children)>1), but it complains about misuse of the count function.

As I'm building the query recursively for a search function, the query is actually built up of dozens of filters, so if at all possible I'd like this to remain in a single filter.

score 0 · Answer 1 · answered Aug 07 '19 at 19:59

0

I think something like this might work (not at all tested)

session.query(func.count(Children.parent_id))\
    .group_by(Children.parent_id)\
    .filter(func.count(Children.parent_id) > 5)

answered Aug 07 '19 at 19:59

reptilicus

10,290
6
55
79

Thanks, though I'm wanting to query which parents have a certain amount of children, rather than querying a single parent (correct me if I'm wrong but doesn't the above query have the same effect as `len(parent.children) > 5`?). I believe I need the equivalent of `WHERE (SELECT count(*) from Children where Children.parent_id == Parent.row_id) > 5`, and SQLAlchemy is only doing `WHERE count(Children.parent_id == Parent.row_id) > 5` if I print out my query. I'm not very good at SQL but I assume the issue could be down to that. I tried joining `Children` and tested a few `group_by`'s but no luck. – Peter Aug 08 '19 at 10:35
Nevermind, figured it out after typing up that comment :) – Peter Aug 08 '19 at 10:39

score 0 · Answer 2 · answered Aug 08 '19 at 13:15

After comparing the generated SQL with what is actually needed, I noticed it needed a nested select statement, which can be done with sqlalchemy.select.

Instead of this:

session.query(Parent).filter(func.count(Parent.children)>1)

The correct syntax is this:

subsearch = select([func.count(Parent.children)]).where(Parent.row_id==Child.parent_id).as_scalar()
session.query(Parent).filter(subsearch>1)

Count number of SQLAlchemy relationships inside a filter

2 Answers2