On 02/17/2012 15:38, Michael Bayer wrote:
what version of SQLA is this ? count() was changed in 0.7 to run the query as is each time, wrapping it in a subquery.Otherwise, can I see more of a complete example I can run here / sample SQL ?
Hi Mike, This is with version 0.7.5.My Python code is here http://www.pastie.org/3401784 and the corresponding SQL is here http://pastie.org/3401782 (the first one is the COUNT and the second one the full query)
Thank you! Julien
On Feb 17, 2012, at 5:41 AM, Julien Cigar wrote:Hello, Is there a reason why a .count() on a Query object doesn't (LEFT) JOIN all the relations marked with lazy='joined' in the Mapper? I guess it's to avoid unnecessary JOINs, but sometimes it can lead to strange results if the relation is marked with innerjoin=True, for example: q = Occurrence.query.join(DataSheet).join(Topic).\ options(orm.contains_eager('datasheet')).\ filter(Topic.id == form.data['topic_id']) count = q.count() # returns 327 all = q.all() # result set is empty the query.count() returns a positive number, and the line after query.all() returns nothing. In this case this is because I have the following in my mapper definition: 'species' : orm.relationship(Species, backref='occurences', lazy='joined', innerjoin=True) So the Species table is joined, but for this specific data set species names haven't been determined yet (this is temporary).. so all the species_id in Occurrence are empty. Now I know that I should put innerjoin=True only if each Occurrence has a Species (so that all species_id are not NULL), but to avoid confusion I think that all relationships marked with innerjoin=True should be joined for the count() ... ? Thanks, Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. <jcigar.vcf>
-- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
<<attachment: jcigar.vcf>>
