Re: Django builds incorrect sql-query

[Javier Guerra Giraldez]

On Mon, Oct 20, 2014 at 1:53 AM, Алексей Широков <alex...@gmail.com> wrote:
> SELECT ...
> FROM "document_document"
> INNER JOIN "document_sending" ON ("document_document"."id" =
> "document_sending"."document_id")
> INNER JOIN "document_sending" T4 ON ("document_document"."id" =
> T4."document_id")
> WHERE ("document_sending"."user_id" = 1
> AND T4."state" = 1)
> ORDER BY "document_document"."created" DESC
>
> Why...???
> it is incorrect!!!
>
> must be...
>
> SELECT ...
> FROM "document_document"
> INNER JOIN "document_sending" ON ("document_document"."id" =
> "document_sending"."document_id")
> WHERE ("document_sending"."user_id" = 1
> AND "document_sending"."state" = 1)
> ORDER BY "document_document"."created" DESC


From the docs [1]:
"To handle both of these situations, Django has a consistent way of
processing filter() and exclude() calls. Everything inside a single
filter() call is applied simultaneously to filter out items matching
all those requirements. Successive filter() calls further restrict the
set of objects, but for multi-valued relations, they apply to any
object linked to the primary model, not necessarily those objects that
were selected by an earlier filter() call."


in other words, your query is equivalent to:

Document.objects.filter(sendings__user=1).filter(sendings__state=0b0000001)

here, `sendings__user` and `sendings__state` appear on different
`filter()` calls, so each can refer to independent `Sending` objects.

try:

Document.objects.filter(sendings__user=1, sendings__state=0b0000001)

in this case, `sendings__user` and `sendings__state` appear on the
same `filter()` call, so they refer to `user` and `state` fields of
the same `Sending` object.


[1]: https://docs.djangoproject.com/en/1.7/topics/db/queries/#spanning-multi-valued-relationships

--
Javier

[Алексей Широков]

Javier, I am unable combine 2 parameters in a single call filter(), because It is built on different levels of code.

Is there another solution???

[Javier Guerra Giraldez]

you can build up a dictionary and expand as parameters on a single
filter() call:

params = {}
params['sendings__user']=1
......
params['sendings__state']=0b0000001
......
qs = Document.objects.filter(**params)

i think you can also use a Q() object, but i'm not so sure about the
exact 'simultaneity' behavior.

--
Javier

[Алексей Широков]

Thank you very much Javier!!!

I've thought about it. I will try.