Annotate with same table name

Using Django Using the ORM
1

I have 3 tables/models

  • T1 has many T2
  • T3 has many T1 and T2
class T1(Model):
    id = BigAutoField(primary_key=True)
    t3 = ForeignKey(T3, on_delete=deletion.CASCADE)
    active = BooleanField(default=True)

class T2(Model):
    id = BigAutoField(primary_key=True)
    t1 = ForeignKey(T1, on_delete=deletion.CASCADE)
    t3 = ForeignKey(T3, on_delete=deletion.CASCADE)
    date_created = DateTimeField(auto_now_add=True)

class T3(Model):
    id = BigAutoField(primary_key=True)

The Query I have as below

T1.objects.filter(active=True, t3__id=1)
.order_by("t2__date_created")

In MySQL with .query

SELECT `t1`.`id`, `t1`.`t3_id`, `t1`.`active`
FROM `t1`
INNER JOIN `t2` ON (`t2`.`t1_id` = `t1`.`id`) 
WHERE (`t1`.`active` = True AND `t1`.`t3_id` = 1)
ORDER BY `t2`.`date_created`

What I want to add is an AND in the ON clause for better performance

SELECT `t1`.`id`, `t1`.`t3_id`, `t1`.`active`
FROM `t1`
INNER JOIN `t2` ON (`t2`.`t1_id` = `t1`.`id` AND `t1`.`t3_id` = `t2`.`t3_id`) 
WHERE (`t1`.`active` = True AND `t1`.`t3_id` = 1)
ORDER BY `t2`.`date_created`

Q1. I am using annotate to do this now in T1 model. Is there a better way to accomplish this?

T1.objects.filter(active=True, t3__id=1)
.annotate(t2_alias=FilteredRelation("t2", condition=Q(t2__t3_id=F("t3_id"))))
.order_by("t2_alias__date_created")

This gives me

SELECT `t1`.`id`, `t1`.`t3_id`, `t1`.`active`
FROM `t1`
INNER JOIN `t2` t2_alias ON (t2_alias.`t1_id` = `t1`.`id` AND `t1`.`t3_id` = t2_alias.`t3_id`) 
WHERE (`t1`.`active` = True AND `t1`.`t3_id` = 1)

Q2. The problem with the above solution is that, If I want to abstract this annotate call into a custom Manager in model T1, the user of this T1 model now needs to use the alias t2_alias by calling .order_by("t2_alias__date_created")
With .order_by("t2__date_created") the ORM would just trigger an extra INNER JOIN with T2 What can I do here

Thanks in advance

2

Q1 - You could use filter() function.

T1.objects.filter(T2_model_name__fk=T1_model_name__pk, T2_model_name__fk_T3=F("fk_T3"))

You could use values() function.

T1.objects.filter(T2_model_name__fk=T1_model_name__pk, T2_model_name__fk_T3=F("fk_T3")).values(T1_model_name=F("T1_model_name"))
1 Like
3

add models.py and result example.

1 Like
4

Thanks for the response but it does not necessarily solve my problem. I reworded the post with some examples. Hope it clarifies my issue more

5

Just updated post. Thanks!

6

it’s a very bad idea

. t1 is not connected to t2.
. It is possible to sort by foreign key, but it is not a good method.

7

How so? t2 has a foreign key t1_id connected to t1?
Also I’m not sorting by foreign key.

8

So, you could use Prefetch to fetch related T2 objects with the additional condition t3_id=OuterRef('t3_id'), which achieves the same effect as your JOIN condition. The order_by('date_created') in the prefetch queryset ensures that the T2 objects are ordered correctly.

from django.db.models import Prefetch

optimized_query = T1.objects.filter(active=True, t3__id=1).prefetch_related(
    Prefetch(
        't2',
        queryset=T2.objects.filter(t3_id=OuterRef('t3_id')).order_by('date_created'),
        to_attr='ordered_t2'
    )
)

# To use the ordered results:
for t1 in optimized_query:
    for t2 in t1.ordered_t2:
        print(t2.date_created)

Then in your Manager you could use the Prefetch and an ordered_t2 property to the T1 model, which returns either the prefetched and ordered T2 objects (if available) or falls back to a regular ordered queryset:

from django.db.models import Prefetch, OuterRef

class T1Manager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().filter(active=True)

    def with_ordered_t2(self, t3_id):
        return self.filter(t3__id=t3_id).prefetch_related(
            Prefetch(
                't2',
                queryset=T2.objects.filter(t3_id=OuterRef('t3_id')).order_by('date_created'),
                to_attr='ordered_t2'
            )
        )

class T1(Model):
    id = BigAutoField(primary_key=True)
    t3 = ForeignKey(T3, on_delete=deletion.CASCADE)
    active = BooleanField(default=True)

    objects = T1Manager()

    @property
    def ordered_t2(self):
        if hasattr(self, '_ordered_t2'):
            return self._ordered_t2
        return self.t2_set.order_by('date_created')

# Usage:
queryset = T1.objects.with_ordered_t2(t3_id=1)

for t1 in queryset:
    for t2 in t1.ordered_t2:
        print(t2.date_created)
9 
from django.db.models import Prefetch

optimized_query = T1.objects.filter(active=True, t3__id=1).prefetch_related(
    Prefetch(
        't2',
        queryset=T2.objects.filter(t3_id=OuterRef('t3_id')).order_by('date_created'),
        to_attr='ordered_t2'
    )
)

# To use the ordered results:
for t1 in optimized_query:
    for t2 in t1.ordered_t2:
        print(t2.date_created)

The Prefetch does not exactly work since t2 is not a field in T1 but prefetch_related makes sense. Is there a way to specify that when I prefetch T2 with T1, I want to join on 2 columns? T1.id = T2.t1_id AND T1.t3_id = T2.t3_id ?

10

So, you want to use prefetch and join on 2 columns: T1.id = T2.t1_id AND T1.t3_id = T2.t3_id ?

You can use the default related name for the reverse relationship from T1 to T2: t2_set:

from django.db.models import Prefetch

t3_id = 1  # Or however you want this value to be

optimized_query = T1.objects.filter(active=True, t3__id=t3_id).prefetch_related(
    Prefetch(
        't2_set',
        queryset=T2.objects.filter(t3_id=t3_id).order_by('date_created'),
        to_attr='ordered_t2'
    )
)

# To use the ordered results:
for t1 in optimized_query:
    for t2 in t1.ordered_t2:
        print(t2.date_created)