is that really not possible with the Django ORM?

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 django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
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.