How to execute SELECT DISTINCT ON query using SQLAlchemy

Question

I have a requirement to display spend estimation for last 30 days. SpendEstimation is calculated multiple times a day. This can be achieved using simple SQL query:

SELECT DISTINCT ON (date) date(time) AS date, resource_id , time
FROM spend_estimation
WHERE
  resource_id = '<id>'
  and time > now() - interval '30 days'
ORDER BY date DESC, time DESC;

Unfortunately I can't seem to be able to do the same using SQLAlchemy. It always creates select distinct on all columns. Generated query does not contain distinct on.

query = session.query(
    func.date(SpendEstimation.time).label('date'),
    SpendEstimation.resource_id,
    SpendEstimation.time
).distinct(
    'date'
).order_by(
    'date',
    SpendEstimation.time
)

SELECT DISTINCT
    date(time) AS date,
    resource_id,
    time 
FROM spend
ORDER BY date, time

It is missing ON (date) bit. If I user query.group_by - then SQLAlchemy adds distinct on. Though I can't think of solution for given problem using group by.

---

Tried using function in distinct part and order by part as well.

query = session.query(
    func.date(SpendEstimation.time).label('date'),
    SpendEstimation.resource_id,
    SpendEstimation.time
).distinct(
    func.date(SpendEstimation.time).label('date')
).order_by(
    func.date(SpendEstimation.time).label('date'),
    SpendEstimation.time
)

Which resulted in this SQL:

SELECT DISTINCT
       date(time) AS date,
       resource_id,
       time,
       date(time) AS date # only difference
FROM spend
ORDER BY date, time

Which is still missing DISTINCT ON.

Answer 1

Your SqlAlchemy version might be the culprit.

Sqlalchemy with postgres. Try to get 'DISTINCT ON' instead of 'DISTINCT'

Links to this bug report: https://bitbucket.org/zzzeek/sqlalchemy/issues/2142

A fix wasn't backported to 0.6, looks like it was fixed in 0.7.