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? 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 -~----------~----~----~----~------~----~------~--~---
