I'm trying to construct a query which combines two filters on a
related model, and I'm having no luck. Hopefully somebody can see what
I'm doing wrong, and point me in the right direction.
I'd like to be able to specify two exclude criteria on a related
model, and have both criteria apply to the same related object. I know
that the QuerySet.exclude() method, if given multiple parameters, will
AND them all together, and exclude objects that satisfy all of the
criteria. However, if the parameters refer to related models, then two
parameters in an exclude() call can apply to different related
objects, both related to the same parent object.
To be more specific, I'd like to be able to pull a list of
BillingAccount objects which do not have a "Payment Received" event
logged in the last 45 days. I have a BillingAccount model, and a
BillingEvent model that is related to it:
class BillingEvent(models.Model):
date_created = models.DateTimeField(auto_now_add=True)
date_modified = models.DateTimeField(auto_now=True)
event_time = models.DateTimeField()
billing_account = models.ForeignKey(BillingAccount)
action = models.IntegerField(choices=BILLING_EVENT_CHOICES)
My instinct is to construct a query like this:
BillingAccount.objects.exclude(billingevent__action=12,
billingevent__event_time__gte=datetime.datetime.now()-
datetime.timedelta(days=45)))
(useless trivia: 12 is the numeric code for "Payment Received" in the
choices field)
Unfortunately, this excludes all of the accounts which have any
Payment Received events ever, and all of the accounts which have any
events at all in the last 45 days.
The SQL that django is producing looks like this: (from
django.db.connection.queries)
SELECT `billing_account`.`id`,
`billing_account`.`customer_id`,
... more fields ...
FROM `billing_account`
WHERE NOT (
`billing_account`.`id` IN (SELECT U1.`billing_account_id` FROM
`billing_event` U1 WHERE U1.`action` = 12 )
AND `billing_account`.`id` IN (SELECT U1.`billing_account_id` FROM
`billing_event` U1 WHERE U1.`event_time` >= 2009-05-25 15:23:45)
)
What I would like to see is a where clause like this:
WHERE NOT (
`billing_account`.`id` IN (SELECT U1.`billing_account_id` FROM
`billing_event` U1 WHERE U1.`action` = 12 AND U1.`event_time` >=
2009-05-25 15:23:45)
)
Is there any way to construct such a query, without resorting to raw
SQL? I've tried it a couple of ways with Q objects, with the same
results... I'm using django 1.0, so I haven't investigated F objects
at all, but I'm willing to upgrade if that's what it takes :)
Thanks,
Ian
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
