Opened 3 years ago

Last modified 2 years ago

#23854 new New feature

Add custom SelectRelated classes

Reported by: Anssi Kääriäinen Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Simon Charette Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

The idea is similar to the Prefetch object. One could for example do:

    qs = Post.objects.select_related(latest_comment=Latest('comments', by='post_date'))

where the models are Post, and a list of comments to that post with related_name='comments'. Django would then generate a query to fetch the latest comment for each post. The SQL generated could be for example:

SELECT post.*, latest_comment.*
  FROM post
  LEFT JOIN (select author.*
               from author
              where author.post_date = (select max(post_date) from author inner_author where inner_author.post_id = author.post_id
            ) latest_comment ON latest_comment.post_id = post.id

It should also be possible to do qs.order_by('latest_comment__post_date') and other query operations on the select_related "annotation".

To implement this the following things are needed:

  • necessary alterations to select_related() call
  • a way to do custom subselect joins (see #23853)
  • addition of "model annotations" to the query (so that resolve_ref() will know to resolve latest_comment to the custom select_related)
  • substantial changes to the way select_related() resolvation works in compiler and QuerySet classes

Change History (6)

comment:1 Changed 3 years ago by Tim Graham

Triage Stage: UnreviewedAccepted

comment:2 Changed 3 years ago by Carl Meyer

Anssi, can you explain why you think this functionality should be part of .select_related() rather than part of .annotate()?

It seems to me that your example is an annotation. Annotations may or may not involve related tables / joins, but that doesn't mean they should be done via select_related(). If this were implemented, how would one determine which types of query annotations would happen via annotate() and which via select_related()?

comment:3 Changed 3 years ago by Anssi Kääriäinen

The problem with using annotate() is that is is used to annotate a single value to the model, and in addition annotate works only for the main model of the query, that is you can't annotate on some already select_related() model. While this can be a bit limiting in some cases (say, two "latest related" annotations in a row), it isn't that big of a problem.

Another solution is a new method, for example annotate_related().

The good thing about this is that there is still plenty of time to discuss the API before the ORM can actually support "latest related" queries :)

comment:4 Changed 2 years ago by Simon Charette

Cc: Simon Charette added

comment:5 Changed 2 years ago by Tim Graham

See #25403 for a related or duplicate issue; in particular comment 4. If the solution for this ticket doesn't address that, please reopen that ticket.

comment:6 Changed 2 years ago by Julien Hartmann

Another use case for custom select related: selecting a specific item of a m2m relation with a through model.

class Item(Model):
    # stuff here
class Group(Model):
    items = ManyToManyField(Item, through='GroupItem')

class GroupItem(Model):
    group = ForeignKey(group)
    item = ForeignKey(item)
    order = PositiveIntegerField()
    class Meta:
        unique_together = [('group', 'item'), ('group', 'order')]

A way to select_related the item that has order==0 would prove very useful.

More generally speaking, being able to select_related on a relation to many, by providing an explicit constraint.

Last edited 2 years ago by Julien Hartmann (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top