Django custom SQL returning single row of results when query returns 2?
- by Alvin
I have a custom SQL call that is returning different results to the template than I get when I run the same query against the database directly, 1 row vs 2
Query - copied from Django Debug Toolbar:
SELECT ((Sum(new_recruit_interviews) / Sum(opportunities_offered)) * 100) as avg_recruit, ((Sum(inspections) / Sum(presentations)) * 100) as avg_inspect, ((Sum(contracts_signed) / Sum(roof_approvals)) * 100) as avg_contracts, ((Sum(adjusters) / Sum(contracts_signed)) * 100) as avg_adjusters, ((Sum(roof_approvals) / Sum(adjusters)) *100) as roof_approval_avg, ((Sum(roof_turned_in) / Sum(adjusters)) * 100) as roof_jobs_avg, Sum(roof_turned_in) as roof_jobs_total,
((Sum(siding_approvals) / Sum(adjusters)) *100) as siding_approval_avg, ((Sum(siding_turned_in) / Sum(adjusters)) * 100) as siding_jobs_avg, Sum(siding_turned_in) as siding_jobs_total, ((Sum(gutter_approvals) / Sum(adjusters)) *100) as gutter_approval_avg, ((Sum(gutter_turned_in) / Sum(adjusters)) * 100) as gutter_jobs_avg, Sum(gutter_turned_in) as gutter_jobs_total,
((Sum(window_approvals) / Sum(adjusters)) *100) as window_approval_avg, ((Sum(window_turned_in) / Sum(adjusters)) * 100) as window_jobs_avg, Sum(window_turned_in) as window_jobs_total,
(Sum(roof_turned_in) + Sum(siding_turned_in) + Sum(gutter_turned_in) + Sum(window_turned_in)) as total_jobs,
(((Sum(collections_jobs_new) + Sum(collections_jobs_previous)) / (Sum(roof_turned_in) + Sum(siding_turned_in) + Sum(gutter_turned_in) + Sum(window_turned_in))) * 100) as total_collections,
sales_report_salesmen.location_id as detail_id, business_unit_location.title as title
FROM sales_report_salesmen
Inner Join auth_user ON sales_report_salesmen.user_id = auth_user.id
Inner Join business_unit_location ON sales_report_salesmen.location_id = business_unit_location.id
GROUP BY location_id
Results from direct query running the above query:
INSERT INTO `` (`avg_recruit`, `avg_inspect`, `avg_contracts`, `avg_adjusters`, `roof_approval_avg`, `roof_jobs_avg`, `roof_jobs_total`, `siding_approval_avg`, `siding_jobs_avg`, `siding_jobs_total`, `gutter_approval_avg`, `gutter_jobs_avg`, `gutter_jobs_total`, `window_approval_avg`, `window_jobs_avg`, `window_jobs_total`, `total_jobs`, `total_collections`, `detail_id`, `title`)
VALUES (95.3968, 92.8178, 106.9622, 90.2928, 103.5420, 103.5670, 4152, 100.2494, 106.8845, 4285, 120.1297, 86.2559, 3458, 92.9658, 106.1611, 4256, 16151, 4.281469, 12, 'St Paul, MN');
VALUES (90.2982, 73.3723, 97.8474, 104.5433, 97.7585, 86.1848, 1884, 109.9268, 109.3321, 2390, 81.0156, 96.4318, 2108, 91.7200, 123.8792, 2708, 9090, 4.531573, 13, 'Denver, CO');
Results from template:
{'roof_jobs_total': Decimal('4152'), 'gutter_jobs_total': Decimal('3458'), 'avg_adjusters': Decimal('90.2928'), 'title': u'St Paul, MN', 'window_approval_avg': Decimal('92.9658'), 'total_collections': Decimal('4.281469'), 'gutter_approval_avg': Decimal('120.1297'), 'avg_recruit': Decimal('95.3968'), 'siding_approval_avg': Decimal('100.2494'), 'window_jobs_total': Decimal('4256'), 'detail_id': 12L, 'siding_jobs_avg': Decimal('106.8845'), 'avg_inspect': Decimal('92.8178'), 'roof_approval_avg': Decimal('103.5420'), 'roof_jobs_avg': Decimal('103.5670'), 'total_jobs': Decimal('16151'), 'window_jobs_avg': Decimal('106.1611'), 'avg_contracts': Decimal('106.9622'), 'gutter_jobs_avg': Decimal('86.2559'), 'siding_jobs_total': Decimal('4285')}
Tried tweaking it a few ways and running the results through various for loops, keep getting the same result where my results are a single row through the Django template and the expected results (through console) have 2 rows
The row that is coming back is the same as the first row returned through the console query so I'm thinking that it is running correctly just a matter of passing the results through...
for good measure this is the code I'm using to generate the query
(yes it's a bit ugly, been playing with it)
def sql_grouped(table, fields, group_by=False, where=False):
from django.db import connection
query = 'SELECT %s FROM %s' % (fields, table)
if where:
query = query + ' WHERE %s' % (where)
if group_by:
query = query + ' GROUP BY %s' % (group_by)
cursor = connection.cursor()
cursor.execute(query)
desc = cursor.description
data = [dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall()]
return data[0]
any feedback is greatly appreciated - been tinkering with this since I realized I could skip a few steps by generating my averages directly within the SQL rather than post-process
