Annotate queryset with whether matching related ob

问题:

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?

回答1:

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

回答2:

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.