Hi,
I'm having problems fitting a certain query I want to do into the ORM
framework and I find myself having to resort to raw SQL. This
doesn't seem like such an uncommon type of query to do so I'm
wondering if someone knows of a better way to phrase it. What I want
to do is join through a table but put a negative constraint on items
in the join table.
Assume the following models:
class Person(models.Model):
email = models.CharField(unique=True, db_index=True,
max_length=255)
class Thread(models.Model):
started_by = models.ForeignKey(Person)
class Mail(models.Model):
data = models.TextField()
thread = models.ForeignKey(Thread, related_name='mails',
null=True, blank=True)
tags = models.ManyToManyField(Tag, related_name='mails')
sender = models.ForeignKey(Person, related_name='sent_mails',
null=True, blank=True)
recipients = models.ManyToManyField(Person,
related_name='received_mails')
Here is an English description of what I want to achieve:
"People who have received mails with tag T, excluding mails in threads
that they started themselves"
This is how I got it by inserting extra SQL clauses:
Person.objects.filter(received_mails__tags=T) \
.extra(tables=['app_thread']) \
.extra(where=[
'app_thread.id = app_mail.thread_id',
'app_thread.started_by_id !=
app_person.id']) \
.distinct()
Using exclude doesn't work since it doesn't refer to the previously
joined table, but introduces the table again with a new alias.
Person.objects.filter(received_mails__tags=T) \
.exclude
(received_mails__thread__started_by=F('id')) \
.distinct()
This actually means:
"People who have received mails with tag T, excluding *people* who
have started a thread themselves"
There's a weird asymmetry between filter and exclude where multiple
filter clauses all constrain the same table references but multiple
exclude clauses are independent.
I see that there used to be a ne (not equal) filter lookup but that it
was replaced with exclude. I could imagine a ne would do what I
wanted here. Something like:
Person.objects.filter(received_mails__tags=T) \
.filter
(received_mails__thread__started_by__ne=F('id')) \
.distinct()
Rowan
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
