Simon, Thanks very much for your detailed reply. Here's your example tailored to more accurately match our program (A is in a 1-to-1 relationship with B, and B is in a 1-to-many relationship with C):
import sqlalchemy as sa import sqlalchemy.orm as saorm from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class A(Base): __tablename__ = 'a' id = sa.Column(sa.Integer(), primary_key=True) class B(Base): __tablename__ = 'b' id = sa.Column(sa.Integer(), primary_key=True) a_id = sa.Column(sa.Integer, sa.ForeignKey(A.id)) c_id = sa.Column(sa.Integer, sa.ForeignKey(C.id)) a = saorm.relationship(A, backref='b') cs = saorm.relationship(C, backref='b') class C(Base): __tablename__ = 'c' id = sa.Column(sa.Integer(), primary_key=True) b_id = sa.Column(sa.Integer, sa.ForeignKey(B.id)) # ...then, later: q = session.query(A).options(joinedload_all('b.cs')) q.count() # debug output prints a SELECT COUNT(*) FROM ($Q) where $Q is a subquery (subselect) that doesn't involve the b or c tables q.all() # debug output prints out the "complete" query, including joins to b and c len(q.all()) < q.count() # True Thanks, Mike ________________________________________ From: sqlalchemy@googlegroups.com [sqlalchemy@googlegroups.com] on behalf of Simon King [si...@simonking.org.uk] Sent: Friday, January 24, 2014 6:29 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] joinedload options on a query don't affect its .count()? Here's a test script that shows the results of query.count() and len(query.all()) for a few different query scenarios, and I think the results match what I would have expected. In particular, I don't see a case where adding joinedload affects the results. Could you see if you could adapt it to show your problem? The setup is that A has 1-to-many relationships to both B and C, and the database has a single A row, 3 B rows and 5 C rows. import sqlalchemy as sa import sqlalchemy.orm as saorm from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class A(Base): __tablename__ = 'a' id = sa.Column(sa.Integer(), primary_key=True) class B(Base): __tablename__ = 'b' id = sa.Column(sa.Integer(), primary_key=True) a_id = sa.Column(sa.Integer, sa.ForeignKey(A.id)) a = saorm.relationship(A, backref='bs') class C(Base): __tablename__ = 'c' id = sa.Column(sa.Integer(), primary_key=True) a_id = sa.Column(sa.Integer, sa.ForeignKey(A.id)) a = saorm.relationship(A, backref='cs') def test(): from optparse import OptionParser parser = OptionParser() parser.add_option('-v', '--verbose', action='count', default=0) options, args = parser.parse_args() echo = {0: False, 1: True}.get(options.verbose, 'debug') engine = sa.create_engine('sqlite:///:memory:', echo=echo) Session = saorm.sessionmaker(bind=engine) Base.metadata.create_all(engine) session = Session() a = A( bs=[B() for i in range(3)], cs=[C() for i in range(5)], ) session.add(a) session.flush() del a def display(title, query): print '\n%s' % title print '=' * len(title) print if options.verbose: print '#### query.count()' count = query.count() if options.verbose: print '\n#### query.all()' results = query.all() if options.verbose: print print 'count = %s' % count print 'len(results) = %s\n' % len(results) for i, row in enumerate(results): print '%2s. %s' % (i + 1, row) display('A, no joins or joinedloads', session.query(A)) display('A, joinedload to B', session.query(A).options(saorm.joinedload('bs'))) display('A, joined to B', session.query(A).join('bs')) display('A, joined to B, joinedload to C', session.query(A).join('bs').options(saorm.joinedload('cs'))) if __name__ == '__main__': test() Here's the output I get (with SA 0.9.1): A, no joins or joinedloads ========================== count = 1 len(results) = 1 1. <__main__.A object at 0x10b8bf9d0> A, joinedload to B ================== count = 1 len(results) = 1 1. <__main__.A object at 0x10b8bf9d0> A, joined to B ============== count = 3 len(results) = 1 1. <__main__.A object at 0x10b8bf9d0> A, joined to B, joinedload to C =============================== count = 3 len(results) = 1 1. <__main__.A object at 0x10b8bf9d0> The second and fourth tests are the same as the first and third with a joinedload('cs') added. In both cases, the results remain the same. Cheers, Simon On 24 Jan 2014, at 22:36, Michael Nachtigal <michael.nachti...@catalinamarketing.com> wrote: > 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. -- 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.