CODEWITHSUNDEEP
djangodjango-querysetdjango-filter
Peter
Peter

Reputation: 65

django sub query filter using value from field in parent query

I have following models:

class Domain(models.Model):
    name = models.CharField(...)
    plan = models.ForeignKey(Plan, ....)

class Plan(models.Model):
    name = models.CharField(...)
    num_ex_accounts = models.IntegerField(...)

class PlanDetails(models.Model):
    accounts = models.IntegerField()
    plan = models.ForeignKey(Plan, ....)

class Mailbox(models.Model):
    domain = models.ForeignKey(Domain, ...)

Any domain has a plan, any plan has N plan details which has accounts value for create mailboxes using a domain, I want to get in a queryset domains which exceed the accounts value, using raw sql the sql is like:

SELECT domain
FROM domain, plan 
WHERE plan.id = domain.plan_id 
  AND (
    SELECT SUM(accounts) 
    FROM plandetails WHERE plandetails.plan_id=plan.id
  ) 
  <= 
  (
    SELECT COUNT(*) 
    FROM mailbox WHERE mailbox.domain_id=domain.id
  )

I tried in django something like this:

domains = Domain.objects.filter(
    Q(
        PlainDetails.objects.filter(plan = Domain.plan).aggregate(Sum('accounts')) <=
        Mailbox.objects.filter(domain = Domain).count()
    )
)

But it doesn't works, it throws an error about the Domain.plan, is there a way to reference that field value from parent query in the sub-query? is this queryset valid or is there another (better) approach? or I should use simply raw sql, what is the best option in this case?

Upvotes: 0

Views: 2365

Answers (1)

Wannabe Coder
Wannabe Coder

Reputation: 1497

If you are using Django 1.8 and higher, then try this:

Domain.objects.annotate(
    account_sum=Sum('plan__plandetails_set__accounts'),
    mailbox_count=Count('mailbox_set__id'))
).filter(
    account_sum__lte=F('mailbox_count')
)

Replace plandetails_set and mailbox_set with the appropriate related name if you explicitly specified any in the ForeignKey fields to Plan. Those two are just the defaults if none has been specified.

UPDATE For very complex queries, it might be better to just run the actual SQL using cursors. Django ORM is usually good enough, but depending on the needs, it may not be enough. Please view the docs.

Upvotes: 2

Related Questions