Sqlalchemy Different Value From `len(query.all())` And `query.count()`

This is an example code. A Document has many Comment(s) PostComment extends Comment (with sqlalchemy polymorphic feature) Some query returns different result between len(query.all(

Solution 1:

Why do you get 8 as result instead of 2? Because you get a query which is a cartesian product (8 = 2 * 2 * 2). In turn, this happens because you have dynamic relationship withinheritance, which creates select from both tables (comment and post_comment) without any predicate between them.

Why the first query returns just 2? Well, because you are asking for actual mapped instances, sqlalchemy is smart enough to filter out the duplicates, although the underlying SQL statement does return 8 rows as well.

Add a join to your query to fix this:

query = d1.comments.join(PostComment).filter(PostComment.ready == True)