id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35810	Provide `Select` class for `select_related` (like `Prefetch` exists for `prefetch_related`)	Bart van Andel		"Use case:

Given a model `Chat` with many linked `Message`s:

{{{
class Chat(models.Model):
    created_at = models.DateTimeField(auto_now_add=True)
    users = models.ManyToManyField(User)

    def last_message(self):
        return self.jobmatch_set.latest(""created_at"")

    def last_message_using_prefetched_set(self):
        return max(self.message_set.all(), key=lambda item: item.created_at, default=None)

    def last_message_using_prefetched_single_item_sets(self):
        assert hasattr(self, ""_last_message_set""), ""_last_message_set not populated"":
        return self._last_message_set[0] if len(self._last_message_set) > 0 else None

class Message(models.Model):
    created_at = models.DateTimeField(auto_now_add=True)
    chat = models.ForeignKey(Chat)
}}}

We'd like to load a list of chats with the latest message of each chat. This doesn't seem to be easily achievable at the moment. Example:

{{{
# Valid, but causes an additional trip for each chat:
chats = Chat.objects.all()
last_messages = [chat.last_message for chat in chats]

# Invalid:
chats = Chat.objects.select_related(""last_message"")

# Also invalid:
chats = Chat.objects.prefetch_related(""last_message"")

# Valid approach (2 queries), which may however load a huuuuge amount of data, so in general this should be avoided
# Also, there is no guarantee that the prefetched set is not filtered from the perspective of the last message getter property
chats = Chat.objects.prefetch_related(""message_set"")
last_messages = [chat.last_message_using_prefetched_set for chat in chats]

# Workaround (2 queries) using prefetch to get the single latest (if exist) message per chat:
chats = Chat.objects.prefetch_related(
    Prefetch(
        ""chat_set"",
        queryset=Chat.objects.order_by(""-created_at"")[:1],
        to_attr=""_last_match_set"",
    ),
)
last_messages = [chat.last_message_using_prefetched_single_item_sets for chat in chats]
}}}

I do appreciate that there are probably very good reasons why the invalid calls in the example above won't be able to work, unless maybe with some still-to-be-invented annotations.

Now, the `Prefetch` class was introduced quite recently to even make the above possible. It can only be used to prefetch _sets of items_ though, not _single values_, so using something like `.latest(""created_at"")` is out of the question.

Could something like that also be implemented to support this syntax? Basically the same effect as `Prefetch`, except with a single output (or None).

{{{
chats = Chat.objects.select_related(
    Select(
        ""chat_set"",
        queryset=Chat.objects.latest(""created_at""),
        to_attr=""last_match"",
    ),
)
}}}

Note: I couldn't find a similar question in the existing ticket list; if I missed something, I do apologize.
"	New feature	closed	Database layer (models, ORM)	5.1	Normal	wontfix	query optimization		Unreviewed	0	0	0	0	0	0