django: sort table data according to other table data

Question

Hi.

I have read the (excellent) documentation but I can't figure out how to do this.

I have a table which represents user friendships called "Shortlist" (with fields "from_user" and "to_user": both Foreign Keys to the classic auth_user table. Namely, from_user befriends to_user).

I have another table into which I log user events/actions (UserLog). In this table there are many fields including a "timestamp" and of course a "user" field (a foreign key to the auth_user table)

how can I retrieve data from table "Shortlist" for a specific user (from_user) ordered by the timestamp in UserLog table? Or how can I retrieve Shortlist data for a specific from_user ordered by the total number of "to_user" actions logged?

in other words I would like to retrieve all user's friends that are most active or order them by most recent action first.

Hope that makes sense, Chris

Answer 1

Orderring by another table is possible with related field syntax:

Example:

UserProfile.objects.all().order_by('user__username')

To calculate thing based on fields use annotations

Answer 2

thanks for your answer, however I think I might need some more help. What I want to do is to order users according to their activity (most active user first). Activities are logged in another table.

for instance I want to do something like this (if possible)

select * from Users U, (select count (id) from UserLog L where U.id=L.user_id) as actions order by actions desc

I would also like to sort them according to their latest actions (user whith most recent action, first)

any ideas (or best practices) on how to do those things?