Sort Interventions by row_index

[rbroth]

The intervention API returns rows in a different order after they've been updated.

Add an ORDER BY clause in the relevant view to ensure that intervention costings rows are always returned in a consistent order.

See MSTeam discussion

[rbroth]

@Fuhji Double-checking: this refers to the end point /interventions/{id}/data, correct?

[rbroth]

Does the PATCH endpoint return the data? Is that returned data used by the frontend?

[rbroth]

Ok, I've added ordering to view intervention_startup_scaleup_costs, which I am strongly assuming is the one used to populate the menu in the screenshot.

I was about to say the one of the screenshots was already ordered, but I've just double-checked and it seems that neither of them are. they should be ordered by intervention_data.row_index, but if you compare the "factor_text" (i.e. the row titles), neither of the screenshots are in order.

Todo:

• [ ] see if any other views need to be ordered (I've done the intervention_startup_scaleup_costs, but I'm not sure where the other ones are used)
• [ ] check that the fix I've committed actually orders the API output

[rbroth]

Images for reference

Before update

After updating itnervention

[rbroth]

For Future reference, here's the order that things should be displayed in:

row_index	header1	header2	factor_text
55	Industry-related start-up/scale-up costs	Equipment	Feeder/dosifier and installation (unit cost)
56	Industry-related start-up/scale-up costs	Equipment	Number of mills requiring a feeder/dosifier
57	Industry-related start-up/scale-up costs	Equipment	Total feeder/dosifier cost
58	Industry-related start-up/scale-up costs	Equipment	Mixing conveyor and installation (unit cost)
59	Industry-related start-up/scale-up costs	Equipment	Number of mills requiring a mixing conveyor
60	Industry-related start-up/scale-up costs	Equipment	Total mixing conveyor cost
61	Industry-related start-up/scale-up costs	Equipment	Other equipment and installation (unit cost)
62	Industry-related start-up/scale-up costs	Equipment	Number of mills requiring other equipment
63	Industry-related start-up/scale-up costs	Equipment	Total other equipment cost
64	Industry-related start-up/scale-up costs	Equipment	iCheck portable fluorometer (unit cost)
65	Industry-related start-up/scale-up costs	Equipment	Number of mills requiring a fluorometer
66	Industry-related start-up/scale-up costs	Equipment	iCheck portable photometer
67	Industry-related start-up/scale-up costs	Equipment	Number of mills requiring a photometer
68	Industry-related start-up/scale-up costs	Equipment	Manual centrifuge (unit cost)
69	Industry-related start-up/scale-up costs	Equipment	Number of mills requiring a centrifuge
70	Industry-related start-up/scale-up costs	Equipment	Total iCheck costs
71	Industry-related start-up/scale-up costs	Equipment	Equipment shipping (%)
72	Industry-related start-up/scale-up costs	Equipment	Total shipping cost
73	Industry-related start-up/scale-up costs	Equipment	Import duty on fortification equipment (%)
74	Industry-related start-up/scale-up costs	Equipment	Import duty on icheck devices and reagents (%)
75	Industry-related start-up/scale-up costs	Equipment	Total import duty cost
76	Industry-related start-up/scale-up costs	Equipment	VAT on fortification equipment (%)
77	Industry-related start-up/scale-up costs	Equipment	VAT on icheck devices and reagents (%)
78	Industry-related start-up/scale-up costs	Equipment	Total VAT cost
79	Industry-related start-up/scale-up costs	Equipment	Total equipment cost
81	Industry-related start-up/scale-up costs	Labeling	Redesign of labels and label printing plates (average unit cost per mill)
82	Industry-related start-up/scale-up costs	Labeling	Number of mills/brands requiring redesign of label and printing plates
83	Industry-related start-up/scale-up costs	Labeling	Total labeling cost
85	Industry-related start-up/scale-up costs	Training	Training and sensitization for fortification and QA/QC personnel (unit cost per training session)
86	Industry-related start-up/scale-up costs	Training	Number of training sessions per mill
87	Industry-related start-up/scale-up costs	Training	Number of mills requiring training
88	Industry-related start-up/scale-up costs	Training	Total training cost
89	Industry-related start-up/scale-up costs	Training	Total industry-related start-up/scale-up costs
93	Government-related start-up/scale-up costs	Planning	Baseline survey
94	Government-related start-up/scale-up costs	Planning	Industrial assessment
95	Government-related start-up/scale-up costs	Planning	Formulation of standards/norms
96	Government-related start-up/scale-up costs	Planning	Development or refresh of monitoring and evaluation (M&E) plan
97	Government-related start-up/scale-up costs	Planning	Total planning cost
99	Government-related start-up/scale-up costs	Equipment	iCheck portable fluorometer (unit cost)
100	Government-related start-up/scale-up costs	Equipment	Total number of flurometers
101	Government-related start-up/scale-up costs	Equipment	iCheck portable photometer (unit cost)
102	Government-related start-up/scale-up costs	Equipment	Total number of photometers
103	Government-related start-up/scale-up costs	Equipment	Manual centrifuge (unit cost)
104	Government-related start-up/scale-up costs	Equipment	Total number of manual centrifuges
105	Government-related start-up/scale-up costs	Equipment	Total iCheck costs
106	Government-related start-up/scale-up costs	Equipment	Equipment shipping (%)
107	Government-related start-up/scale-up costs	Equipment	Total shipping cost
108	Government-related start-up/scale-up costs	Equipment	Total equipment cost
110	Government-related start-up/scale-up costs	Social marketing and advocacy	Social marketing/advocacy campaign costs
111	Government-related start-up/scale-up costs	Social marketing and advocacy	Total social marketing and advocacy cost
113	Government-related start-up/scale-up costs	Training
114	Government-related start-up/scale-up costs	Training	Number of training sessions
115	Government-related start-up/scale-up costs	Training	Total training cost
116	Government-related start-up/scale-up costs	Training	Total government-related start-up/scale-up costs

source:

SELECT * 
FROM intervention_data
WHERE intervention_id = 1
ORDER BY row_index

[rbroth]

I suspect that it may be a problem with the API. I've made changes to view intervention_startup_scaleup_costs, and it seems to return things in the proper order.