combine queryset with array in views ?

[doc1977]

Hi folks, once more it’s me again.

i’ve been looking for a solution half a day know but nothing works as intended. My Problem is:

i have a view that hands over several “items” in its context.
‘total_durations’ is a calculated Array filled with values of the kind “DurationField” with exactly as many values in it like the query named ‘results’.

I need those “corresponding” DurationField values in a ‘results’-forloop but without a nested loop for ‘total_durations’. Usual concatenating methods like iter.chain or union won’t work…i think because ‘total_durations’ is not a real queryset itself.

if 'duration_query' in request.POST:

    count = range(Employees.objects.filter(foreign_missions__isnull=False).distinct().count())
    total_duration = [None] * len(count)
    ids = Employees.objects.values_list('id', flat=True).filter(foreign_missions__isnull=False).distinct()

    k = 0
    for i in count:
        total_duration[k] = Foreign_Missions.objects.total_duration_days(ids[i])
        k += 1


    context = {
        ...
        'results': Employees.objects.filter(foreign_missions__isnull=False).distinct(),
        'total_durations': total_duration,
        ...
    }

[KenWhitesell]

Please post your current total_duration_days function.

[doc1977]

class Duration_Manager(models.Manager):
    def with_duration_days(self):
        return self.annotate(
            duration_days=  ExpressionWrapper(
                (F('end') - F('begin')), output_field=fields.DurationField()
            )
        )

    def total_duration_days(self, employee):
        return self.with_duration_days().filter(employee=employee).aggregate(
            total_duration=Sum('duration_days')
        )['total_duration']

But as i said: I´m happy with the content-values of the context-items. My problem is to use values of total_duration Array in the result-forloop

[KenWhitesell]

The biggest problem with this view is that you’re performing n+3 queries, where n is the number of Employees being retrieved by the query. This is a performance killer.

Since you know that you’re going to have a related value for each Employee, these two lines are totally unnecessary:

@doc1977

    count = range(Employees.objects.filter(foreign_missions__isnull=False).distinct().count())
    total_duration = [None] * len(count)

You already have:

@doc1977

ids = Employees.objects.values_list('id', flat=True).filter(foreign_missions__isnull=False).distinct()

Which means that either

• len(ids)
  or
• ids.count()

would give you the value you are looking for, if it were necessary for you to get the actual numerical value. (But it’s not necessary at all)

You also have the extra (and unnecessary query here:

@doc1977

'results': Employees.objects.filter(foreign_missions__isnull=False).distinct(),

If you were in a situation where this type of process were needed, you’d be much better off doing something like this:

employee_list = Employees.objects.filter(foreign_missions__isnull=False).distinct()

total_duration = [
    Foreign_Missions.objects.total_duration_days(i)
    for i in employee_list.id
]

(This also replaces all the explicit index manipulations.)

However, this still leaves you in an n + 1 query situation - you’re still performing n times as many queries as needed.

The key to making this query more effective is to allow the database engine to do this work for you, rather than you doing it “manually” within the view. This is where Subqueries come into play.

duration_subq = Subquery(
  Foreign_Mission.objects.filter(employee=OuterRef('id')).annotate(
    duration_days=ExpressionWrapper(
      (F('end') - F('begin')), output_field=fields.DurationField()
    )
  ).aggregate(
    total_duration=Sum('duration_days')
  ).values('total_duration')
)

employee_list = Employees.objects.filter(
  foreign_missions__isnull=False
).distinct().annotate(total_duration=duration_subq)

This means then you can reference this total_duration attribute in your employee object in your template.

(Note: I’m kinda winging this query - There may be one or more syntax errors here. I also don’t have your models or any sample data, so this may not be exactly correct. Before you put this into a view, I suggest you play with this query in the Django shell to verify that you get the results you’re looking for.)

[doc1977]

Thx for this detailed answer. Of course you are right concerning redundance of some queries but code-beutification is next on my to-do list anyway. I think things like that happen if you have functionig code and like to add some more features without breaking functionality of old code parts…

I will check your given information carefully and report back

[doc1977]

So after fixing some minor typos of your example-code and importing all necessary modules for execution i keep getting this at the line of the .aggregate-statement:

Environment:

Request Method: POST
Request URL: http://127.0.0.1:8000/employees/show.html

Django Version: 4.1.7
Python Version: 3.11.2
Installed Applications:
[‘django.contrib.admin’,
‘django.contrib.auth’,
‘django.contrib.contenttypes’,
‘django.contrib.sessions’,
‘django.contrib.messages’,
‘django.contrib.staticfiles’,
‘django.utils.timezone’,
‘django.template’,
‘django_bootstrap_icons’,
‘django_bootstrap5’,
‘django_mysql’,
‘fontawesomefree’,
‘drop_down’,
‘accounts’,
‘employees’]
Installed Middleware:
[‘django.middleware.security.SecurityMiddleware’,
‘django.contrib.sessions.middleware.SessionMiddleware’,
‘django.middleware.common.CommonMiddleware’,
‘django.middleware.csrf.CsrfViewMiddleware’,
‘django.contrib.auth.middleware.AuthenticationMiddleware’,
‘django.contrib.messages.middleware.MessageMiddleware’,
‘django.middleware.clickjacking.XFrameOptionsMiddleware’]

Traceback (most recent call last):
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\core\handlers\exception.py”, line 56, in inner
response = get_response(request)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\core\handlers\base.py”, line 197, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\contrib\auth\decorators.py”, line 23, in _wrapped_view
return view_func(request, *args, **kwargs)
File “C:\Users\User\PycharmProjects\klinikXdatabase\employees\views.py”, line 132, in show
).aggregate(total_duration=Sum(‘duration_days’)).values(‘total_duration’)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\query.py”, line 605, in aggregate
return query.get_aggregation(self.db, kwargs)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\sql\query.py”, line 544, in get_aggregation
result = compiler.execute_sql(SINGLE)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\sql\compiler.py”, line 1385, in execute_sql
sql, params = self.as_sql()
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\sql\compiler.py”, line 1919, in as_sql
).as_sql(with_col_aliases=True)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\sql\compiler.py”, line 618, in as_sql
self.compile(self.where) if self.where is not None else (“”, )
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\sql\compiler.py”, line 506, in compile
sql, params = node.as_sql(self, self.connection)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\sql\where.py”, line 112, in as_sql
sql, params = compiler.compile(child)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\sql\compiler.py”, line 506, in compile
sql, params = node.as_sql(self, self.connection)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\fields\related_lookups.py”, line 185, in as_sql
return super().as_sql(compiler, connection)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\lookups.py”, line 357, in as_sql
return super().as_sql(compiler, connection)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\lookups.py”, line 225, in as_sql
rhs_sql, rhs_params = self.process_rhs(compiler, connection)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\lookups.py”, line 118, in process_rhs
sql, params = compiler.compile(value)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\sql\compiler.py”, line 506, in compile
sql, params = node.as_sql(self, self.connection)
File “C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\django\db\models\expressions.py”, line 838, in as_sql
raise ValueError(

Exception Type: ValueError at /employees/show.html
Exception Value: This queryset contains a reference to an outer query and may only be used in a subquery.

[KenWhitesell]

I would need to see what you’re ended up with as the query.

[doc1977]

So I was “playing around” some more. As according to Django-Documentation "aggregate"will not work in a subquery, (https://docs.djangoproject.com/en/4.2/ref/models/expressions/)
i tried to adapt Your suggestion with the Code examples in Django documentation-Section “Subquery” and ended up with this at the moment:

from django.db.models import Q, F, fields, Sum, ExpressionWrapper, Subquery, OuterRef

duration = Foreign_Missions.objects.filter(employee=OuterRef('id')).annotate(
          duration_days=ExpressionWrapper(
              (F('end') - F('begin')), output_field=fields.DurationField()
              )
          ).order_by().values('duration_days')

durations = duration.annotate(total=Sum('duration_days')).values('total')

employee_list = Employees.objects.filter(foreign_missions__isnull=False).distinct().annotate(
                  total_duration=Subquery(durations))

context = {
          ...
         'results': employee_list,
          ...
        }

This now raises an error while rendering the template while trying to perform {{ results.count }}.

" OperationalError at /employees/show.html

(1242, ‘Subquery returns more than 1 row’)

Request Method:	POST
Request URL:	http://127.0.0.1:8000/employees/show.html
Django Version:	4.1.7
Exception Type:	OperationalError
Exception Value:	(1242, ‘Subquery returns more than 1 row’)
Exception Location:	C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages\MySQLdb\cursors.py, line 352, in _get_result
Raised during:	employees.views.show
Python Executable:	C:\Users\User\PycharmProjects\klinikXdatabase\venv\Scripts\python.exe
Python Version:	3.11.2
Python Path:	[‘C:\Users\User\PycharmProjects\klinikXdatabase’, ‘C:\Users\User\PycharmProjects\klinikXdatabase’, 'C:\Program Files\JetBrains\PyCharm ’ ‘2022.3.3\plugins\python\helpers\pycharm_display’, ‘C:\Users\User\AppData\Local\Programs\Python\Python311\python311.zip’, ‘C:\Users\User\AppData\Local\Programs\Python\Python311\DLLs’, ‘C:\Users\User\AppData\Local\Programs\Python\Python311\Lib’, ‘C:\Users\User\AppData\Local\Programs\Python\Python311’, ‘C:\Users\User\PycharmProjects\klinikXdatabase\venv’, ‘C:\Users\User\PycharmProjects\klinikXdatabase\venv\Lib\site-packages’, 'C:\Program Files\JetBrains\PyCharm ’ ‘2022.3.3\plugins\python\helpers\pycharm_matplotlib_backend’]

So my Query-Syntax must still be brocken somewhere…

The good thing your other proposal

    
employee_list = Employees.objects.filter(foreign_missions__isnull=False).distinct()
   
total_duration = [
        Foreign_Missions.objects.total_duration_days(i)
        for i in employee_list
    ]

context = {
          ...
         'results': employee_list,
         'total_durations' = total_duration,
          ...
        }

works great with narrowed code and better performance as You already said…, but leaves me with my problem

[KenWhitesell]

If you run only this part in the Django shell:

@doc1977

duration = Foreign_Missions.objects.filter(employee=OuterRef('id')).annotate(
          duration_days=ExpressionWrapper(
              (F('end') - F('begin')), output_field=fields.DurationField()
              )
          ).order_by().values('duration_days')

by replacing the OuterRef function with any one Employee id with more than one related Foreign_Mission, what is the content of duration?

Then what happens if you run:

@doc1977

durations = duration.annotate(total=Sum('duration_days')).values('total')

What is durations after this? Is it a list with multiple elements, but all having the same value for total?

If it’s what I think it is, you may be able to replace that with:
durations = duration.annotate(total=Sum('duration_days')).values('total')[:1]
to ensure that only one value is returned.

You can find many examples of that being used in Subquery expressions here in the forum. You can do a search on "[:1]" to find them. A couple of recent examples include:

• how to filter the inbox messages that belong to a user?
• How to order_by ForeignKey that matches query?

[doc1977]

Replacing the OuterRef with an employee-id with 9 stored missions gives me this result:

<QuerySet [{‘duration_days’: datetime.timedelta(days=175)}, {‘duration_days’: datetime.timedelta(days=58)}, {‘duration_days’: datetime.timedelta(days=48)}, {‘duration_days’: datetime.timedelta(days=108)}, {‘duration_days’: datetime.timedelta(days=38)}, {‘duration_days’: datetime.timedelta(days=41)}, {‘duration_days’: datetime.timedelta(days=51)}, {‘duration_days’: datetime.timedelta(days=48)}, {‘duration_days’: datetime.timedelta(days=70)}]>

with adding “[:1]” in the durations-line i got the template rendered without error message, AND felt like seing magic

Only thing left is now, that i get presented only ONE mission-period and not the sum of all…

[doc1977]

Hm, i don’t get it done I tried several approaches who alltogether produced error-results.
I’m still stranded with this:

duration_subq = Foreign_Missions.objects.filter(employee=OuterRef('id')).annotate(
    duration_days=ExpressionWrapper(
        (F('end') - F('begin')), output_field=fields.DurationField()
    )
).order_by().values('duration_days')

sum_durations_subq = duration_subq.annotate(total_duration=Sum('duration_days')
            ).values('total_duration')[:1]

employee_list = Employees.objects.filter(foreign_missions__isnull=False).distinct().annotate(
    total_duration=Subquery(sum_durations_subq))

Producing a flawless-rendered template with the result i want besides the fact, that to every employee only the duration of his/her first mission in database is displayed, not the sum of all mission days…

Since I’m new to python and Django and do this only in my Freetime , i may be lacking the necessary programmer skills to complete this task (obviously)…

[KenWhitesell]

I finally had a chance to create a couple of models and play with this in more detail. This should work for you:

duration_subq = Foreign_Missions.objects.filter(
  employee=OuterRef('id')
).order_by('employee').values('employee').annotate(
  duration_days=Sum(
    ExpressionWrapper(
      (F('end') - F('begin')), output_field=fields.DurationField()
    )
  )
).values('duration_days')

employee_list = Employees.objects.filter(
  foreign_missions__isnull=False
).distinct().annotate(
  total_duration=duration_subq
)

This works for me in my test environment to generate the desired results.

Note, I had to guess enough about what your models look like to build this - there’s the possibility that there’s something in your models that would prevent this from working. (I don’t know what that might be, but I’m not going to rule it out.)

[doc1977]

Thank you for all your efforts, will try this as soon as possible and give you feedback!

[doc1977]

Seems all your assumptions about my model where correct, at least concerning this task!
Works perfectly! You made my day!