Django - how can I do this query in the ORM

Question

Hello everybody I'm trying to make this query:

SELECT * 
FROM dashboard_informe inf
INNER JOIN dashboard_servicio serv ON inf.servicio_id = serv.id
WHERE serv.nombre LIKE 'Inventario' AND inf.nombre LIKE 'Inventario%'

and I don't know how to make it, I tried this:

b = Servicio.objects.all().values_list('id')
r = Informe.objects.filter(servicio_id=b)

And it gives me an error: 'The QuerySet value for an exact lookup must be limited to ' ValueError: The QuerySet value for an exact lookup must be limited to one result using slicing.

There are someway to do that? or maybe is better create a Store Procedure with that query?

Forward thanks!

Jieter · Answer 1 · 2018-06-26T15:17:29.240

I think you are looking for the __in field lookup:

b = Servicio.objects.filter(nombre__startswith='Inventario').values('id')
r = Informe.objects.filter(servicio_id__in=b)

Django will convert the first query to a subquery resulting in SQL looking something like this:

SELECT ... 
FROM dashboard_informe inf 
WHERE inf.id IN (
    SELECT s.id 
    FROM dashboard_servicio s 
    WHERE s.nombre LIKE 'Inventario%'
);

score 0 · Answer 2 · edited Jun 26 '18 at 15:18

0

SELECT * 
FROM dashboard_informe inf
INNER JOIN dashboard_servicio serv ON inf.servicio_id = serv.id
WHERE serv.nombre LIKE 'Inventario' AND inf.nombre LIKE 'Inventario%'

r = Informe.objects.filter(servicio__nombre='Inventario', nombre__startswith='Inventario')

edited Jun 26 '18 at 15:18

Serkan Arslan

13,158
4
29
44

answered Jun 26 '18 at 14:55

Jonathan Le Cornichone

342
2
19

score -1 · Answer 3 · answered Jun 26 '18 at 14:47

-1

Better to use values_list()

b = Servicio.objects.all().values_list('id', flat=True) r = Informe.objects.filter(servicio_id__in=b)

answered Jun 26 '18 at 14:47

Andrey

9
1

Can you _explain_ why `values_list` is better? – Jieter Jun 26 '18 at 14:50
https://stackoverflow.com/questions/37205793/django-values-list-vs-values – Andrey Jun 26 '18 at 14:55
Yes, there might be a little performance gain when using the result of `b` in python, but in this case, django converts this to a subquery, with the exactly same sql. It will be something like `SELECT ... FROM dashboard_informe inf WHERE inf.id IN (SELECT s.id FROM dashboard_servicio s WHERE s.nombre LIKE 'Inventario%';` – Jieter Jun 26 '18 at 14:58

Django - how can I do this query in the ORM

3 Answers3