Django Group By

Question

Hello. I'm writing a simple private messenger using Django and am implementing message threading.

Each series of messages and replies will have a unique thread_id that will allow me to string sets of messages together. However, in the inbox view ALL of the messages are showing up, I'd just like to group by the thread_id so that although a thread could have 20 messages, it only shows up once in the inbox.

It'd be a pretty simple

SELECT msg_id, msg_text, subject, from_user_id, to_user_id, date_time, is_read,
thread_id WHERE to_user_id='foo' GROUP BY thread_id FROM inbox_message;

however, I can't seem to execute it using django's ORM

any thoughts?

Answer 1

What do you want to achieve with this SQL statement?

It will work on some DBMS (ie. MySQL), but it isn't legal. When your are using GROUP BY statement, you can select only columns your are grouping by, and aggregates (SUM, AVG, COUNT etc.). Other columns are forbidden, because DBMS don't know what data return (ie. should it return subject of first message, second one or what?).

If you wan't some sumup about thread, other than count of messages, probably best solution for you is to add new columns to thread table (BTW. do you have thread table?).