Group by in Django ORM

Question

I want to get the number of "chats" between 2 users in my app and I have a table called "Message" that contains:

sender_user | reciever_user | contain | date

I want to do a query that gives me all the messages between 2 differents users, I know in SQL I need to use GROUP BY but how can I get the list of messages with the Django ORM? Do you have any idea?

NOTE: sender_user and reciever_user are instances of User table)

follow this - http://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django it might be a solution of your problem — jewelhuq, Dec 26 '14 at 23:06
Yes I tried, but I want to get the info of the reciever and the sender in a sigle object, not just the id and the number of records. Do you have any idea? — Cris_Towi, Dec 26 '14 at 23:11
No, because I what the list of "chats" (messages between 2 users) that involves the request.user, so the "WHERE" you say just give me a single "chat". — Cris_Towi, Dec 26 '14 at 23:12

score 3 · Answer 1 · answered Dec 26 '14 at 23:15

3

You don't need to "GROUP BY" in the case described. You need to filter on sender and receiver users - it's equivalent to an SQL "WHERE" clause. And you would also order by the date.

Message.objects.filter(sender_user='sender', receiver_user='receiver')

answered Dec 26 '14 at 23:15

mhawke

84,695
9
117
138

I think i was not all specific. If I do that, i will only get the "chats" of me and some other person. I want to get ALL the chats that I am involved. – Cris_Towi Dec 26 '14 at 23:17
Then just use sender_user or receiver_user depending on which user you require. Order the results by sender_user and receiver user. You have specified any requirement that requires grouping. – mhawke Dec 26 '14 at 23:20
Yes, but that will give me all the "Messages" and i want only the "Chats" because I want to show all the chats in a list on a html. – Cris_Towi Dec 26 '14 at 23:23

score 0 · Answer 2 · answered Dec 26 '14 at 23:45

You can use annotate method of queryset. anotate method is abstruction for group by clauses in django.

Your query should probably be something like:

User.objects.filter(message_set__sender=me).annotate(message_count=Count('message'))

Reference: https://docs.djangoproject.com/en/1.7/topics/db/aggregation/#order-of-annotate-and-filter-clauses

Group by in Django ORM

2 Answers2