Query optimization when combining the same table

Using Django Using the ORM
1

My model:

class ChatConversationMessages(models.Model):
    id = models.BigAutoField(primary_key=True)
    parent = models.ForeignKey('self', on_delete=models.CASCADE, null=True)
    conversation = models.ForeignKey(ChatConversation, on_delete=models.CASCADE)
    direction = models.CharField(max_length=10)
    message = models.TextField()
    rating = models.SmallIntegerField(null=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = 'chat_conversation_messages'

I need to receive such a query:

SELECT "c1"."message" as assistant_message,
       "c2"."message" as human_message
FROM "chat_conversation_messages" c1
INNER JOIN "chat_conversation_messages" c2 ON (c2.id = c1.parent_id)
WHERE "c1"."conversation_id" = 'da0e0d8b3af34bc0bcac39bd9a7cfcab'::uuid AND "c1"."direction" = 'assistant'
ORDER BY "c1"."created_at" ASC;

I’m doing it now, but it’s not right:

for cm1 in ChatConversationMessages.objects.filter(conversation_id=request.session['conversation_id'], direction='assistant').order_by('created_at'):
    cm2 = ChatConversationMessages.objects.get(id=cm1.parent_id)

    print(cm2.message)
    print(cm1.message)

I tried it this way, but an extra AND is added to the query:

for cm in ChatConversationMessages.objects.filter(conversation_id=request.session['conversation_id'], direction='assistant', parent__parent_id=F('id')).order_by('created_at'):
    print(cm.message)

Query:

SELECT "chat_conversation_messages"."id",
       "chat_conversation_messages"."parent_id",
       "chat_conversation_messages"."direction",
       "chat_conversation_messages"."message",
       "chat_conversation_messages"."rating",
       "chat_conversation_messages"."created_at",
       "chat_conversation_messages"."updated_at"
FROM "chat_conversation_messages"
    INNER JOIN "chat_conversation_messages" T3 ON ("chat_conversation_messages"."parent_id" = T3."id")
WHERE ("chat_conversation_messages"."conversation_id" = 'da0e0d8b3af34bc0bcac39bd9a7cfcab'::uuid AND "chat_conversation_messages"."direction" = 'assistant' AND T3."parent_id" = ("chat_conversation_messages"."id"))
ORDER BY "chat_conversation_messages"."created_at" ASC;

I need your help, I can’t cope on my own.

2

Don’t try to match exact database SQL. Look to achieve the appropriate functionality.

It looks to me that you’re trying to retrieve the message field from two rows of a table. The first instance being the row where id == <a given value> and the direction == 'assistant', and the second instance being the instance related to the first instance by the parent field.

If this is correct, then the query would be something like:

cm1 = ChatConversationMessages.objects.filter(conversation_id=request.session['conversation_id'], direction='assistant').order_by('created_at')

The second message is accessible directly from the relationship provided by the first:
message2 = cm1.parent.message

Note, as an optimization (but not required), you can improve the cm1 query by using the select_related('parent') clause in the query.

3

I updated the model a bit, I forgot to specify 1 more column with a foreign key.

ChatConversationMessages.objects.filter(conversation_id=request.session['conversation_id'], direction='assistant').order_by('created_at')

This is a basic query to which I would also like to add another selection by “parent_id” from the same table in order to get the parent value from the “message” field.

4

forget the sql approach. django will do it for you. Based on your updated model, which I invite you to republish. We can propose the appropriate query.

5 
class ChatConversationMessages(models.Model):
    id = models.BigAutoField(primary_key=True)
    parent = models.ForeignKey('self', on_delete=models.CASCADE, null=True)
    conversation = models.ForeignKey(ChatConversation, on_delete=models.CASCADE)
    direction = models.CharField(max_length=10)
    message = models.TextField()
    rating = models.SmallIntegerField(null=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = 'chat_conversation_messages'

Screenshot at Jan 03 18-31-52
Screenshot at Jan 03 18-31-522568×426 103 KB

One parent record has only one child.

Perhaps I also created the wrong key for the “parent_id” field.

6

OK. Now what do you want to extract from this model?

7

This doesn’t materially affect anything.

In Django, when you have a foreign key to a model, accessing fields from that model does not change the query. The Django ORM handles that for you.

So, if you have a variable named cm1 which is an instance of ChatConversationMessages, then the message field of the parent instance of cm1 is cm1.parent.message. No change to the query is required.

If you have not yet worked your way through the Official Django Tutorial, I suggest you do so. It’ll give you a lot of practical experience working with foreign key relationships.

8

I want to get the “message” field of the child and the “message” field of the parent in one row.

9

wow, got it :slight_smile: Thanks! That’s what I was looking for.