Opened 16 years ago

Closed 12 years ago

Last modified 10 years ago

#7231 closed New feature (wontfix)

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

Reported by: Davide "Design" Muzzarelli Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: queryset extra left join
Cc: elsdoerfer@…, sciyoshi@…, ssadler@…, bendavis78@…, simonotron, real.human@… Triage Stage: Design decision needed
Has patch: yes Needs documentation: yes
Needs tests: yes Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

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.

Attachments (8)

extra-join.diff (2.4 KB ) - added by Davide "Design" Muzzarelli <info@…> 16 years ago.
New parameter "join" for the QuerySet.extra() function.
extra-join-1.0.diff (2.4 KB ) - added by Davide "Design" Muzzarelli <info@…> 15 years ago.
New parameter "join" for the QuerySet.extra() function. For Django-1.0.
extra-join-1.1b.diff (2.6 KB ) - added by Davide "Design" Muzzarelli <info@…> 15 years ago.
Patch for django-1.1b.
extra-join-1.1b.2.diff (2.6 KB ) - added by tomas@… 15 years ago.
Modified extra join which will not interfere with django multilingual.
extra-join-1.1.diff (2.6 KB ) - added by Davide Muzzarelli 15 years ago.
info@…
extra-join-1.2-beta-SVN-12755.diff (3.1 KB ) - added by Ben Davis 14 years ago.
Updated for django 1.2-beta
extra-join-1.2-svn-12906.diff (123.8 KB ) - added by gordyt 14 years ago.
Updated for Django version 1.2, svn build number 12906
extra-join-1.2.patch (3.3 KB ) - added by jonozzz 14 years ago.
adapted for the 1.2 release

Download all attachments as: .zip

Change History (33)

by Davide "Design" Muzzarelli <info@…>, 16 years ago

Attachment: extra-join.diff added

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

comment:1 by Eric Holscher, 16 years ago

milestone: post-1.0
Triage Stage: UnreviewedDesign decision needed

comment:2 by Eric Holscher, 16 years ago

Needs documentation: set
Needs tests: set

comment:3 by Tobias McNulty, 15 years ago

Owner: changed from nobody to Tobias McNulty
Status: newassigned

see also #9049

comment:4 by Tobias McNulty, 15 years ago

Owner: changed from Tobias McNulty to nobody
Status: assignednew

by Davide "Design" Muzzarelli <info@…>, 15 years ago

Attachment: extra-join-1.0.diff added

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

comment:5 by miracle2k, 15 years ago

Cc: elsdoerfer@… added

comment:6 by Samuel Cormier-Iijima, 15 years ago

Cc: sciyoshi@… added

See also #7907

comment:7 by (none), 15 years ago

milestone: post-1.0

Milestone post-1.0 deleted

comment:8 by anonymous, 15 years ago

Cc: ssadler@… added

by Davide "Design" Muzzarelli <info@…>, 15 years ago

Attachment: extra-join-1.1b.diff added

Patch for django-1.1b.

by tomas@…, 15 years ago

Attachment: extra-join-1.1b.2.diff added

Modified extra join which will not interfere with django multilingual.

comment:9 by Ben Davis, 15 years ago

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

by Davide Muzzarelli, 15 years ago

Attachment: extra-join-1.1.diff added

info@…

comment:10 by info@…, 15 years ago

Component: Core frameworkDatabase layer (models, ORM)
Keywords: left added

Updated patch for Django v1.1.

by Ben Davis, 14 years ago

Updated for django 1.2-beta

by gordyt, 14 years ago

Updated for Django version 1.2, svn build number 12906

comment:11 by Ramiro Morales, 14 years ago

Patch needs improvement: set

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

comment:12 by Russell Keith-Magee, 14 years ago

Resolution: wontfix
Status: newclosed

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.

by jonozzz, 14 years ago

Attachment: extra-join-1.2.patch added

adapted for the 1.2 release

comment:13 by jonozzz, 14 years ago

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.

comment:14 by eads, 14 years ago

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.

comment:15 by Ben Davis, 13 years ago

Cc: bendavis78@… added

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().

comment:16 by erikrose, 13 years ago

+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.

comment:17 by Ben Davis, 13 years ago

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

comment:18 by Tai Lee, 13 years ago

Easy pickings: unset
Severity: Normal
Type: Uncategorized
UI/UX: unset

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.

comment:19 by Eloff, 13 years ago

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.

comment:20 by simonotron, 12 years ago

Cc: simonotron added

comment:21 by anonymous, 12 years ago

Resolution: wontfix
Status: closedreopened

comment:22 by Simon Charette, 12 years ago

Resolution: wontfix
Status: reopenedclosed
Type: UncategorizedNew feature

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.

comment:23 by Tai Lee, 12 years ago

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

comment:24 by Tai Lee, 12 years ago

Cc: real.human@… added

comment:25 by dms@…, 10 years ago

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

Note: See TracTickets for help on using tickets.
Back to Top