id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
27332	Specifying additional ON arguments, and more flexibility with joins	MikiSoft	nobody	"For example, this raw SQL:
{{{
#!sql
SELECT event.*
FROM event
INNER JOIN
    business
    ON (event.business_id = business.id AND business.manager_id = 1)
LEFT JOIN
    'like'
    ON (event.id = object_id AND content_type_pk = 17 AND person_id = 1)
ORDER BY COALESCE('like'.date, event.'when') DESC;
}}}

Can't be translated to the **equivalent** Django ORM expression even if using `extra()` command, because there isn't anywhere a possibility to set additional arguments on `JOIN` clauses. And when I'm using  `filter(Q(argument) | ...)` it happens to always push all arguments to `WHERE` clause, which just causes a performance hit. I wish there was some parameter which would determine the destination of the argument, whether it has to go to the (last) `JOIN` clause, or to be put in `WHERE` (which goes by default). Also, I'm not able to comprehend how to perform correctly `LEFT JOIN` so in the end I have made some jumbled up expression which is completely inefficient (as it executes two queries, but it's the only way to do the same from above and get `QuerySet` in return):

{{{
#!python
Event.objects.filter(Q(pk__in=Like.objects.filter(person_id=1, content_type_id=17).prefetch_related('content_object').values_list('object_id', flat=True)) | Q(business__manager_id=1)).order_by(Coalesce('likes__date', 'when').desc())
}}}

I'm filing this issue because some (or probably many) people like me desperately need output in a form of a `QuerySet` because of the pagination and many other things `RawQuerySet` doesn't support, so using `raw()` isn't definitely an option in my case. Django has left me out of choice."	New feature	closed	Database layer (models, ORM)	dev	Normal	fixed		Simon Charette django@… ticosax@… josh.smeaton@…	Accepted	1	0	0	0	0	0