On Aug 24, 2009, at 7:44 PM, DavidG wrote:
> > First, there is at most a single Feedback record (for a given user, > hence the subquery) per quote (and in the one case I have been banging > my head on, I am certain of this). > > And this: why would I get different results from pasting the echoed > sql into the online mysql query vs from sqlalchemy directly? for the reasons I've given - entities are uniqued, primary keys with null columns are skipped by default. for further explanation you need to illustrate a test case illustrating the identical behavior here. > > > Thanks, > David. > > On Aug 24, 6:39 pm, "Michael Bayer" <[email protected]> wrote: >> DavidG wrote: >> >>> Hi Mike - >> >>> Confused. Why would it be different with the limit() or not? >> >> well there's not enough detail to say exactly but you're applying the >> limit() to a query with outer join. So if Quote number one had five >> related Feedback entries, you'd get one row back for all five of >> those, >> unless the Feedback entries were part of the returned results. >> >> another thing that happens, but is probably not happening here, is if >> Quote is mapped to a join that might return NULL for some primary >> keys, >> those aren't going to be turned into entities either unless the >> mapping >> specifies allow_null_pks=True. In 0.6 this option is just turned on >> permanently since it turned out nobody wants it the other way. >> >> Without >> >>> the limit() I get *all* the Quote records (>1000) which is >>> correct. If >>> I have something like limit(10), I'll get *less then 10*. >> >>> Also, I didn't know about the "unique entities" limitation. In any >>> event, the Quote objects are all unique (via their unique >>> primary_key >>> "id"). >> >>> OK, more details. Here are the classes (summary): >> >>> class Quote(Base): >>> __tablename__ = "quote" >> >>> id = Column(Integer, primary_key=True) >>> date_create = Column(DateTime) >>> feedback = relation('Feedback') >> >>> class Feedback(Base): >>> __tablename__ = "feedback" >> >>> id = Column(Integer, primary_key=True) >>> username = Column(Unicode(20)) # NOTE: this is a ForeignKey >>> also, but ignore for now. >>> quote_id = Column(Integer, >>> ForeignKey('quote.id')) >>> vote = Column(Integer, default=0) # -1 or +1 >> >>> This *should* be so simple: there are bunches of quotes. There *may* >>> be a (single) Feedback record for each user for each quote. For a >>> given username, I want to display a range of quotes, sorted a >>> particular way, with the Feedback record for each quote (when it >>> exists) tacked on (in a tuple is fine). >> >>> Thanks! >> >>> On Aug 24, 4:42 pm, "Michael Bayer" <[email protected]> >>> wrote: >>>> Query(), when called with entity classes as arguments, returns only >>>> unique >>>> entities or unique combinations thereof. to get the raw data call >>>> Query >>>> with columns/attributes as arguments instead. >> >>>> DavidG wrote: >> >>>>> Hi, >> >>>>> I can give all the details, but let's start with a simple >>>>> question. >> >>>>> I have a query, and it is returning the wrong number of rows! >> >>>>> Not only is the number wrong compared to what I would expect, but, >>>>> more importantly, when I paste the *exact sql* (except for >>>>> substituting a param) printed on the console with "echo on" into >>>>> the >>>>> mysql prompt, the results are exactly what I would expect. >> >>>>> What, if anything, is known to cause the "printed sql" to give a >>>>> different result then sqlalchemy itself? >> >>>>> I am using: >>>>> sqlalchemy-0.5.5 >>>>> mysql >>>>> python 2.6.2 >> >>>>> I am using the orm, and I am doing basically: >> >>>>> recs = q.all() >>>>> print "len(recs)=", len(recs) >> >>>>> where q is the query. >> >>>>> Sample: >> >>>>> username = u'steve' >>>>> subq = SES.query(Feedback).filter(Feedback.username == >>>>> username).subquery() >>>>> valias = aliased(Feedback, subq) >>>>> q = SES.query(Quote, >>>>> valias).order_by(desc(Quote.n_votes)).outerjoin >>>>> (Quote.feedback, valias).limit(2) >> >>>>> Without the limit(), I get all the records (>1000), which seem >>>>> correct. *With* the limit, the number of records is completely >>>>> kookie >>>>> (to me!). It seems to be always *less* then what the actual >>>>> limit is. >> >>>>> But again, the sql printed on the console gives me the correct >>>>> results! >> >>>>> Any help would be most appreciated! Thank you. I will happily >>>>> furnish >>>>> more details if needed. >> >>>>> David >> >> > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
