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.
