Opened 8 years ago

Closed 6 years ago

Last modified 6 years ago

#26426 closed New feature (fixed)

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 (5)

comment:1 by Alex Hill, 8 years ago

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 by Shai Berger, 8 years ago

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 by Tim Graham, 8 years ago

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

Something like #25590?

comment:4 by Tim Graham, 8 years ago

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

comment:5 by Josh Smeaton, 6 years ago

Resolution: fixed
Status: newclosed

This specific issue has been fixed with the addition of Exists expressions: https://docs.djangoproject.com/en/stable/ref/models/expressions/#exists-subqueries

The more general problem of supporting custom joins is: #29262.

Last edited 6 years ago by Tim Graham (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top