We have several Django projects that include inline SQL for complex queries. Sometimes, this is the only option. But there are also downsides to mixing SQL with the Django ORM, not least of all that it's brittle to changes in the models, making things like using OCD models in Councilmatic and adding fields to the BGA import more difficult and far-reaching changes than they could have been.
The Django ORM supplies functions for complicated querying, including aggregation and window functions. See https://github.com/datamade/bga-payroll/issues/362. I've made some headway refactoring the raw SQL in Payroll to ORM operations, so it's easier to calculate additional summary statistics. Let's use lessons learned from that and other refactors (e.g., Councilmatic) to take a principled stance on when to write raw SQL in Django code, then document it with our Django practices.
Documentation request
We have several Django projects that include inline SQL for complex queries. Sometimes, this is the only option. But there are also downsides to mixing SQL with the Django ORM, not least of all that it's brittle to changes in the models, making things like using OCD models in Councilmatic and adding fields to the BGA import more difficult and far-reaching changes than they could have been.
The Django ORM supplies functions for complicated querying, including aggregation and window functions. See https://github.com/datamade/bga-payroll/issues/362. I've made some headway refactoring the raw SQL in Payroll to ORM operations, so it's easier to calculate additional summary statistics. Let's use lessons learned from that and other refactors (e.g., Councilmatic) to take a principled stance on when to write raw SQL in Django code, then document it with our Django practices.