Replace django results/blanket manipulate data

Question

I need to be able to globally replace certain data in django results and perform operations on it.

I already have a custom manager doing exclusions based on another database eg CRMData.filtered_obj which works fine however I have instances requiring certain things to be grouped, I then need to perform totals/counts of the new grouping for example I get a result using annotate(Count(name)) eg:

Pizza 20
Beer 5
Tea 3
Nerds 2

And for example I need to combine Pizza and Beer so the list becomes:

Beer & Pizza 25
Tea 3
Nerds 2
Banana 1

And the list must now be populated with the next highest number.

I've attempted doing something like:

CRMData.objects.extra(select={'name1': 'case name where "Beer" OR "Pizza" then "Beer & Pizza" else name'})

Which outputs a result with the extra column but I cannot perform

CRMData.objects.extra(select={'name1': 'case name where "Beer" OR "Pizza" then "Beer & Pizza" else name'}).values('name1').annotate(Count('name1'))

As I get a:

Cannot resolve keyword 'name1' into field

I've tried a few different things like adding .group_by() to the end but I still get the same error.

Ideally I want to be able to transact using the queryset methods as there's a lot of pre-existing code built around it, the goal is to add this as a custom manager method that preceeds other methods but I'm not sure if this is possible.

I've toyed with the idea of placing the results into a panda dataframe and using that but if anyone has any suggestions if this isn't possible I'd be grateful although it will mean a lot of rewriting of code.

Answer 1

After some research and advice from the django users mailing list I've been advised that django's ORM doesn't support such a function.

A solution I came up with hypothetically would be be to use django to generate the query and replace the column name and run the query raw eg.

a = CRMData.objects.filter(log_type='Request').query

SELECT `"TABLE_NAME"`.`"Log_Number "`, `"TABLE_NAME"`.`"First_Name"`, 
`"TABLE_NAME"`.`"Incident_Date"`, `"TABLE_NAME"`.`"Item_Description"`, 
`"TABLE_NAME"`.`"Problem_Code"`, `"TABLE_NAME"`.`"Call_type"`, 
`"TABLE_NAME"`.`"Log_Severity"`, `"TABLE_NAME"`.`"Log_Status"`, 
`"TABLE_NAME"`.`"Owner"`, `"TABLE_NAME"`.`"Group"`, `"TABLE_NAME"`.`"Log_Summary"`, 
`"TABLE_NAME"`.`"Created_By"`, `"TABLE_NAME"`.`"USER_NAME"`, 
`"TABLE_NAME"`.`"LAST_UPDATE_DATE"`, `"TABLE_NAME"`.`"LAST_UPDATED_BY"`, 
`"TABLE_NAME"`.`"LAST_UPDATED_BY_USERNAME"` FROM `"TABLE_NAME"` WHERE 
`"TABLE_NAME"`.`"Call_type"` = Request 

This provides the raw query so now we can replace the relevant column with a case eg.

new_query = a.replace('`"TABLE_NAME"`.`"Problem_Code"`', 
'CASE `"TABLE_NAME"`.`"Problem_Code"` 
WHEN "value" then "new value" 
ELSE `"TABLE_NAME"`.`"Problem_Code"` 
END')

You can then run

instance = CRM.objects.raw(new_query)

To return the results.

It's also possible to process this in Python which is viable but would take a bit more work on Python's part. There's probably a more efficient way of doing this but that's all I have at this stage.