Django ORM query with multiple inner join

Question

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

Todor · Accepted Answer · 2015-02-24 20:53:39Z

2

Can you try this:

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

edited Feb 24, 2015 at 20:53

answered Feb 24, 2015 at 19:51

Todor

16.2k6 gold badges61 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3851205 Over a year ago

HI, just tried it and received the following error: FieldError: Cannot resolve keyword 'instanciainstalacion_set' into field. Any idea of what might be happening? Thanks!!!!

Todor Over a year ago

Can you paste me the possible field choices suggested by the error?

user3851205 Over a year ago

Sure,here-s the whole error: FieldError: Cannot resolve keyword 'instanciainstalacion_set' into field. Choices are: activo, dispersion, id_punto_medida, id_punto_medida_padre, id_tipo_medicion, identificador, incidencia, instanciainstalacion, instanciapuntomedida, instinstalacionpuntomedida, nombre, nombre_archivo, plantilladispersion, plantilladisppuntomedida, potencia_nominal, puntomedida, puntomedidadispersion

Todor Over a year ago

ok.. lets try again, try to use instanciainstalacion instead of instanciainstalacion_set i've edited the answer too. Sorry but its hard when the code is in Spanish (i guess?) :) If you face similar error again for the other fields try use the closest suggest...

user3851205 Over a year ago

It's working now!!! Thanks, your answer helped me a lot understanding ORM syntaxis...and yes code in Spanish :)

|

Collectives™ on Stack Overflow

Django ORM query with multiple inner join

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related