django sub query filter using value from field in parent query

Question

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?

what is a paret query? and also it's perfectly all right to use raw queries when you have to — e4c5
– e4c5, Commented Sep 16, 2016 at 5:38

Wannabe Coder · Accepted Answer · 2016-09-19 07:36:35Z

2

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.

edited Sep 19, 2016 at 7:36

answered Sep 16, 2016 at 5:07

Wannabe Coder

1,47712 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Peter Over a year ago

Thanks for your answer, in fact mailbox does not have relation wiht plan neither plan with mailbox, so get the mailbox_count as in your code not working, taking your code I think it could work if change that part as something like: mailbox_count=Mailbox.objects.filter(domain=DomainFromParentQuery).count() but I can not find how to reference DomainFromParentQuery or even if its posible

Wannabe Coder Over a year ago

oh sorry about that. i misread and thought that mailbox has a foreignkey to Plan. Since it has a fk to Domain, just use mailbox_set__id instead of plan__mailbox_set__id. I edited my post.

Peter Over a year ago

Ok, thanks again for the update, now the query runs ok but is not giving the same results as the raw sql, its givin wrong values for the sum and the count, it seems its no taking the right related records, I am studying the issue to find whats happen.

Wannabe Coder Over a year ago

Probably because of grouping. You can use .values() before .annotate() to specify how to group the records to compute for the aggregates. If unspecified, it should default to the primary key of your Domain model. You can also print the SQL query generated by invoking a queryset's query attribute, e.g. print(the_long_code_i_wrote_above.query)

Peter Over a year ago

Watching the sql generated, I found to get the right result the count and sum parts must be DISTINCT, for the count case, it can be done using Count('mailboxes_set_id', disctinct=true), but for Sum() adding such parameter does no difference, and it seems for sum theres no way to achieve the desire behavior

|

Collectives™ on Stack Overflow

django sub query filter using value from field in parent query

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related