0

I have an SQL query I'm trying to translate to sqlalchemy but it's not the same. Here's the query:

select distinct on (uid) uid, id, amount, type, due_date
from events
where status = 'pending' and due_date <= '2020-09-14'
order by uid, due_date desc;

And here's how I'm trying to write it in sqlalchemy:

today = datetime.today()
query = Events.select().distinct(Events.c.uid).where(
    Events.c.status == EventStatus.pending).where(
    Events.c.due_date <= today).order_by(
        Events.c.uid, Events.c.due_date.desc())

When I print the above query I get:

SELECT DISTINCT events.id, events.uid, events.type, events.status, 
events.amount, events.old_tier, events.new_tier, events.due_date, 
events.created_at, events.updated_at 
FROM events 
WHERE events.status = :status_1 AND events.due_date <= :due_date_1 
ORDER BY events.uid, events.due_date DESC

Which is not the same thing because my original query is DISTINCT ON. What is wrong here? PS: I'm using postgres

ninesalt
  • 4,054
  • 5
  • 35
  • 75
  • 1
    does this help https://stackoverflow.com/questions/57253307/what-is-the-correct-way-to-use-distinct-on-postgres-with-sqlalchemy ? – AlexisG Sep 14 '20 at 12:41
  • `DISTINCT ON` ... is a Postgres-specific convenience command not a standard ANSI SQL command. Since sqlalchemy serves as a generalizable tool for *any* backend, you need to translate dialect specific commands to standard commands. And yes, `DISTINCT ON` can be translated with more SQL verbosity. – Parfait Sep 14 '20 at 14:40
  • 2
    It is the print that is "fooling" you; doing just `print(query)` will turn the query to a string using the default dialect, which does not understand `DISTINCT ON`. If you instead `print(query.compile(dialect=engine.dialect))` you should get the desired output, given the engine is using PostgreSQL dialect. – Ilja Everilä Sep 14 '20 at 14:48

0 Answers0