Django models and join sql tables

Question

i wanted to try using django instead of having to call my stored procedure on the DB.

So i created a new model, taking informations from two differents models

class TbMouvementinit(models.Model):
    id = models.AutoField(db_column='Id', primary_key=True)  # Field name made lowercase.
    dateheurecreat = models.TextField(db_column='DateHeureCreat')  # Field name made lowercase.
    dateheureclot = models.TextField(db_column='DateHeureClot', blank=True, null=True)  # Field name made lowercase.
    id_pesee = models.BigIntegerField(db_column='Id_Pesee')  # Field name made lowercase.
    id_proteine = models.BigIntegerField(db_column='Id_Proteine', blank=True, null=True)  # Field name made lowercase.
    id_humidite = models.BigIntegerField(db_column='Id_Humidite', blank=True, null=True)  # Field name made lowercase.
    id_espece = models.BigIntegerField(db_column='Id_Espece', blank=True, null=True)  # Field name made lowercase.
    id_produit = models.BigIntegerField(db_column='Id_Produit', blank=True, null=True)  # Field name made lowercase.
    id_codeechantillon = models.BigIntegerField(db_column='Id_CodeEchantillon', blank=True, null=True)  # Field name made lowercase.
    id_traitement = models.BigIntegerField(db_column='Id_Traitement', blank=True, null=True)  # Field name made lowercase.
    id_circuit = models.BigIntegerField(db_column='Id_Circuit', blank=True, null=True)  # Field name made lowercase.
    code_source = models.CharField(db_column='Code_Source', max_length=20, blank=True, null=True)  # Field name made lowercase.
    code_destination = models.CharField(db_column='Code_Destination', max_length=20, blank=True, null=True)  # Field name made lowercase.
    nomos = models.CharField(db_column='NomOS', max_length=30, blank=True, null=True)  # Field name made lowercase.
    codesite = models.CharField(db_column='CodeSite', max_length=9, blank=True, null=True)  # Field name made lowercase.
    type_mouvement = models.CharField(db_column='Type_Mouvement', max_length=3, blank=True, null=True)  # Field name made lowercase.
    sous_domaine = models.CharField(db_column='Sous_Domaine', max_length=1, blank=True, null=True)  # Field name made lowercase.
    recolte = models.SmallIntegerField(db_column='Recolte', blank=True, null=True)  # Field name made lowercase.
    espece = models.CharField(db_column='Espece', max_length=9, blank=True, null=True)  # Field name made lowercase.
    code_variete = models.CharField(db_column='Code_Variete', max_length=10, blank=True, null=True)  # Field name made lowercase.
    code_tiers = models.CharField(db_column='Code_Tiers', max_length=9, blank=True, null=True)  # Field name made lowercase.
    num_bl_livreur = models.CharField(db_column='Num_Bl_Livreur', max_length=9, blank=True, null=True)  # Field name made lowercase.
    num_contrat_client = models.IntegerField(db_column='Num_Contrat_Client', blank=True, null=True)  # Field name made lowercase.
    num_oe = models.IntegerField(db_column='Num_OE', blank=True, null=True)  # Field name made lowercase.
    code_transporteur = models.CharField(db_column='Code_Transporteur', max_length=9, blank=True, null=True)  # Field name made lowercase.
    immat_transporteur = models.CharField(db_column='Immat_Transporteur', max_length=10, blank=True, null=True)  # Field name made lowercase.
    poids_charge = models.FloatField(db_column='Poids_Charge', blank=True, null=True)  # Field name made lowercase. This field type is a guess. Modified
    poids_vide = models.FloatField(db_column='Poids_Vide', blank=True, null=True)  # Field name made lowercase. This field type is a guess. Modified
    code_caract1 = models.CharField(db_column='Code_Caract1', max_length=5, blank=True, null=True)  # Field name made lowercase.
    val_caract1 = models.FloatField(db_column='Val_Caract1', blank=True, null=True)  # Field name made lowercase. This field type is a guess. Modified
    code_caract2 = models.CharField(db_column='Code_Caract2', max_length=5, blank=True, null=True)  # Field name made lowercase.
    val_caract2 = models.FloatField(db_column='Val_Caract2', blank=True, null=True)  # Field name made lowercase. This field type is a guess. Modified
    code_caract3 = models.CharField(db_column='Code_Caract3', max_length=5, blank=True, null=True)  # Field name made lowercase.
    val_caract3 = models.FloatField(db_column='Val_Caract3', blank=True, null=True)  # Field name made lowercase. This field type is a guess. Modified
    code_caract4 = models.CharField(db_column='Code_Caract4', max_length=5, blank=True, null=True)  # Field name made lowercase.
    val_caract4 = models.FloatField(db_column='Val_Caract4', blank=True, null=True)  # Field name made lowercase. This field type is a guess. Modified
    code_caract5 = models.CharField(db_column='Code_Caract5', max_length=5, blank=True, null=True)  # Field name made lowercase.
    val_caract5 = models.FloatField(db_column='Val_Caract5', blank=True, null=True)  # Field name made lowercase. This field type is a guess. Modified
    code_caract6 = models.CharField(db_column='Code_Caract6', max_length=5, blank=True, null=True)  # Field name made lowercase.
    val_caract6 = models.FloatField(db_column='Val_Caract6', blank=True, null=True)  # Field name made lowercase. This field type is a guess. Modified
    code_caract7 = models.CharField(db_column='Code_Caract7', max_length=5, blank=True, null=True)  # Field name made lowercase.
    val_caract7 = models.FloatField(db_column='Val_Caract7', blank=True, null=True)  # Field name made lowercase. This field type is a guess. Modified
    code_caract8 = models.CharField(db_column='Code_Caract8', max_length=5, blank=True, null=True)  # Field name made lowercase.
    val_caract8 = models.FloatField(db_column='Val_Caract8', blank=True, null=True)  # Field name made lowercase. This field type is a guess. Modified
    code_caract9 = models.CharField(db_column='Code_Caract9', max_length=5, blank=True, null=True)  # Field name made lowercase.
    val_caract9 = models.FloatField(db_column='Val_Caract9', blank=True, null=True)  # Field name made lowercase. This field type is a guess. Modified
    code_caract10 = models.CharField(db_column='Code_Caract10', max_length=5, blank=True, null=True)  # Field name made lowercase.
    val_caract10 = models.FloatField(db_column='Val_Caract10', blank=True, null=True)  # Field name made lowercase. This field type is a guess. Modified
    code_cel = models.CharField(db_column='Code_Cel', max_length=5, blank=True, null=True)  # Field name made lowercase.
    num_operation = models.IntegerField(db_column='Num_Operation', blank=True, null=True)  # Field name made lowercase.
    datetime2supv = models.CharField(db_column='DateTime2SUPV', max_length=20, blank=True, null=True)  # Field name made lowercase.
    num_lot_supv = models.BigIntegerField(db_column='Num_Lot_SUPV', blank=True, null=True)  # Field name made lowercase.
    user_supv = models.CharField(db_column='User_SUPV', max_length=18, blank=True, null=True)  # Field name made lowercase.


class TbEspece(models.Model):
    id = models.AutoField(db_column='Id', primary_key=True)  # Field name made lowercase.
    recolte = models.SmallIntegerField(db_column='Recolte')  # Field name made lowercase.
    groupe = models.CharField(db_column='Groupe', max_length=3)  # Field name made lowercase.
    categorie = models.CharField(db_column='Categorie', max_length=3)  # Field name made lowercase.
    code = models.CharField(db_column='Code', max_length=3)  # Field name made lowercase.
    libelle = models.CharField(db_column='Libelle', max_length=30)  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'TB_Espece'
        app_label = 'erp'

class ResumeMvtActif(models.Model):
    mvt = models.ForeignKey(TbMouvementinit)
    espece = models.ForeignKey(TbEspece)

And then call it by using just

mouvements = ResumeMvtActif.objects.all()

But of course i get a Database error because i don't really have a ResumeMvtActif table.

How can i proceed to make this work ? do i have to create an extra table on my DB ? Is there a trick to tell Django that it's not a real table and just a join of two tables ?

Django models are not supposed to be used like that and just putting two ForeignKey fields in a model doesn't join the two tables. What do you want to achieve with that and how do you want to join the tables? — Johannes Reichard, Sep 26 '17 at 13:30
Currently i connect to the DB and call stored procedure with python, but it should be done only with Django. i did saw that it wasn't supposed to be used like that, i should have a first table containing the foreignKey from the table i want to join right ? — Girard clément, Sep 26 '17 at 13:34
So what you created was just a new model / table referencing two other models. I don't think that this will ever work like you expected it to work. Here someone tried mixing Django ORM and stored procedures: https://stackoverflow.com/questions/805393/what-is-the-best-way-to-access-stored-procedures-in-djangos-orm — Johannes Reichard, Sep 26 '17 at 13:52

Django models and join sql tables

0 Answers0