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