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
3665	select_related() should generate LEFT JOINs for foreign keys with null=True	semenov@…	Malcolm Tredinnick	"Consider a (simplified) real-life example:

{{{
class Ticket(models.Model): 
    is_resolved = models.BooleanField() 
    resolved_by = models.ForeignKey(User, null=True)
}}}
 
...a view which is showing all resolved tickets: 
 
{{{
def resolved_tickets(request): 
    tickets = Ticket.objects.filter(is_resolved=True).select_related() 
    return HttpResponse(......) 
}}}
 
...and a template which is doing something like: 
 
{{{
{% for t in tickets %} 
    {{ t }} resolved by {{ t.user }} 
{% endfor %} 
}}}

(I believe this is a very common use case; I've run into it several times while developing my first application!)

Since user field has null=True, select_related() doesn't do its job and the code generates N+1 queries. However, from SQL point of view, the whole bunch of queries can be replaced with a single simple LEFT JOIN query, and I would expect Django ORM to allow this.

I realize that under some circumstances, LEFT JOINs can be costly. I also realize that LEFT JOINs can hardly traverse through several nesting levels.
That's why, I would like to suggest the following (unobtrusive and backwards-compatible!) behaviour:

1. If select_related() called with depth argument (as of [4645]), the ''last'' level of recursion uses LEFT JOINs instead of INNER JOINs for foreign keys.

2. If select_related() called with fields argument (once the fields suggestion from #3275 is committed) and the list of fields contains nullable foreign key(s), these particular key(s) are joined using LEFT JOIN, and the recursion stops for them.

As far as I understand, this will neither break any old code, nor make any perfomance impact; while being very useful in terms of database load optimization."		closed	Core (Other)	1.1		duplicate	orm select_related left join		Accepted	0	0	0	0	0	0