New "join" parameter for the "extra" QuerySet method

[Davide "Design" Muzzarelli]

This "join" parameter for the "extra" QuerySet method add the possibility to use any join type.

The implementation is simple and resolve just the base needs of custom SQL queries without lose the capabilities of the Django ORM.

This feature could open the possibility of the fast translation of models and many other uses like speed optimizations in views.

An example from the News application:

class News(models.Model):
    pub_date = models.DateField()

class NewsTranslation(model.Model):
    title = models.CharField(max_length=128)
    body = models.TextField()
    language_code = models.CharField(max_length=2, core=True)
    news = models.ForeignKey(News)

Getting all the news translated in english:

News.objects.extra(
    select={'title': 'news_newstranslation.title', 'body': 'news_newstranslation.body'},
    join=['LEFT JOIN news_newstranslation ON (news_news.id = news_newstranslation.news_id AND news_newstranslation.language_code = \'en\')']
)

The result of the query is a list of News with "title" and "body" attributes. The News without translation are also correctly fetched with the attributes "title" and "body" set at None.

The patch is post queryset-refactor.

[Davide "Design" Muzzarelli <info@…>]

New parameter "join" for the QuerySet.extra() function.

[Tobias McNulty]

see also #9049

[Davide "Design" Muzzarelli <info@…>]

New parameter "join" for the QuerySet.extra() function. For Django-1.0.

[Samuel Cormier-Iijima]

See also #7907

[(none)]

Milestone post-1.0 deleted

[Davide "Design" Muzzarelli <info@…>]

Patch for django-1.1b.

[tomas@…]

Modified extra join which will not interfere with django multilingual.

[Ben Davis]

Anyone willing to write a test for this? Would be nice to have this in for 1.2...

[Davide Muzzarelli]

info@…

[info@…]

Updated patch for Django v1.1.

[Ben Davis]

Updated for django 1.2-beta

[gordyt]

Updated for Django version 1.2, svn build number 12906

[Ramiro Morales]

Latest patch (extra-join-1.2-svn-12906.diff) is severely broken.

[Russell Keith-Magee]

extra() is already a nightmare to manage; I can't see how adding a join argument will make anything easier.

We added raw queries to get around cases where there are complex join requirements but you want to retain the capabilities of the ORM. I'm closing this proposal wontfix in the light of that addition.

[jonozzz]

adapted for the 1.2 release

[jonozzz]

I have to disagree with russellm about this patch. Sometimes raw queries are not reliable and even harder to maintain.
The extra() covers almost every piece of a query EXCEPT for the join piece and I believe this patch completes it.

In my case I had to replace the auto-generated sub-query SELECT...IN(SELECT...) with a INNER JOIN. By doing this my query improved 100x times on a really small, non-production, database.

Maybe if Django would've optimized the sub-query generation when using the 'in' lookup I wouldn't have needed this.
I looked at creating a custom aggregate, but that's just a pain in the ass and I think there's more work to be done in this area.

Thanks for the patch.

[eads]

Just putting in my 2 cents for jonozz here: I need to do subquery in the join clause of a RESTful application I'm building in with Django + Django Piston. Portability isn't a major (or even minor) issue for this app. I could fall back to raw SQL querying, but this seems like a much cleaner, easier-to-understand-and-maintain solution. It would be nice if the patch supported passing parameters to the custom join statement, as with params and select_params.

[Ben Davis]

Adding my vote for re-opening this patch. I've been using this patch for nearly two years, and have found it to be useful in several different cases. I agree with jonozzz that it completes the .extra() functionality, and allows for more flexibility than raw() can provide. For example, if I'm dynamically building my query using django, and I need to add custom joins to the existing queryset, it's much cleaner to use .extra() instead of .raw().

[erikrose]

+1 for us working on support.mozilla.com. We use left joins quite a bit (think optional attrs in a key/value store table), and it would be great to use the ORM for at least part of those queries.

[Ben Davis]

Discussion here: ​http://bit.ly/gpdYtV

[Tai Lee]

I can't reply to the discussion linked on Google Groups anymore, but wanted to add my comment that I have found many uses for .extra() and also hit the limitation of being unable to add left outer joins to a query generated by the ORM. I would not like to see it disappear in favour of raw SQL.

My current use case is for joining reverse generic relations on queries against a model that does not have a GenericRelation field back to the generic model (because I don't know which models it will be used with -- I can't add models to 3rd party code).

To require the whole query in raw SQL in order to join generic relations would mitigate the pluggability of the generic model in question.

[Eloff]

Just throwing in my vote that this really completes extra, and is a better tradeoff IMHO than switching to raw sql queries in many cases. I would like to see this in Django.

[Simon Charette]

Please don't anonymously re-open tickets closed by a core developer. If you want to argue about why this ticket should be re-opened feel free to discuss ​here. However I think there's already a clear consensus on not adding this parameter.

[Tai Lee]

If anyone else wants or needs this functionality, I've updated the patch on a branch at GitHub. I'll try to keep it updated periodically as long as I'm still using it myself.

​https://github.com/thirstydigital/django/tree/tickets/7231-extra-join

[dms@…]

Vating to have this patch. INNER JOIN is very common in applications and django have to implement better support for it.