Hello. I haven't used distinct() yet, but if you modify your query:
> query = dbSession.query( Candidate )\ > .join( Vote )\ > .filter(\ > model.core.Candidate.id == model.core.Vote.candidate_id , > model.core.Vote.yay == True , > )\ > .order_by(\ > model.core.Vote.timestamp.desc() > )\ > .limit(limit)\ > .offset(offset) > .all() it will return you instances of Candidate class, not a list of their ids. During this process, the distinct is automatic, i.e. the result will contain each candidate at most once. However I am not sure if this will work as expected in combination with limit and offset. HTH, Ladislav Lenart On 24.10.2012 00:59, Jonathan Vanasco wrote: > I'm trying to write a query against Postgres and it's driving me a bit > mad. Hoping someone here can help. > > I'm make the example in something topical... voting! > > Given: > Candidate > id > name > Vote > id > candidate_id (fkey on Candidate) > yay (bool) > > With this structure, I'm trying to find the distinct Candidates IDs > that have voted 'Yay' > > I thought i would accomplish it by querying the Candidate.id, joining > the Vote and filtering/sorting on that. > > That sort of works. > > The problem with the query, is that it seems to be giving me distinct > "votes" -- ie, distinct on the join of the 2 tables > > Is there a way to get a distinct limit on the Candidate.id field > only ? > > I tried padding in Candidate.id into the distinct() option, but then > Postgres wants me to fill it with all the different query params that > are used - which is normal in raw postgres, but a little scare in an > ORM where you're not necessarily concerned with ever knowing/seeing > the raw sql > > anyone have an idea on how to proceed ? > > ( fwiw, my interim fix is to just turn the returned ids into a set in > python , that works but gives unreliable items-per-page in > pagination ) > > > query = dbSession.query( Candidate.id )\ > .distinct() > .join( Vote )\ > .filter(\ > model.core.Candidate.id == model.core.Vote.candidate_id , > model.core.Vote.yay == True , > )\ > .order_by(\ > model.core.Vote.timestamp.desc() > )\ > .limit(limit)\ > .offset(offset) > .all() -- 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.
