jeudi 8 janvier 2015

Django ORM to sum two columns to fill a third one

Lets say, I have a SQLite table like this:



A | B | SUM
-------------
AA | BA |
AB | BB |
AC | BC |


and now I'd like to set numeric values to them, to fill in a third column. For example:



AA = 1 BA = 1
AB = 3 BB = 3
AC = 2 BC = 2


So the table in database would be like this:



A | B | SUM
-------------
AA | BA | 2
AB | BB | 6
AC | BC | 4


I'm doing this because, I added a django-tables2 table to my project and I'd like to custom sort my table, but as far as I searched, there is no way to do this. I can only sort table by column A or B. But it orders them in alphabetical order and my values are different.


The other possibility would be to use SQL query to sort my table like this:



ORDER BY CASE WHEN A = 'AA' THEN 1
WHEN A = 'AB' THEN 3
WHEN A = 'AC' THEN 2 END


But I'd have to use raw method in django to do this, right? Inserting it with ORM is not possible? At least that's what I've understood this far. Or I just dont know how to do this. :P


So instead of using raw, I thought it would be simple to add a third column just to order the whole table by that ;)


Well, it turned out not to be as simple as I hoped and I would like to get any help if possible. Thank you!


Aucun commentaire:

Enregistrer un commentaire