problem of join between my django ORM tables

Using Django Using the ORM
1

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.

2

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.

3

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.

4

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”.

1 Like
5

thank you very much i am really satisfied with your explanation