Simon, Thanks so much for your informative reply.
Yes, our query joins against several tables, and is only querying for, e.g., X instances (X is a mapped class). Furthermore, the relationship involved in its joinedload option is 1-to-many, so it actually increases the number of records in the result set. The combined effect is that doing query.count() returns 20, but the number of elements in the resulting set is actually 17, because there were multiple rows for a single X (the multiple rows were distinguishable by the values in the related table's columns); because we were only querying for Xs, the 4 "duplicate" X rows were (evidently) "coalesced" into a single result row when running query.all(). Does that make sense? You said, "I’d expect the same answer even if you’ve specified joinedload options"; I would expect so, too, but apparently this is not always the case! Now I wonder: We evidently cannot say that len(query.all()) == query.count(), but can we say that len(query.all()) <= query.count()? ________________________________________ From: sqlalchemy@googlegroups.com [sqlalchemy@googlegroups.com] on behalf of Simon King [si...@simonking.org.uk] Sent: Friday, January 24, 2014 5:22 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] joinedload options on a query don't affect its .count()? On 24 Jan 2014, at 21:56, Michael Nachtigal <michael.nachti...@catalinamarketing.com> wrote: > Hello, all, > > I have a question that I discovered while troubleshooting a problem with our > application today: It seems that joinedload options > (e.g.,http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html?highlight=joinedload_all#sqlalchemy.orm.joinedload) > on a query do not affect the query that is emitted when you do a > query.count(); the following illustrates my point: > > # ...query has already been constructed, and includes > .options(joinedload(...)) > count = query.count() # line 1 > results = query.all() # line 2 > assert count == len(results) # fails! > > Upon inspection of the queries emitted by line 1 and line 2, the > joinedload()ed relationship's table is included in the query emitted by line > 2 (correctly), but isnot included in the query emitted for the count by line > 1 (surprisingly!). In our case, because the joinedload()ed relationship > happens to add rows to the result set of the query, the count (line 1) and > the length (line 2) of the results do not match! > > Can someone confirm that this is expected behavior? If so, is there an > explanation for this behavior? > > Thanks in advance for your time and reply, > Mike I’m not sure this answers your question exactly, but the number of results you get back from query.all() is not necessarily equal to the number of rows returned by the query. For example, lets say you had a table with a 1-to-many relationship to another table. If you had a query against the first table that returned a single row, then when you added a joinedload option to the second table, the number of rows would be more, but len(query.all()) would still be 1. (This is touched on in the docs at http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html#the-zen-of-eager-loading - the load options that you use are not meant to affect the results of the query in any way) I don’t know what .count() is meant to count exactly. In a “simple” query (ie. against a single table), I would guess it would return the same as len(query.all()). And, since eager load options aren’t meant to affect the results of the query, I’d expect the same answer even if you’ve specified joinedload options. However, if you’ve used something like .join() to bring other tables into the query, .count() would probably count those rows. Does that match with what you are seeing? Simon -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.