CODEWITHSUNDEEP
djangodjango-queryset
tboz203
tboz203

Reputation: 484

Annotate queryset with whether matching related object exists

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?

Upvotes: 5

Views: 5383

Answers (3)

Martin Faucheux
Martin Faucheux

Reputation: 1015

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.

Upvotes: 2

Tomas Walch
Tomas Walch

Reputation: 2305

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

Upvotes: 7

normic
normic

Reputation: 1538

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.

Upvotes: 0

Related Questions