Django left join m2m field.

Question

Here's my Model:

class User(models.Model):
    pass

class Item(models.Model):
    pass

class ItemVote(models.Model):
    user = models.ForeignKey(User)
    item = models.ForeignKey(Item)
    vote = models.BooleanField()

I want to retrieve a list of Items, and I want to know if the current user has voted for each Item. How do I alter my query object so that it will generate sql similar to:

SELECT ...
FROM items
LEFT OUTER JOIN item_votes ON (item_votes.user_id = ? AND
                               item_votes.item_id = items.id)

Answer 1

You cannot in plain django queries. This is a many to many relation ship, you can even specify it more clearly by doing something like this:

class Item(models.Model):
    votes = models.ManyToManyField(User, through='ItemVote', related='votedItems')

In a Many To Many relationship, we can speak of related sets (because there are multiple objects). While django can filter on related sets, something like:

Item.objects.filter(votes=request.user)

this will return you all Items that a user has voted for. However when you will list the .votes attribute in those objects, you will get all users that have voted for that item. the filtering is for the original object, never for the related set.

In a purely django way, you can expand my previous Item class to:

class Item(models.Model):
    votes = models.ManyToManyField(User, through='ItemVote', related='votedItems')

    def markVoted(self, user):
        self.voted = user in self.votes

And then call this method for every object. This will however create an additional query to the votes set for every object (and no, select_related does not work for many to many relationships).

The only way to solve this is to add some SQL to your queryset using the extra method, like this:

Item.objects.extra('voted' : 'SELECT COUNT(*) FROM app_itemvote WHERE app_itemvote.item_id = app_item.id AND app_itemvote.user_id=%d'%request.user.pk)