Django one-to-one left join is null?

Question

I have models similar to django:

class User(models.Model):
  name = models.CharField(max_length = 128)

class Message(models.Model):
  sender = models.ForeignKey(User, related_name = 'messages_sent')
  recipient = models.ForeignKey(User, related_name = 'messages_recieved')
  subject = models.CharField(max_length = 128)
  body = models.CharField(max_length = 3500)

class Response(models.Model):
  message = models.OneToOneField(Message, primary_key = True)
  reply = models.TextField()

and I'm trying to get all the messages for a user who has no response, something that I would write in SQL:

select * from user u
         join message m on (u.id = m.recipient_id)
         left join response r on (m.id = r.message_id)
where r.message_id = null

I would think that the natural way to do this would be:

u.messages_recieved.filter(response = None)

or

u.messages_recieved.filter(response__message_id__isnull = True)

but the generated SQL always ends:

WHERE ("project_message"."recipient_id" = 1  AND "project_message"."id" IS NULL)

Am I doing something stupid, or is it a bug in Django?

Answer 1

Try:

user.messages_recieved.filter(response__isnull=True)

resulting query:

SELECT "messaging_message"."id", "messaging_message"."sender_id", "messaging_message"."recipient_id", "messaging_message"."subject", "messaging_message"."body" FROM "messaging_message" LEFT OUTER JOIN "messaging_response" ON ("messaging_message"."id" = "messaging_response"."message_id") WHERE ("messaging_message"."recipient_id" = 1  AND "messaging_response"."message_id" IS NULL)

which I think is right. It really does the left outer join, and then checks the strings with the null identifier of the response message.

u.messages_recieved.filter(response=None)

works great.

I am using django 1.1 RC, but this should work in 1.0+.

Answer 2

-, :

u.messages_received.filter(~Q(response__id__gt=0))