Django joins through table

[GerryM02]

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 = models.ManyToManyField(Articles, through="Detail", blank=True)

class Detail(models.Model):
     id = models.AutoField(primary_key=True)
     article = models.ForeignKey(articles, on_delete = models.CASCADE)
     intervention = 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.

[plopidou]

hello there,
I think the community may need more details as to what you are actually trying… i.e. a filter() code example (not screencaps please )

[KenWhitesell]

In general, if you have two models where you want everything, regardless of the presence of a relationship, it’ll be two queries.

Now, since Detail.Intervention_ID cannot be null, Interventions.objects.all() will give you all Interventions along with a way to access the related Detail objects.

Side note: I would strongly encourage you to adopt the Django coding conventions regarding field names (lower case) and related field names (name of the relationship, without an _id suffix).

In addition to the request for more clarity regarding your requirements, I’ll also point out:

@GerryM02

I tried with prefetch_related but it doesn’t seem to work.

The two clauses, select_related and prefetch_related are “performance enhancement” tools and do not affect the functionality of the query. They’re helpful in most cases, but never required.

[GerryM02]

Thank you very much. But how i can do if i want to show all (The interventions and their corresponding Details) in the same row of the same html table?

The problem is that if i use this Query:

QueryAll = Details.objects.all()

when an intervention is made, but without having used any article, the intervention itself is not displayed.

Thank you again.

[KenWhitesell]

Since there are (possibly) multiple Detail for each Intervention, you need to iterate over those individual instances of Detail if you want to render them for each Intervention.

See the docs and examples at Many-to-many relationships | Django documentation | Django

(For clarity, using the reverse of a ForeignKey relationship works effectively the same as a many-to-many. See Related objects reference | Django documentation | Django)

[GerryM02]

Sorry, I just corrected the models.py file because it contained these errors:
-Item_ID → Articles_ID;
-Item model doesn’t exist → Articles

In fact this is an example of output that I would like to be able to have:

[KenWhitesell]

You need to change your perspective on this.

What you have as output in every line of your sample table is an intervention. Therefore, you want to base your query for that table using Intervention (e.g. Intervention.objects.all()) and build your output from the results of that query.

[KenWhitesell]

And again, I want to point out that while you have:

@GerryM02

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')

For the “you” that will be looking at this 6+ months down the road, or any other developer who is going to be looking at this code, you want to change this to:

class Detail(models.Model):
     id = models.AutoField(primary_key=True)
     article = models.ForeignKey(Article, on_delete = models.CASCADE)
     intervention = models.ForeignKey(Intervention, on_delete = models.CASCADE)
     quantity = models.IntegerField(null=True, blank=True, default=1)

(with the corresponding changes to the other models)

[GerryM02]

Yes, you are right, I have to change my point of view because in fact it is precisely the interventions from which I have to start.
But then, how can I join the two tables?
Because if I make a query that requires all interventions, how do I then combine the articles used in each intervention?

Many thanks again for your precious availability.

[KenWhitesell]

Reread my response above at Django joins through table - #5 by KenWhitesell and the links referenced there.

[GerryM02]

Hi everyone.
Finally I found the answer on stackoverflow:

Django join through table even if don't match

But I sincerely thank all of you for the help and availability you have given me.