On 02/17/2012 16:26, Michael Bayer wrote:
Yeah everything is working by design. count() doesn't run any of those "lazy='joined'" joins, as those are not
ok, good to know ! I thought it was a bug
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.
yes.. that's actually the case
I'd try turning off the eagers to see which one is messing up the results.
Thanks for support, Julien
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! JulienOn 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 [email protected]. To unsubscribe from this group, send email to [email protected]. 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 [email protected]. To unsubscribe from this group, send email to [email protected]. 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
<<attachment: jcigar.vcf>>
