Yeah everything is working by design. count() doesn't run any of those "lazy='joined'" joins, as those are not intended to change the results. That you get zero results on a regular query probably means some of the related records that you're using "innerjoin=True" on don't actually have a row. If a collection or related row is empty/nonpresent, you won't get a parent row back.
I'd try turning off the eagers to see which one is messing up the results. On Feb 17, 2012, at 9:55 AM, Julien Cigar wrote: > 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. > > <jcigar.vcf> -- 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.
