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.
