CODEWITHSUNDEEP
pythonmysqldjangosubquery
Manuel
Manuel

Reputation: 33

Subquery Count in Python Django

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

Upvotes: 0

Views: 276

Answers (2)

Manuel
Manuel

Reputation: 33

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')

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13519

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;

Upvotes: 1

Related Questions