Optimize Django generated SQL query

Question

I have the these models:

class Person(models.Model):
    firstname = models.CharField(max_length=20)
    lastname = models.CharField(max_length=20, blank=True, null=True)

class relation(models.Model):
    parent = models.ForeignKey(Person,on_delete=models.PROTECT, related_name='children')
    rel = models.CharField(max_length=1)
    child = models.ForeignKey(Person, on_delete=models.PROTECT, related_name='parents')

    class Meta:
        unique_together = (('parent', 'child'),)

where relation model represent the relation from parent to child. If I want to get all brothers of a person I would use this query:

tid = 198
brothers = relation.objects.filter(~Q(child = tid), parent__children__child = tid).values('child__firstname', 'child__lastname')

The above query generates this SQL query:

SELECT "eftapp_person"."firstname", "eftapp_person"."lastname" FROM "eftapp_relation" INNER JOIN "eftapp_person" ON ("eftapp_relation"."child_id" = "eftapp_person"."id") INNER JOIN "eftapp_person" T3 ON ("eftapp_relation"."parent_id" = T3."id") INNER JOIN "eftapp_relation" T4 ON (T3."id" = T4."parent_id") WHERE (NOT ("eftapp_relation"."child_id" = 198) AND T4."child_id" = 198)

I added to my models an unmanaged model pointing to relation table, the intent was to optimize the generated SQL query.

class relcalc(models.Model):
pp = models.OneToOneField(relation, on_delete=models.PROTECT, db_column='parent_id', to_field='parent', related_name='pcn')
cc = models.OneToOneField(relation, on_delete=models.PROTECT, db_column='child_id', to_field='child', related_name='cps')
r = models.CharField(max_length=1, db_column='rel')

class Meta:
    db_table = 'eftapp_relation'
    managed = False

To query get brothers of a person:

brothers = relation.objects.filter(~Q(child = tid), pcn__cc = tid).values('child__firstname', 'child__lastname')

and the generated SQL query is:

SELECT "eftapp_person"."firstname", "eftapp_person"."lastname" FROM "eftapp_relation" INNER JOIN "eftapp_person" ON ("eftapp_relation"."child_id" = "eftapp_person"."id") INNER JOIN "eftapp_relation" T3 ON ("eftapp_relation"."parent_id" = T3."parent_id") WHERE (NOT ("eftapp_relation"."child_id" = 198) AND T3."child_id" = 198)

You can see that the later SQL query is 1 table less than the previous query. This tactic works perfectly in python shell but if I run Django server I get the following errors:

Watching for file changes with StatReloader
Performing system checks...

Exception in thread django-main-thread:
Traceback (most recent call last):
  File "/usr/lib/python3.7/threading.py", line 917, in _bootstrap_inner
    self.run()
  File "/usr/lib/python3.7/threading.py", line 865, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/local/lib/python3.7/dist-packages/django/utils/autoreload.py", line 53, in wrapper
    fn(*args, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/django/core/management/commands/runserver.py", line 118, in inner_run
    self.check(display_num_errors=True)
  File "/usr/local/lib/python3.7/dist-packages/django/core/management/base.py", line 442, in check
    raise SystemCheckError(msg)
django.core.management.base.SystemCheckError: SystemCheckError: System check identified some issues:

ERRORS:
eftapp.relcalc.cc: (fields.E311) 'relation.child' must be unique because it is referenced by a foreign key.
    HINT: Add unique=True to this field or add a UniqueConstraint (without condition) in the model Meta.constraints.
eftapp.relcalc.pp: (fields.E311) 'relation.parent' must be unique because it is referenced by a foreign key.
    HINT: Add unique=True to this field or add a UniqueConstraint (without condition) in the model Meta.constraints.

System check identified 2 issues (0 silenced).

Any ideas on how to get an optimized query without generating errors in Django server?

Answer 1

After further research; I found that using the raw() manager is the best way to get the optimum query, this method takes a raw SQL query and returns RawQuerySet instance with object instances.

brothers = Person.objects.raw("SELECT eftapp_person.id, eftapp_person.firstname, eftapp_person.lastname\
        FROM eftapp_relation INNER JOIN eftapp_person ON (eftapp_relation.child_id = eftapp_person.id)\
            INNER JOIN eftapp_relation T2 ON (eftapp_relation.parent_id = T2.parent_id)\
        WHERE (eftapp_relation.child_id <> %s AND T2.child_id = %s)",[tid,tid])