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
<[email protected]> 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: [email protected] [[email protected]] on behalf of
> Simon King [[email protected]]
> Sent: Friday, January 24, 2014 6:29 PM
> To: [email protected]
> 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
> <[email protected]> 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: [email protected] [[email protected]] on behalf of
>> Simon King [[email protected]]
>> Sent: Friday, January 24, 2014 5:22 PM
>> To: [email protected]
>> Subject: Re: [sqlalchemy] joinedload options on a query don't affect its
>> .count()?
>>
>> On 24 Jan 2014, at 21:56, Michael Nachtigal
>> <[email protected]> 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 [email protected].
>> To post to this group, send email to [email protected].
>> 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 [email protected].
>> To post to this group, send email to [email protected].
>> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
