How to do "IF SELECT" with Django orm

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)

    objec开发者_JAVA百科ts = 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,

---

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.

---

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)

---

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

---

Though an old question, I found Case and When to be useful:

from django.db.models import Case, When, Q

Friendship.objects.values('id')\
  .annotate(friend=Case(When(from_user=X, then='to_user'), default='from_user'))\
  .filter(Q(to_user=X) | Q(from_user=X))

See Django doc on conditional expressions.