"distinct on" feature

[shaloba]

I need to retrieve the latest record in the DB for certain device and to do that I need to use SQL DISTINCT feature. Now I saw that the Tortoise package have distinct method but I didn't find a usage in the example (that worked for me), the query just froze

In raw SQL the query working fine and I'm trying to understand If im doing anything wrong with the ORM (I hope this is the case)

The expected result is that I will retrieve the latest users from the users table (where account ID is 1) from the users table

SQL query:

select distinct on (user_id) u.* from users u where account_id = 1 order by user_id, created_at desc;

The ORM usage:

result = await Users.filter(account_id=1).order_by('created_at').distinct().values('user_id').all()

Am I doing something wrong here ??

Thanks for the help ! :)

[grigi]

I will have to investigate. We are using PyPika to generate the SQL, and pass the parameters in to that. Let me check what is going on...

[grigi]

Ok, I had a look at how distinct() works. It puts a distinct in front of the entire set of fields. Meaning for it to work right one needs to limit the values that one wants.

e.g. await Users.filter(account_id=1).order_by("created_at").distinct().values("user_id") gives: SELECT DISTINCT "user_id" "user_id" FROM "users" ORDER BY "created_at" ASC

Which is not what you were thinking of. Your query also makes me uncomfortable in that distinct is an aggregation feature, and selecting u.* only makes sense if user_id is unique, else you end up with duplicate data.

Now that I'm thinking of it, Can't you do a: await Users.filter(account_id=1).order_by("-created_at").limit(1) to give you the latest created account for that account_id ?

[grigi]

Ah, I see what you are asking.

"distinct on" is not a standard SQL feature, and I don't know how valuable it is to add this feature to Tortoise in Tortoises current state. We can consider it a post-1.0 feature.

Could make calling custom SQL easier be an OK workaround in the interm?

[Posrabi]

@grigi any updates on this?