Add a way to customize a QuerySet's joins

[Charlie DeTar]

This ticket is just to document a use case for QuerySet.extra as requested by the docs: ​https://docs.djangoproject.com/en/1.9/ref/models/querysets/#extra

I have a Category model like this:

class Category(models.Model):
    name = models.CharField(max_length=200)
    followers = models.ManyToManyField(User)

I want to get a list of all categories, but to annotate each category with whether the currently logged in user is a "follower" of the category. Neither prefetch_related nor annotate work here, because I don't want to fetch nor aggregate over all "followers" (potentially many), I just want the presence of the current user. The extra query looks like this:

Category.objects.filter(...).extra(
    select={'is_following': '''EXISTS(
        SELECT "id" FROM "projects_category_followers" WHERE
            "projects_category_followers"."category_id"="projects_category"."id" AND
            "projects_category_followers"."user_id"=%s
    )'''},
    select_params=(request.user.id,)
)

[Alex Hill]

It's not a great deal better, it is possible using RawSQL in an annotation rather than extra:

from django.db.models.expressions import RawSQL

exists_sql = """EXISTS (
    SELECT "id" FROM "extra_category_followers"
    WHERE "extra_category_followers"."category_id"="extra_category"."id"
    AND "extra_category_followers"."user_id"=%s
)"""
Category.objects.all().annotate(is_following=RawSQL(exists_sql, (request.user.id,)))

[Shai Berger]

I agree that these aren't very intuitive and are less efficient that the extra or RawSQL, but technically,

Prefetch:

curr_user_query = User.objects.filter(id=request.user.id)
Category.objects.all().prefetch(Prefetch('followers', 
                                         queryset=curr_user_query,
                                         to_attr=is_following))

I am also quite certain an annotate solution can be found involving a variation (which I don't particularly care to get completely right) on Sum(Case(When(followers__id=Value(request.user.id), 1), default=0)).

But more generally -- this falls into a wide category of cases we should support, that of generalized joins (that is, joins whose conditions are specified by the user, and are not just along FK relationships).

(I'm not sure there's a ticket for generalized joins, although it has been mentioned before; if there is, this should be marked duplicate, and if there isn't, it should be accepted).

[Tim Graham]

Something like #25590?