Annotate queryset with whether matching related object exists

Question

I have two models with an explicit many-to-many relationship: a thing, auth.user, and a "favorite" model connecting the two. I want to be able to order my "thing"s by whether or not they are favorited by a particular user. In Sqlite3, the best query i've come up with is (roughly) this:

select 
    *, max(u.name = "john cleese") as favorited 
    from thing as t 
        join favorite as f on f.thing_id = t.id 
        join user as u on f.user_id = u.id
    group by t.id
    order by favorited desc
    ;

The thing tripping me up in my sql-to-django translation is the max(u.name = "john cleese") bit. As far as I can tell, Django has support for arithmatic but not equality. The closest I can come is a case statement that doesn't properly group the output rows:

Thing.objects.annotate(favorited=Case(
    When(favorites__user=john_cleese, then=Value(True)),
    default=Value(False),
    output_field=BooleanField()
))

The other direction I've tried is to use RawSQL:

Thing.objects.annotate(favorited=RawSQL('"auth_user"."username" = "%s"', ["john cleese"]))

However, this won't work, because (as far as I'm aware) there's no way to explicitly join the favorite and auth_user tables I need.

Is there something I'm missing?

score 6 · Accepted Answer · answered Sep 01 '17 at 13:22

6

This will achieve what you (or anyone else googling there way here) wants to do:

Thing.objects.annotate(
    favorited=Count(Case(
        When(
            favorites__user=john_cleese, 
            then=1
        ),
        default=0,
        output_field=BooleanField(),
    )),
)

answered Sep 01 '17 at 13:22

Tomas Walch

2,245
1
14
17

I guess you're right, but be aware that depending on the size of the underlying data annotate might be terribly slow. – normic Nov 25 '17 at 01:30

score 2 · Answer 2 · edited Jun 13 '21 at 07:24

From what I read in a related ticket, you can use subquery with the Exists query expression.

Exists is a Subquery subclass that uses an SQL EXISTS statement. In many cases it will perform better than a subquery since the database is able to stop evaluation of the subquery when a first matching row is found.

Assuming the middle model in your case of ManyToMany is called Favorite

from django.db.models import Exists, OuterRef

is_favorited_subquery = Favorite.objects.filter(
  thing_id = OuterRef('pk')
)

Thing.objects.annotate(favorited=Exists(is_favorited_subquery))

Then you can order by favorited attribute of the query.

score 0 · Answer 3 · answered Nov 15 '16 at 01:25

I'm not exactly sure what you're trying to achieve, but I would start it like this way.

from django.db import models
from django.contrib.auth.models import User

class MyUser(models.Model):             
    person = models.OneToOneField(User)

class Thing(models.Model):
    thingname = models.CharField(max_length=10)
    favorited_by = models.ManyToManyField(MyUser)

And in your view:

qs = MyUser.objects.get(id=pk_of_user_john_reese).thing_set.all()

Will give you all Thing objects of the given user.

You should have a look in the Django Docs for ManyToMany

I'm using Django for some years now in several smaller and even bigger Projects, but I have never used the RawSQL features. Most times I thought about it, I have had a mistake in my model design.

I hadn't thought of starting with the user & attacking the problem from that direction, but that approach filters out all things that are not favorited. I'd like to have all of my things in a queryset and be able to order them by favorited/not favorited. — tboz203, Nov 15 '16 at 15:03
I'm still unsure what you are trying to achieve, but if you only want to get all Things, you can have a queryset with all of them, the favorited objects are accessable then. — normic, Nov 15 '16 at 15:22

Annotate queryset with whether matching related object exists

3 Answers3

Linked