I’ve put your classes below into the test script (attached), but I can’t reproduce your results. The last query is:
query(A).options(saorm.joinedload_all('b.cs’)) and I get the same value for len(query.all()) and query.count(), despite there being 3 rows returned from the underlying query. Note: I had to modify your classes slightly to make the script work. I removed the c_id column from B (since C has a b_id column pointing in the other direction, and you said the relationship was 1-to-many), I had to use strings for the foreign key definitions because of the forward references, and I had to set uselist=False on the a.b backref. If you get different results, what version of SA are you using? Simon
On 28 Jan 2014, at 17:45, Michael Nachtigal <michael.nachti...@catalinamarketing.com> wrote: > 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.
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=saorm.backref('b', uselist=False)) 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')) 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(b=B(cs=[C() for i in range(3)])) 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('b'))) display('A, joined to B', session.query(A).join('b')) display('A, joinedload to b.cs', session.query(A).options(sa.orm.joinedload_all('b.cs'))) display('A, joined to B, joinedload to C', session.query(A).join('b').options(saorm.joinedload_all('b.cs'))) if __name__ == '__main__': test()
-- 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.