CODEWITHSUNDEEP
pythonpython-2.7ormsqlalchemy
Ketouem
Ketouem

Reputation: 3857

Way to filter using a backref "length" with SQLAlchemy

I have a one-to-many relationship between two types of objects Message and URL. A relationship is defined in the class URL with a backref named "link" that points to Message. Is there a way to make a filter, while querying on Message, to find for example the Message that possesses less than n URLs ?

I've tried with:

session.query(Message).filter(len(Message.link) < n).all()

And with

from sqlalchemy import func
session.query(Message).filter(func.count(Message.link) < n).all()

Each of them is sending me back an error. The first one:

InstrumentedAttribute has no len()

The second:

DatabaseError: (DatabaseError) ORA-00934: group function is not allowed here

Thanks to Audrius Kažukauskas and after some fiddling I've managed to do the following query:

session.query(Message.message_id)\
       .join(URL)
       .group_by(Message.message_id)
       .having(func.count(URL.url_id) <= n)

Which works fine (send back a KeyedTuple instead of a collection of Message but I can live with it).

Upvotes: 1

Views: 3552

Answers (1)

Audrius Kažukauskas
Audrius Kažukauskas

Reputation: 13543

For this to work, you need to join Message with URL, group by all fields in Message and do the filtering in HAVING clause:

q = session.query(Message).\                                                     
    join(URL).\                                                                  
    group_by(Message).\                                                          
    having(func.count(URL.id) < n)

filter() (which translates to WHERE in SQL) is applied before grouping and aggregate functions, while having() filters rows after the grouping is done.

Upvotes: 4

Related Questions