How to do "IF SELECT" with Django orm

Question

i can't find a way to do the mysql "IF SELECT" with the django orm:

I have this model:

class Friendship(models.Model):
    to_user = models.ForeignKey(User, related_name="friends")
    from_user = models.ForeignKey(User, related_name="_unused_")
    added = models.DateField(default=datetime.date.today)

    objects = FriendshipManager()

    class Meta:
        unique_together = (('to_user', 'from_user'),)

Now to get all friends of user X i use this:

  q = Friendship.objects.raw('SELECT IF( from_user_id = X, `to_user_id` ,
 `from_user_id`) AS friend,id FROM `friends_friendship` WHERE `to_user_id` = X
 OR `from_user_id` = X' 

But the raw sqls do not return any objects, only ids. So it doesn't help me at all.

How could i use the django ORM to return a queryset of users?

Best regards,

Answer 1

I suggest you to have a look at the Many-to-Many relationship supported by Django's ORM.

It seems that your case fits the example from the Django Documentation here.

Answer 2

QuerySet objects have an extra method that lets you add raw SQL to the generated query. Something like the following should work for you.

qs = Friendship.objects.filter(Q(to_user=X) | Q(from_user=X))
qs = qs.extra(select={ "friend_id": "IF(from_user_id = X, `to_user_id`, `from_user_id`" })

When used like this the Friendship objects will have an extra parameter, friend, that contains the id of the friend. You might want to add a property to the Friendship that returns the User object, but there's not enough information in your question to say for certain that this is a good idea.

@property
def friend(self):
    return User.object.get(id=self.friend_id)

Answer 3

You can use the select_related method to follow the foreign key fields so when you access them an extra query is not made.

qs = Friendship.objects.filter(Q(to_user=X) | Q(from_user=X)).select_related(depth=1)
qs[0].to_user # doesn't cause another query to be made

If you then add a new method to the Friendship class you can get the correct User object. The values for both to_user and from_user will already be in memory so no queries are made when calling this function.

def friend_of(self, user):
    if self.to_user == user:
        return self.from_user
    else:
        return self.to_user