CODEWITHSUNDEEP
sqldjangodjango-queryset
cms_mgr
cms_mgr

Reputation: 2017

Using a complex custom SQL query and returning a queryset in Django 1.7

I have a slow-running Django ListView on a complaints management app I wrote. It's running slow because, when using the ORM, it doesn't hit the database very efficiently.

I've written the following query, which gets the information I need efficiently:

WITH triages AS (
    SELECT
        agreed_resolution_date
        , complaint_id
        , date_completed
        , id
        , stage_id
        , MAX(id) OVER(PARTITION BY complaint_id, stage_id) AS ltst
    FROM complaints_triage
)
SELECT
    cpt.id
    , cplnt.first_name || ' ' || cplnt.last_name AS complainant
    , DATE_PART('year', cs.date_case_received) AS case_year
    , cs.case_reference AS case_reference
    , CASE WHEN cpt.handler_id IS NULL
        THEN NULL
        ELSE usr.first_name || ' ' || usr.last_name
    END AS complaint_handler
    , tm.name AS team
    , stg.stage AS complaint_stage
    , CASE WHEN trg.agreed_resolution_date IS NULL
        THEN ((cpt.date_received + integer '5') - CURRENT_DATE)
        ELSE (trg.agreed_resolution_date - CURRENT_DATE)
    END AS time_left
FROM complaints_complaint AS cpt
INNER JOIN complaints_complainant AS cplnt
    ON cpt.complainant_id = cplnt.id
LEFT JOIN complaints_case AS cs
    ON cpt.case_id = cs.id
LEFT JOIN auth_user AS usr
    ON cpt.handler_id = usr.id
INNER JOIN complaints_stage AS stg
    ON cpt.stage_id = stg.id
INNER JOIN community_team AS tm
    ON cpt.team_id = tm.id
LEFT JOIN triages AS trg
    ON trg.stage_id = cpt.stage_id
    AND trg.complaint_id = cpt.id
    AND trg.id = trg.ltst
WHERE cpt.date_closed IS NULL
ORDER BY time_left

I would like to use this query to override get_queryset in the ListView and use the results as a queryset, with the column headings as the attribute names. But using this query to return results as described in the docs returns a list (using cursor.fetchall) rather than a queryset. Is there any way to return a queryset from this kind of SQL query? Alternatively, is there another established way to do what I'm trying to?

Upvotes: 2

Views: 470

Answers (1)

cms_mgr
cms_mgr

Reputation: 2017

I eventually settled on using a namedtuple, which has the great benefit of behaving like a queryset in the template (though obviously without being able to access related items through dot notation). First off I changed my views superclass to TemplateView to remove any default queryset. I then defined the following extra context

from collections import namedtuple
from django.db import connection

...

class AllLiveComplaintListView(TemplateView):
    template_name = 'complaints/complaint_list.html'

    def get_context_data(self, *args, **kwargs):
        context = super(
            AllLiveComplaintListView,
            self).get_context_data(**kwargs)
        with connection.cursor() as c:
            c.execute('''
            # SQL query from question removed for brevity
            ''')
            ComplaintRecord = namedtuple('ComplaintRecord', 'complaint_id, complainant, case_year, case_reference, handler, team, complaint_stage, days_left')
            context['complaint_list'] = map(ComplaintRecord._make, c.fetchall())
        return context

Upvotes: 1

Related Questions