Open krissy opened 7 years ago
got questions!
had a start at replicating the example csv and found the following hurdles:
1) where is the "Region" keyword - can't seem to find it in the db tables! 2) similar for "Emotional State".. was this what mood rating used to be? 3) Do all the columns need to appear in the same csv? if so - 4) did you want nested group by statements or 5) would it make sense to aggregate the counts with non db code or a plsql stored procedure (a la http://www.postgresqltutorial.com/plpgsql-function-returns-a-table/ ) - and then to create a materialized view based on a cache table?
Blocked by: #146 Blocks: #147
What
Create a materialized view in Postgres and corresponding Rails model (see helpful tutorial here!) to hold data we will share with the Humanitarian Data Exchange (HDX).
This is the first step to setting up a public API endpoint that exposes a comma-separated table of statistics from our collected drawings that help visualise the "Number of displaced children and their conveyed moods in art therapy". (#147)
Note: Refer to #146 for confirmed data design and refer to the data design spreadsheet.
How
Reminder: this tutorial will come in handy if all this is new to you.
drawing
andorganisation
models, returning rows of totals corresponding to the sample spreadsheet. E.g. one column will be the sum of drawings in an Organisation's country that had a negative mood rating, another will be the same but for children who identify as male, etc.Example view output
NB: This is a quickly drawn-up example to roughly help visualise the column and values in this view, based on the state of the spreadsheet at the time of writing (very prone to change), design/naming details are up to you!
View name: report_drawing_mood_stats