Select related on 3 tables in Django ORM

Question

I need some help to perform a select_related in Django framework. My model is:

models.py

class Richiesta(models.Model):
    # TIPOLOGIE_DISPOSITIVO can be = BK or SC
    codice = models.CharField(max_length=20, null=True, blank=True, unique=True)
    ufficio_registrazione = models.ForeignKey(UfficioRegistrazione, null=True, blank=False)
    tipologia = models.CharField(max_length=50, null=False, blank=False, choices=TIPOLOGIE_DISPOSITIVO, default=TIPOLOGIE_DISPOSITIVO[0][0])
    tipo = models.IntegerField(null=False, blank=False, choices=TIPO_CHOICES, default=TIPO_PRIMO_RILASCIO)
    data_produzione = models.DateTimeField(null=True, blank=True)

class UfficioRegistrazione(models.Model):
    cliente = models.ForeignKey(Cliente, null=False, blank=False)

class Cliente(models.Model):
    # provider can be = 1 or 2
    denominazione = models.CharField(max_length=100, null=False, blank=False)
    codice_cliente = models.PositiveIntegerField(null=False, blank=False, db_index=True)
    provider = models.IntegerField(null=False, blank=False, choices=PROVIDER_CHOICES)

and there is the raw sql query I need to perform with Django ORM:

select cms_richiesta.id, cms_richiesta.tipologia, cms_richiesta.data_produzione, 
cms_richiesta.ufficio_registrazione_id, cms_ufficioregistrazione.id, 
cms_ufficioregistrazione.cliente_id, cms_cliente.id, cms_cliente.provider, 
cms_cliente.denominazione, cms_cliente.codice_cliente
from cms_richiesta INNER JOIN cms_ufficioregistrazione on 
cms_richiesta.ufficio_registrazione_id=cms_ufficioregistrazione.id
INNER JOIN cms_cliente on cms_ufficioregistrazione.cliente_id = 
cms_cliente.id where data_produzione>'2011-01-01' and data_produzione<'2017- 
12-31' and cms_cliente.provider = 2 and cms_richiesta.tipologia='BK'

can someone help me?

Exprator · Accepted Answer · 2018-07-17 11:07:45Z

2

Richiesta.objects.filter(ufficio_registrazione__cliente__provider=2,tipologia='BK')

and rest filter can be added like that

answered Jul 17, 2018 at 11:07

Exprator

27.8k6 gold badges54 silver badges64 bronze badges

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

Comments

willeM_ Van Onsem · Accepted Answer · 2018-07-17 11:10:15Z

You can process this as:

from datetime import datetime

Richiesta.objects.select_related(
    'ufficio_registrazione',
    'ufficio_registrazione__cliente',
).filter(
    data_produzione__range=(datetime(2011, 1, 1), datetime(2017, 12, 30)),
    ufficio_registrazione__cliente__provider=2,
    tipologia='BK',
)

Typically the .select_related(..) is here not necessary, since we perform these JOINs already at the filtering part, but typically it is better to be explicit than implicit (especially in case you would later omit a condition, and still want these related models to be fetched).

It will retrieve Richiesta objects, but with related models already loaded.

You probably meant <= '2017-12-31' in which case the upper bound of the date range needs to be incremented as well.

Diego Vinícius · Accepted Answer · 2018-07-17 14:50:52Z

0

I dont want paste and copy other answer, but you can use .values() to extract only the columns that you want to work, if you think this is really necessary, because in template you will show only the data that you want

richiesta.values('tipologia', 'data_produzione'...) # when richiesta is the queryset

answered Jul 17, 2018 at 14:50

Diego Vinícius

2,2731 gold badge15 silver badges24 bronze badges

Collectives™ on Stack Overflow

Select related on 3 tables in Django ORM

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related