[Answered ]-Select in (select ..) using ORM django

by

1👍

✅

You can traverse ForeignKey relations using double underscores (__) in Django ORM. For example, your query could be implemented as:

payments = invoicePayments.objects.filter(invoice__store_id=3, 
                                          date__range=["2016-05-01", "2016-05-06"])

I guess you renamed your classes to English before posting here. In this case, you may need to change the first part to factura__local=3.

As a side note, it is recommended to rename your model class to InvoicePayments (with a capital I) to be more compliant with PEP8.

👤Selcuk

1👍

Your mysql raw query is a sub query.

select  name where id in (select id from ...)

In mysql this will usually be slower than an INNER JOIN (refer : [http://dev.mysql.com/doc/refman/5.7/en/rewriting-subqueries.html]) thus you can rewrite your raw query as an INNER JOIN which will look like 1. 

SELECT ip.* FROM invoicepayments i INNER JOIN invoice i ON 
ip.invoice_id = i.id

You can then use a WHERE clause to apply the filtering.

The looping query approach you have tried does work but it is not recommended because it results in a large number of queries being executed. Instead you can do.

InvoicePayments.objects.filter(invoice__local=3, 
                                      date__range=("2016-05-01", "2016-05-06"))

I am not quite sure what ‘local’ stands for because your model does not show any field like that. Please update your model with the correct field or edit the query as appropriate.

To lean about __range see this https://docs.djangoproject.com/en/1.9/ref/models/querysets/#range

👤e4c5

Leave a comment