problem of join between my django ORM tables

[yaokouadio]

I am trying to join four tables where the three tables carry the primary key of the fourth table and this is my model.

 class patient(models.Model):

choix = (
(0, 0),
(1, 1),
(0.1, 0.1),
(0.15, 0.15),
(0.2, 0.2),
(0.25, 0.25),
(0.3, 0.3),
(0.4, 0.4),
(0.5, 0.5),
(0.6, 0.6),
(0.7, 0.7),
(0.8, 0.8),
(0.9, 0.9),

)
date_creation=models.DateField(auto_now_add=True,null=True)
Nom_patient=models.CharField(max_length=200,null=True)
date_naissance=models.DateField(max_length=200,null=True)
numero_telephone=models.CharField(max_length=30, null=True)
adresse=models.CharField(max_length=100, null=True)
matricule=models.CharField(max_length=200,default=“Pas de matricule”,null=True)
assurance=models.CharField(max_length=200,default=“Non assuré”,null=True)
compagnie=models.CharField(max_length=200,default=“Pas de compagnie”,null=True)
medecin=models.CharField(max_length=200,null=True)
pourcentage = models.FloatField(max_length=20,default=‘Pas de pourcentage’,null=True, choices=choix)
def str(self):
return self.Nom_patient
``

the second lab model

class labo(models.Model):

examen = (
(“Ac anti- HAV IgM B 70”, “Ac anti- HAV IgM B 70”),
(“Ac anti- HBc IgM B 70”, “Ac anti- HBc IgM B 70”),
(“Ac anti- HBe B 70”, “Ac anti- HBe B 70”),
(“Ac anti- HBs (Test qualitatif ) B 70”, “Ac anti- HBs (Test qualitatif ) B 70”),
(“Ac anti- HVs (Titrage) B 100”, “Ac anti- HVC (Titrage) B 100”),
(“Ac anti- HVC (Hépathie C) B 70”, “Ac anti- HVC (Hépathie C) B 70”),
(“Ag HBe B 70”, “Ag HBe 70”),
(“Ag HBs B 70”, “Ag HBs B 70”),
(“Antistreptolysine O ( ASLO) B 35”, “Antistreptolysine O (ASLO) B 35”),
(“Sérodiagnostic de Widal et Félix B 40”, “Sérodiagnostic de Widal et Félix B 40”),
("Sérologie Amibienne B 60 ", “Sérologie Amibienne B 60”),
(“Sérologie bilharzienne B 60”, “Sérologie bilharzienne B 60”),
(“Sérologie palustre B 60”, “Sérologie palustre B 60”),
("Sérologie de la syphilis ( TPHA - VDRL) B 40 ", "Sérologie de la syphilis ( TPHA - VDRL) B 40 "),
("Sérologie des Chlamydia B 160 ", “Sérologie des Chlamydia B 160”),
("Sérologie des mycoplasmes B 100 ", "Sérologie des mycoplasmes B 100 "),
(“Sérologie de la rubéole (IgG - IgM) B 120”, “Sérologie de la rubéole (IgG - IgM) B 120”),
(“Sérologie de la toxoplasmose (IgG - IgM) B 80”, “Sérologie de la toxoplasmose (IgG - IgM) B 80”),
(“Sérologie Cytomégalovirus CMV B 160”, “Sérologie Cytomégalovirus CMV B 160”),
("Sérologie herpétique (HSV 1 et HSV 2 ) B 160 ", "Sérologie herpétique (HSV 1 et HSV 2 ) B 160 "),
(“IgE totales B 150”, “IgE totales B 150”),
(“Facteur rhumatoïdes B 50”, “Facteur rhumatoïdes B 50”),
(“Prélèvement”, “Prélèvement”),
)
prix = (
(0, 0),
(100, 100),
(200, 200),
(250, 250),
(280, 280),
(300, 300),
(350, 350),
(400, 400),
(450, 450),
(2500, 2500),
)
date_labo=models.DateField(auto_now_add=True,null=True)
libelle_labo=models.CharField(max_length=1000,null=True,choices=examen)
activite_labo=models.CharField(max_length=1000,default=“Non”,null=True)
cotation=models.FloatField(null=True)
prix=models.FloatField(null=True,choices=prix)
patient=models.ForeignKey(patient, null=True, on_delete=models.SET_NULL)

def str(self):
return self.libelle_labo

the third model specialist

class specialite(models.Model):

libel = (
(“Non”, “Non”),
(“Cs Ped”, “Cs Ped”),
(“Cs Gyn”, “Cs Gyn”),
(“Echo Pelv”, “Echo Pelv”),
(“Cs + Echo Pelv”, “Cs + Echo Pelv”),
(“Echo Obst”, “Echo Obst”),
(“Cs+ Echo Obst”, “Cs+ Echo Obst”),
(“Cs Obst”, “Cs Obst”),
(“Echo Morph”, “echo morpho”),
(“Echo Mam”, “Echo Mam”),
(“Echo Monito”, “Echo Monito”),
(“Echo Abdom”, “Echo Abdom”),
)
date_specialite = models.DateField(auto_now_add=True,null=True)
libelle_specialiste = models.CharField(max_length=200, null=True, choices=libel)
activite_specialite=models.CharField(max_length=1000,default=“Non”,null=True)
prix_specialite=models.FloatField(null=True)
patient=models.ForeignKey(patient, null=True, on_delete=models.SET_NULL)

def str(self):
return self.libelle_specialiste

the fourth generalister model

class generalister(models.Model):

date_generaliste = models.DateTimeField(auto_now_add=True, null=True)
libelle_generaliste = models.CharField(max_length=200,null=True)
prix_generaliste = models.FloatField(null=True)
patient = models.ForeignKey(patient, null=True, on_delete=models.SET_NULL)

def str(self):
return self.libelle_generaliste

So my problem is that I can’t join these three tables at the same time and get the information in one. But I can put two tables in relation to each other which is this.

labo=labo.objects.all().select_related('patient')

but how to put these three tables together. Please help me.

[marcorichetta]

@yaokouadio

So my problem is that I can’t join these three tables at the same time and get the information in one. But I can put two tables in relation to each other which is this.

labo=labo.objects.all().select_related('patient')

but how to put these three tables together.

That depends on what information do you want.

At first sight, I can see your 3 models labo, specialite and generalister all share a relation with Patient, so you can use your patient to traverse between models and get the information you need.

[yaokouadio]

thank you for answering me and putting me on the right track
I would like to know if it is possible to link these three tables at the same time and get an answer.

[KenWhitesell]

Not a valid answer in one query, no.

You’ve got a case where you’ve got three different 1-many relationships. But those relationships are not transitive. In your models as described, the set of “labo” associated to “patient” has no relationship to the set of “specialite” associated with that same “patient”.

So, given a “patient”, you need to access the set of related fields through the implicit manager. If you have “patient” p, then the set of “labo” associated with that “patient” is p.labo_set.all(). The set of “specialite” would be p.specialite_set.all().

You can access all three in the same view - it just becomes three different queries. If that’s what you mean by “at the same time”, then this isn’t a problem.
(Note: This isn’t a Django issue, this is a more fundamental database issue.)

Side note 1: When posting code here, please enclose the code between lines of three backtick - ` characters. This means you’ll have a line of ```, then your code, then another line of ```. This allows the forum software to keep your code properly formatted.

Side note 2: I’d strongly recommend you become familiar with the PEP-8 coding standards - particularly regarding capitalization. Your class names should be captialized and your field names should be lower case. That convention makes it a lot easier to quickly understand a reference as being a class or a variable.

Side note 3: You’ll see references to the “prefetch_related” function as a performance enhancement. Using this function is only a benefit if you’re working with multiple instances of the root class of your query and needing to perform the reverse ForeignKey lookup for every instance of that query. There’s no benefit to using it if your view is processing a single “patient”.

[yaokouadio]

thank you very much i am really satisfied with your explanation