Django join through table even if don't match

Question

I'm starting to use Django, but I'm just a beginner. I have a problem with Django Queries, and although I've done a lot of research online, I haven't found any answers that can help me.

Models.py:

class Articles(models.Model):
     ID = models.AutoField(primary_key=True)
     description = models.TextField()
     price = MoneyField(blank=True, null=True, decimal_places=2, max_digits=8, default_currency='EUR')
     class Meta:
        ordering = ('description',)
     def __str__(self):
         return self.description

class Interventions(models.Model):
     ID = models.AutoField(primary_key=True)
     start_date = models.DateField()
     description = models.TextField()
     Articles_ID = models.ManyToManyField(Articles, through="Detail", blank=True)

class Detail(models.Model):
     ID = models.AutoField(primary_key=True)
     article_id = models.ForeignKey(articles, on_delete = models.CASCADE)
     Intervention_ID = models.ForeignKey(Interventions, on_delete = models.CASCADE)
     quantity = models.IntegerField(null=True, blank=True, default='1')

I would like to be able to create a Query that takes all the records of the 'Interventions' model and all the records of the 'Details' table.

The problem is that if an intervention is made, but without having used any article, the intervention itself is not displayed.

How can I do? I tried with prefetch_related but it doesn't seem to work. Heartfelt thanks to everyone.

Example output Table:

Can you also add the Items model, is it supposed to be Items or Article ? — Julkar9, Jun 07 '23 at 17:59
Sorry I meant they were Articles (Item_ID is wrong --> Articles_ID) — GM02, Jun 08 '23 at 06:06

score 1 · Accepted Answer · answered Jun 08 '23 at 19:44

You are essentially looking for a left join, you can uses .values() to specify all the fields.

Interventions.objects.values(
    "ID", "start_date", "description", "Articles_ID__description", "detail__quantity"
)

The query is equivalent to this sql query

SELECT "interventions"."id",
       "interventions"."start_date",
       "interventions"."description",
       "articles"."description",
       "detail"."quantity"
FROM   "interventions"
       LEFT OUTER JOIN "detail"
                    ON ( "interventions"."id" =
                       "detail"."intervention_id_id" )
       LEFT OUTER JOIN "articles"
                    ON ( "detail"."article_id_id" = "articles"."id" )

Output :

<QuerySet [
{'ID': 4, 'start_date': datetime.date(2023, 6, 8), 'description': 'Try number one', 'Articles_ID__description': 'Article1', 'detail__quantity': 3},
{'ID': 4, 'start_date': datetime.date(2023, 6, 8), 'description': 'Try number one', 'Articles_ID__description': 'Article2', 'detail__quantity': 8},
{'ID': 5, 'start_date': datetime.date(2023, 6, 8), 'description': 'try number two', 'Articles_ID__description': None, 'detail__quantity': None},
{'ID': 6, 'start_date': datetime.date(2023, 6, 8), 'description': 'try number three', 'Articles_ID__description': 'Article2', 'detail__quantity': 10},
{'ID': 6, 'start_date': datetime.date(2023, 6, 8), 'description': 'try number three', 'Articles_ID__description': 'Article4', 'detail__quantity': 27},
{'ID': 6, 'start_date': datetime.date(2023, 6, 8), 'description': 'try number three', 'Articles_ID__description': 'Article32', 'detail__quantity': 1}
]>

A downside of this approach is that the Joins are not lazy.
Also use F model if you want to rename the fields

Thank you very much @Julkar9. Now the Query works perfectly! — GM02, Jun 12 '23 at 07:45

Django join through table even if don't match

1 Answers1