Code

Opened 6 years ago

Closed 23 months ago

Last modified 21 months 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: master
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@…> 6 years ago.
New parameter "join" for the QuerySet.extra() function.
extra-join-1.0.diff (2.4 KB) - added by Davide "Design" Muzzarelli <info@…> 5 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@…> 5 years ago.
Patch for django-1.1b.
extra-join-1.1b.2.diff (2.6 KB) - added by tomas@… 5 years ago.
Modified extra join which will not interfere with django multilingual.
extra-join-1.1.diff (2.6 KB) - added by Davide Muzzarelli 5 years ago.
info@…
extra-join-1.2-beta-SVN-12755.diff (3.1 KB) - added by bendavis78 4 years ago.
Updated for django 1.2-beta
extra-join-1.2-svn-12906.diff (123.8 KB) - added by gordyt 4 years ago.
Updated for Django version 1.2, svn build number 12906
extra-join-1.2.patch (3.3 KB) - added by jonozzz 4 years ago.
adapted for the 1.2 release

Download all attachments as: .zip

Change History (32)

Changed 6 years ago by Davide "Design" Muzzarelli <info@…>

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

comment:1 Changed 6 years ago by ericholscher

  • milestone set to post-1.0
  • Triage Stage changed from Unreviewed to Design decision needed

comment:2 Changed 6 years ago by ericholscher

  • Needs documentation set
  • Needs tests set

comment:3 Changed 6 years ago by tobias

  • Owner changed from nobody to tobias
  • Status changed from new to assigned

see also #9049

comment:4 Changed 6 years ago by tobias

  • Owner changed from tobias to nobody
  • Status changed from assigned to new

Changed 5 years ago by Davide "Design" Muzzarelli <info@…>

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

comment:5 Changed 5 years ago by miracle2k

  • Cc elsdoerfer@… added

comment:6 Changed 5 years ago by sciyoshi

  • Cc sciyoshi@… added

See also #7907

comment:7 Changed 5 years ago by anonymous

  • milestone post-1.0 deleted

Milestone post-1.0 deleted

comment:8 Changed 5 years ago by anonymous

  • Cc ssadler@… added

Changed 5 years ago by Davide "Design" Muzzarelli <info@…>

Patch for django-1.1b.

Changed 5 years ago by tomas@…

Modified extra join which will not interfere with django multilingual.

comment:9 Changed 5 years ago by bendavis78

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

Changed 5 years ago by Davide Muzzarelli

info@…

comment:10 Changed 5 years ago by info@…

  • Component changed from Core framework to Database layer (models, ORM)
  • Keywords left added

Updated patch for Django v1.1.

Changed 4 years ago by bendavis78

Updated for django 1.2-beta

Changed 4 years ago by gordyt

Updated for Django version 1.2, svn build number 12906

comment:11 Changed 4 years ago by ramiro

  • Patch needs improvement set

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

comment:12 Changed 4 years ago by russellm

  • Resolution set to wontfix
  • Status changed from new to closed

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.

Changed 4 years ago by jonozzz

adapted for the 1.2 release

comment:13 Changed 4 years ago by 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.

comment:14 Changed 4 years ago by 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.

comment:15 Changed 3 years ago by bendavis78

  • 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 Changed 3 years ago by 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.

comment:17 Changed 3 years ago by bendavis78

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

comment:18 Changed 3 years ago by mrmachine

  • Easy pickings unset
  • Severity set to Normal
  • Type set to 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 Changed 3 years ago by 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.

comment:20 Changed 2 years ago by simonotron

  • Cc simonotron added

comment:21 Changed 23 months ago by anonymous

  • Resolution wontfix deleted
  • Status changed from closed to reopened

comment:22 Changed 23 months ago by charettes

  • Resolution set to wontfix
  • Status changed from reopened to closed
  • Type changed from Uncategorized to New 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 Changed 21 months ago by mrmachine

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 Changed 21 months ago by mrmachine

  • Cc real.human@… added

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.