Add custom SelectRelated classes

[Anssi Kääriäinen]

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

[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()?

[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 :)

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

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