CODEWITHSUNDEEP
pythonsqlalchemy
Zachary Richey
Zachary Richey

Reputation: 302

sqlalchemy: Controlling columns selected from query

I'm having a bit of an issue with sqlalchemy. I'm creating a query like:

db.session.query(Case.court_id).filter(
        Case.last_updated.between(start, end)).filter(
                (Case.last_scraped_by != "RSS") | 
                (Case.last_scraped_by.is_(None))).order_by(Case.last_updated.desc()).distinct()

Which results in the following SQL:

SELECT DISTINCT `case`.court_id AS case_court_id, `case`.last_updated AS 
case_last_updated FROM `case` WHERE `case`.last_updated BETWEEN %s AND %s 
AND (`case`.last_scraped_by != %s OR `case`.last_scraped_by IS NULL) ORDER BY `case`.last_updated DESC

The issue here is I want only distinct Case.court_id's from the database, but due to Query.order_by changing the column list I get incorrect results, I've scoured the docs for sqlalchemy but so far have found no way to explicitly control the columns selected. So my question, is what would be the best way to explicitly control the columns selected?

Upvotes: 1

Views: 493

Answers (1)

van
van

Reputation: 76962

How about this one (moved distinct to select part):

db.session.query(Case.court_id.distinct()).filter(
        Case.last_updated.between(start, end)).filter(
                (Case.last_scraped_by != "RSS") | 
                (Case.last_scraped_by.is_(None))).order_by(Case.last_updated.desc())

However, it might be up to a database engine to allow such constructs.

Upvotes: 2

Related Questions