Thanks, Mike. I'll work on that. I do have a test case, but there
everything works fine. There must be something in my real data. I just
can't figure it out what it is!
D.
On Aug 24, 8:45 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> 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" <mike...@zzzcomputing.com> 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" <mike...@zzzcomputing.com>
> >>> 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 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
-~----------~----~----~----~------~----~------~--~---
