Subquery Count in Python Django

Question

I have a problem with subquery count in Python Django...

Query MySQL:

SELECT usuario.name, COUNT(*) AS Groups,(
    SELECT COUNT(*)
    FROM group
    WHERE group.user_id=user.id and state = 'on going') AS Started
FROM user
INNER JOIN group
ON (user.id=group.user_id)
WHERE user.rol = 'Provider'
GROUP BY group.user_id
ORDER BY Groups desc;

Views:

connected_query= GroupModel.objects.values(
'usuario__name').filter(
user__rol='PROVIDER'.lower(), status='ON_GOING'.lower()).annotate(
started=Count('user'))


totals_query = GroupModel.objects.values(
provider=Concat('user__name', Value(' '), 'user__surname')).annotate(
totals=Count('user'),connected=Subquery(connected_query.values('started')[:1])).order_by(
'-connected')


First query return:
<QuerySet [{'user__name': 'James', 'started': 3}, {'user__name': 'John', 'started': 2}, {'user__name': 'Frank', 'started': 2}, {'user__name': 'Sara', 'started': 1}]>

While second query return:
<QuerySet [{'user__name': 'James', 'totals': 10, 'started': 3}, {'user__name': 'John', 'totals': 10, 'started': 3}, {'user__name': 'Frank', 'totals': 12, 'started': 3}, {'user__name': 'Sara', 'totals': 9, 'started': 3}]>

I need the query to return the following:

<QuerySet [{'user__name': 'James', 'totals': 10, 'started': 3}, {'user__name': 'John', 'totals': 10, 'started': 2}, {'user__name': 'Frank', 'totals': 12, 'started': 2}, {'user__name': 'Sara', 'totals': 9, 'started': 1}]>

The problem is that the second query, always return 3, which are the connected that the user 'James', if in subquery I remove [:1] python gives me the following error: django.db.utils.OperationalError: (1241, 'Operand should contain 1 column(s)')

I don't know where to continue, I'm a bit desperate, any help is good

group is a reserved word..

P.Salmon
– P.Salmon

2020-07-28 11:37:46 +00:00
Commented Jul 28, 2020 at 11:37 — P.Salmon
– P.Salmon, Commented Jul 28, 2020 at 11:37
I know, it really is a grupo (in Spanish)

Manuel
– Manuel

2020-07-28 12:23:29 +00:00
Commented Jul 28, 2020 at 12:23 — Manuel
– Manuel, Commented Jul 28, 2020 at 12:23

Ankit Bajpai · Accepted Answer · 2020-07-28 10:44:44Z

1

I think you may update your query to -

SELECT usuario.name, COUNT(*) AS Groups, COUNT(state = 'on going') AS Started
FROM user
INNER JOIN group
ON (user.id=group.user_id)
WHERE user.rol = 'Provider'
GROUP BY group.user_id
ORDER BY Groups desc;

answered Jul 28, 2020 at 10:44

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

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

4 Comments

Manuel Over a year ago

With that query I get this:

<QuerySet [{'user__name': 'James', 'ge_totals': 10, 'started': 10}, {'user__name': 'John', 'ge_totals': 10, 'started': 10}, {'user__name': 'Frank', 'ge_totals': 12, 'started': 12}, {'user__name': 'Sara', 'ge_totals': 9, 'started': 9}]>

Ankit Bajpai Over a year ago

Does everyone have state as 'on going'?

Manuel Over a year ago

Yes, all users, have at least 1 state as 'on going'

Manuel Over a year ago

Thanks for your query, it gave me a hint, I did not know that you could give parameters to count, my ny query is:

SELECT us.name, COUNT(gr.Id) , COUNT(case when status='on going' then 1 END) FROM user us INNER JOIN group gr ON (us.id = gr.User_id)  WHERE us.Rol = "Provider" GROUP BY gr.user_id

Manuel · Accepted Answer · 2020-07-29 07:42:50Z

0

It's already solved, much easier than using a subquery, I have used Case, the new MySQL query:

SELECT us.name, COUNT(gr.Id) , COUNT(case when status='on going' then 1 END)
FROM user us
INNER JOIN group gr
ON (us.id = gr.User_id) 
WHERE us.Rol = "Provider"
GROUP BY gr.user_id

Views:

from django.db.models import Count, Value, Case, When
from django.db.models.functions import Concat

totals_query = GrupoModel.objects.values(
provider=Concat('usuario__nombre', Value(' '), 'usuario__apellidos')).annotate(
totals=Count('user'),connected=Count(Case(When(status='ON_GOING'.lower(),then=1)))).order_by(
'-connected')

answered Jul 29, 2020 at 7:42

Manuel

331 gold badge1 silver badge6 bronze badges

Collectives™ on Stack Overflow

Subquery Count in Python Django

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related