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.