How to get a list of tuples from a Django RawQuerySet?

user_162358 0
Viewed 2367

I'm doing a complex query with raw SQL within django to solve some annotation issues. The actual query has many left joins that have been converted to subqueries in order to get around a major bug in Django. https://code.djangoproject.com/ticket/10060

Given

fields = ['shift__adminFee',                                      
        'shift__rosterSlot__adminFee',
        'shift__taxi__rosterGroup__adminFee',
        'shift__driver__adminFee']

query = `''select table.id, "table_shift"."adminFee"
, "table_rosterslot"."adminFee"
, "table_rostergroup"."adminFee"
, "table_driver"."adminFee"  from table 
left join ( select table_id, sum(amount) amount_sum from related_table group by table_id ) related_table
on table.id = related_table.table_id
...
( more inner joins and tables to support the above fields )
'''
rawQuerySet = Table.objects.raw(q)

which returns a RawQuerySet.

The RawQuerySet works well ... and it populates the related models as well as giving the correct annotated results.

The RawQuerySet however doesn't support returning a list of tuples.

I've looked through the source file which locally in the project is 'env/lib/python2.7/site-packages/django/db/models/query.py' but I don't understand it yet and I had a result to produce.

So instead of doing results_as_list_of_tuples = query.values_list(*fields) I did something like

    results_as_list_of_tuples = []
    for result in query:
        shift = result.shift
        eventSchedule = shift.eventSchedule
        rosterSlot = shift.rosterSlot
        taxi = shift.taxi
        rosterGroup = taxi.rosterGroup

        data = []
        ...
        # the following is one line. I broke it up because it didn't format correctly.
        data.extend([
            shift.adminFee
            ,rosterSlot.adminFee
            ,rosterGroup.adminFee
            ,driver.adminFee]
        )
        ...
        results_as_list_of_tuples.append(tuple(data))

How to get a list of tuples from a Django RawQuerySet with something like results_as_list_of_tuples = values_list(raw_query_set,*fields)

django-queryset django python
1 Answers

You can get a list of column names from RawQuerySet by columns property. A values_list iterator can be created from a raw queryset this way:

def raw_queryset_as_values_list(raw_qs):
    columns = raw_qs.columns
    for row in raw_qs:
        yield tuple(getattr(row, col) for col in columns)

The property columns is undocumented, but steady.

user_4484740
Related