Opened 20 months ago

Last modified 14 months ago

#26426 new New feature

Add a way to customize a QuerySet's joins

Reported by: Charlie DeTar Owned by: nobody
Component: Database layer (models, ORM) Version: 1.9
Severity: Normal Keywords: QuerySet.extra
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

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,)
)

Change History (4)

comment:1 Changed 20 months ago by 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,)))

comment:2 Changed 20 months ago by 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).

comment:3 Changed 20 months ago by Tim Graham

Component: UncategorizedDatabase layer (models, ORM)
Type: UncategorizedNew feature

Something like #25590?

comment:4 Changed 20 months ago by Tim Graham

Description: modified (diff)
Summary: Use case for QuerySet.extra: annotate query with the existence of a relationAdd a way to customize a QuerySet's joins
Triage Stage: UnreviewedAccepted
Note: See TracTickets for help on using tickets.
Back to Top