0

I am trying to annotate User with the count of delayed leads objects. The calculation of delayed leads is complex (uses RawSQL) implemented using a custom model manager. Hence, I am trying to implement this using a subquery.

sq = Lead.delayed.filter(assigned_to_id=OuterRef('pk'))
User.objects.annotate(num=Count(Subquery(sq.count())))

However, I keep getting this error:

 ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

UPDATE:

I tried adding only('id') so my code:

sq = Lead.delayed.filter(assigned_to_id=OuterRef('id')).only('id')
User.objects.annotate(num=Count(Subquery(sq)))

This generated the sql query:

SELECT `auth_user`.`id`, `auth_user`.`username`, `auth_user`.`first_name`,
`auth_user`.`last_name`, COUNT((SELECT U0.`id` FROM
`lead` U0 WHERE U0.`assigned_to_id` = (`auth_user`.`id`))) AS `count`
FROM `auth_user` GROUP BY `auth_user`.`id`;

This is throwing error:

ERROR 1242 (21000): Subquery returns more than 1 row

I would like to have my query generated as:

SELECT `auth_user`.`id`, `auth_user`.`username`, `auth_user`.`first_name`,
`auth_user`.`last_name`, (SELECT COUNT(U0.`id`) FROM `marketing_lead` U0 WHERE
(more complex conditions here) U0.`assigned_to_id` = (`auth_user`.`id`)) AS `count`
FROM `auth_user` GROUP BY `auth_user`.`id`;

How can I acheive that using django ORM?

Husain Basrawala
  • 1,757
  • 15
  • 21
  • I guess your problem is that you don't specify 'only' function of your subquery. You must use Lead.delayed.filter(assigned_to_id=OuterRef('pk')).only('id). Also this link can help you: https://stackoverflow.com/questions/43770118/simple-subquery-with-outerref/43771738 – Saber Solooki Dec 25 '19 at 06:49
  • @SaberSolooki I have updated my questions. After adding only I got SQL error. – Husain Basrawala Dec 25 '19 at 10:07
  • Why you are not trying Conditional aggregation. I don't know if you can use it in your complex query that you mentioned or not. See my answer in this link: https://stackoverflow.com/questions/59472592/annotating-a-filteredrelation-on-a-manytomanyfield-returns-nothing/59472963#59472963 – Saber Solooki Dec 25 '19 at 10:22

2 Answers2

1

Alternative question label might be How to use Count() not perform grouping (GROUP BY) or How to count all in a Subquery

Check this answer for custom Count function to just perform simple count on any queryset without grouping.

Unfortunately, so far haven't found native django option for this.

Oleg Russkin
  • 4,234
  • 1
  • 8
  • 20
0

Though the answer from Oleg was quite close to my requirement but I was still getting an SQL error on the query generated by django. Hence, I ended up implementing using cursor.

Husain Basrawala
  • 1,757
  • 15
  • 21