CODEWITHSUNDEEP
djangoorminner-join
user3851205
user3851205

Reputation: 75

Django ORM query with multiple inner join

I want to be able to do queries involving multiple inner joins using Django ORM, here's my model (showing only relevant fields)

class PuntoMedida(models.Model):
    id_punto_medida = models.AutoField(primary_key=True,db_column='id_punto_medida')
    nombre = models.CharField(max_length=100, blank=False,db_column='nombre')
    class Meta:
        db_table = 'punto_medida'

class Instalacion(models.Model):
    id_instalacion = models.AutoField(primary_key=True,db_column='id_instalacion')
    activo = models.CharField(max_length=1, blank=False,default='1',db_column='activo')
    usuarios=models.ManyToManyField(User,through='InstalacionUsuario')
    class Meta:
        db_table = 'instalacion'

class InstanciaInstalacion(models.Model):
    id_instancia_instalacion = models.AutoField(primary_key=True,db_column='id_instancia_instalacion')
    id_instalacion = models.ForeignKey(Instalacion, blank=False, null=False,db_column='id_instalacion')
    puntos_medida=models.ManyToManyField('PuntoMedida',through='InstInstalacionPuntoMedida')
    activo = models.CharField(max_length=1, blank=True,default='1',db_column='activo')
    class Meta:
        db_table = 'instancia_instalacion'

class InstInstalacionPuntoMedida(models.Model):
    id= models.AutoField(primary_key=True,db_column='id')
    id_instancia_instalacion = models.ForeignKey(InstanciaInstalacion,db_column='id_instancia_instalacion', blank=False, null=False)
    id_punto_medida = models.ForeignKey('PuntoMedida',db_column='id_punto_medida', blank=False, null=False)
    class Meta:
        unique_together = ('id_instancia_instalacion','id_punto_medida')
        db_table = 'instancia_instalacion_punto_medida'

class InstalacionUsuario(models.Model):
    id= models.AutoField(primary_key=True,db_column='id')
    id_instalacion = models.ForeignKey(Instalacion,db_column='id_instalacion', blank=False, null=False)
    id_usuario = models.ForeignKey(User,db_column='id_usuario', blank=False, null=False)
    class Meta:
        unique_together = ('id_instalacion','id_usuario')
        db_table = 'instalacion_usuario'

I want to obtain all the objects PuntoMedida related to all the objects Instalacion that a certain user can see. Until now, I've been able to do this using a raw sql like this one:

SELECT a.id_punto_medida, a.nombre 
FROM punto_medida a
INNER JOIN instancia_instalacion_punto_medida b ON
a.id_punto_medida=b.id_punto_medida
INNER JOIN instancia_instalacion c ON
b.id_instancia_instalacion=c.id_instancia_instalacion
INNER JOIN instalacion d ON
c.id_instalacion=d.id_instalacion
INNER JOIN instalacion_usuario f ON
d.id_instalacion=f.id_instalacion 
AND c.activo='1'
AND d.activo='1'
and f.id_usuario=7

However, I am looking forward to stop using raw queries and only use Django ORM just in case I need to change my database in the future (currently using PostgreSQL, might migrate to Oracle). I've been trying to create the equivalent ORM query with no success. Any ORM expert out there than can help me with this syntax or give me an example of where to start?Thanks in advance...

Upvotes: 2

Views: 2415

Answers (1)

Todor
Todor

Reputation: 16050

Can you try this:

PuntoMedida.objects.filter(
    instanciainstalacion__activo='1',
    instanciainstalacion__id_instalacion__activo='1',
    instanciainstalacion__id_instalacion__instalacionusuario_set__id_usuario=7,
)

Upvotes: 2

Related Questions