# Query optimization when combining the same table

**URL:** <https://forum.djangoproject.com/t/query-optimization-when-combining-the-same-table/26618>\
**Category:** Using the ORM\
**Created:** [January 3, 2024, 3:43pm UTC](https://forum.djangoproject.com/t/query-optimization-when-combining-the-same-table/26618 "2024-01-03T15:43:29Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![alekso87](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/alekso87/32/18149_2.png) [@alekso87](https://forum.djangoproject.com/u/alekso87)\
**Post date:** [January 3, 2024, 3:43pm UTC](https://forum.djangoproject.com/t/query-optimization-when-combining-the-same-table/26618/1 "2024-01-03T15:43:29Z")

</div>

My model:

```auto
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:

```auto
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:

```auto
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:

```auto
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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [January 3, 2024, 4:07pm UTC](https://forum.djangoproject.com/t/query-optimization-when-combining-the-same-table/26618/2 "2024-01-03T16:07:54Z")

</div>

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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![alekso87](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/alekso87/32/18149_2.png) [@alekso87](https://forum.djangoproject.com/u/alekso87)\
**Post date:** [January 3, 2024, 4:24pm UTC](https://forum.djangoproject.com/t/query-optimization-when-combining-the-same-table/26618/3 "2024-01-03T16:24:00Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![HamaBarhamou](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/hamabarhamou/32/18007_2.png) [@HamaBarhamou](https://forum.djangoproject.com/u/HamaBarhamou)\
**Post date:** [January 3, 2024, 4:29pm UTC](https://forum.djangoproject.com/t/query-optimization-when-combining-the-same-table/26618/4 "2024-01-03T16:29:04Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![alekso87](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/alekso87/32/18149_2.png) [@alekso87](https://forum.djangoproject.com/u/alekso87)\
**Post date:** [January 3, 2024, 4:33pm UTC](https://forum.djangoproject.com/t/query-optimization-when-combining-the-same-table/26618/5 "2024-01-03T16:33:46Z")

</div>

```auto
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](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/3X/c/d/cd1b15bebd46e7543f89bbc157a921aa5080505a.png)

One parent record has only one child.

Perhaps I also created the wrong key for the “parent\_id” field.

---

<div class="post-metadata">

**Author:** ![HamaBarhamou](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/hamabarhamou/32/18007_2.png) [@HamaBarhamou](https://forum.djangoproject.com/u/HamaBarhamou)\
**Post date:** [January 3, 2024, 4:37pm UTC](https://forum.djangoproject.com/t/query-optimization-when-combining-the-same-table/26618/6 "2024-01-03T16:37:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [January 3, 2024, 4:58pm UTC](https://forum.djangoproject.com/t/query-optimization-when-combining-the-same-table/26618/7 "2024-01-03T16:58:27Z")

</div>

> [@alekso87](#):
>
> I updated the model a bit, I forgot to specify 1 more column with a foreign key.

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](https://docs.djangoproject.com/en/4.2/intro/tutorial01/), I suggest you do so. It’ll give you a lot of practical experience working with foreign key relationships.

---

<div class="post-metadata">

**Author:** ![alekso87](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/alekso87/32/18149_2.png) [@alekso87](https://forum.djangoproject.com/u/alekso87)\
**Post date:** [January 3, 2024, 5:03pm UTC](https://forum.djangoproject.com/t/query-optimization-when-combining-the-same-table/26618/8 "2024-01-03T17:03:09Z")

</div>

> [@HamaBarhamou](#):
>
> OK. Now what do you want to extract from this model?

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

---

<div class="post-metadata">

**Author:** ![alekso87](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/alekso87/32/18149_2.png) [@alekso87](https://forum.djangoproject.com/u/alekso87)\
**Post date:** [January 3, 2024, 5:05pm UTC](https://forum.djangoproject.com/t/query-optimization-when-combining-the-same-table/26618/9 "2024-01-03T17:05:50Z")

</div>

> [@KenWhitesell](#):
>
> 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.

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