In my 10 years playing with Django i found a few bugs, but i never had to
fall back to a raw SQL query. Fiddling with Django for almost a full day
now im out of ideas except of asking for help here:
I have an organizer app with days containing shifts and people working
those shifts (M2M). There also are supervisors for each day that are
directly stored on the day (M2M). For the user stats i want to see who has
how many shifts. For speed reasons i WANT to handle this with only one
query. Here is the query i started with:
assigned_to = self.shifts.filter(day_id=OuterRef('id')).order_by()
days = Day.data.filter(date__range=(start, end)).distinct().annotate(
assigned_shifts=Count(
Subquery(assigned_to.values('id'))
)).annotate(
assigned_pl=Max(Case(
When(production_managers=self, then=1),
default=0, output_field=models.IntegerField(),
distinct=True
))).annotate(
assigned_tl=Max(Case(
When(day_managers=self, then=1),
default=0, output_field=models.IntegerField(), distinct=True
))).annotate(
assigned_al=Max(Case(
When(managers=self, then=1),
default=0, output_field=models.IntegerField(), distinct=True
))).annotate(
assigned_cash=Max(Case(
When(cash_managers=self, then=1),
default=0, output_field=models.IntegerField(), distinct=True
))).order_by()
This is giving me an SQL error "more than one row returned by a subquery
used as an expression", without the subquery i was getting false values -
probably because the grouping didnt work out as its supposed to. Turns out
there is a warning in the docs and a very old ticket
(https://code.djangoproject.com/ticket/10060) warning about this exact
scenario, but somehow i dont get the subquery to work like it should.
The final and a lil simplified SQL looks like this:
days = Day.data.raw("""
SELECT distinct
"events_day"."id","events_day"."date",
(select count(*)
from events_shift
left outer join events_shift_employees
on events_shift_employees.shift_id = events_shift.id
where events_shift_employees.user_id = {user_id} and
events_shift.day_id = events_day.id and
events_shift.deleted is null) AS "assigned_shifts",
Max(CASE WHEN"events_day_production_managers"."user_id"={user_id} THEN 1
ELSE 0 END)AS"assigned_pl",
Max(CASE WHEN"events_day_day_managers"."user_id"={user_id} THEN 1 ELSE 0
END)AS"assigned_tl",
Max(CASE WHEN"events_day_managers"."user_id"={user_id} THEN 1 ELSE 0
END)AS"assigned_al",
max(CASE WHEN"events_day_cash_managers"."user_id"={user_id} THEN 1 ELSE 0
END)AS"assigned_cash"
FROM"events_day"
LEFT JOIN"events_shift"ON("events_day"."id"="events_shift"."day_id")
LEFT
JOIN"events_shift_employees"ON("events_shift"."id"="events_shift_employees"."shift_id")
LEFT
JOIN"events_day_production_managers"ON("events_day"."id"="events_day_production_managers"."day_id")
LEFT
JOIN"events_day_day_managers"ON("events_day"."id"="events_day_day_managers"."day_id")
LEFT
JOIN"events_day_managers"ON("events_day"."id"="events_day_managers"."day_id")
LEFT
JOIN"events_day_cash_managers"ON("events_day"."id"="events_day_cash_managers"."day_id")
WHERE("events_day"."deleted"IS NULL AND"events_day"."date" BETWEEN
'{start}' AND '{end}')
GROUP BY "events_day"."id"
ORDER BY"events_day"."date"DESC
""".format(**{
'user_id': self.id,
'start': start,
'end': end
}))
I could need a lil hand here in achiving the SELECT COUNT(*) in line 4
rather then the Count(Subquery()) Django allows me to add to the query, but
produces an SQL error.
Im Working on Python 3.6.5, Django 2.0.3 and Postgres 10
This problem is hardcore SQL ORM stuff so THX for anyone looking into this.
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/816185ac-58eb-4f71-986c-04ac34e22e02%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
