How to use JOIN in Postgresql Query in Django ORM

i need help to build a queryset for the following problem:

I have 2 different models:

class parentElement(models.Model):
    name  = models.CharField(max_length=50)
    date  = models.DateField()
    .... other fields

class childElement(models.Model):
    name = models.CharField(max_length=50)
    date = models.DateField()
    type = models.PositiveSmallIntegerField(
        choices=types.CHOICES, null=True, blank=True
    )
    parent_id = models.ForeignKey(
        "parentElement", related_name="rel_parent", on_delete=models.CASCADE
    )
    .... other fields

And I want to have the following query realized with the Django ORM (not in a raw SQL query):

SELECT m.*, t1.date as type1_date, t2.date as type2_date FROM parentElement m
  LEFT INNER JOIN childElement t1 ON t1.parent_id = m.id AND t1.type = 1
  LEFT OUTER JOIN childElement t2 ON t2.parent_id = m.id AND t2.type = 2

I don't want to use the .raw(), is there a solution with .annotate() and .extra()?

from Recent Questions - Stack Overflow https://ift.tt/3fcZw6z
https://ift.tt/eA8V8J