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

Question

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?

But what would this queryset contain? A queryset is a container for model instances, but your query clearly does not map to any particular model class. — Daniel Roseman
– Daniel Roseman, Commented Nov 6, 2014 at 13:39
@DanielRoseman the idea I was imagining was for it to map to a temporary abstract model, that would be held in memory in order to return the view - analogous to a database view. — cms_mgr
– cms_mgr, Commented Nov 6, 2014 at 13:43
@DanielRoseman if that's not possible (which I'm assuming it isn't from your comment :-)), is just using a TemplateView and adding the list of tuples from the query as context the best way to go about this? — cms_mgr
– cms_mgr, Commented Nov 6, 2014 at 13:52
You could create an Class that would accept a tuple and map the tuple into the properties. Otherwise the second option you listed is the way you'd have to go. Personally, I think the first option is cleaner and a bit more intuitive. Especially if you have to change the order of the fields in the tuple or make an actual model to replace it in the future. — schillingt
– schillingt, Commented Nov 6, 2014 at 14:32

cms_mgr · Accepted Answer · 2014-11-10 12:57:36Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related