Redesign the food matching logic

[rbroth]

283 revealed that the matching of food consumption to food composition is broken; fallback food composition table datasets are not being used when a match for the highest priority FCT is not found.

After discussion with @bgsandan we believe that it may be time to rewrite the matching logic into a more imperative paradigm, in order to make it easier for developers. This would entail moving a lot of the matching logic out of views/materialized views and into a few functions that get run when new data gets added.

Todo:

• diagram/pseudocode the matching logic
• write functions to implement the matching logic

[rbroth]

@andy-bevan @spenny-liam FYI

[spenny-liam]

@rbroth damn that is a pain. Had thoroughly tested this so would like to find out where it has gone wrong. However yes i agree the stack of views and functions is rather messy so is worthwhile to refactor anyway.

[andy-bevan]

One for the next sprint then I guess - unless we can do it by end of today...

[bgsandan]

@spenny-liam I think the function you created works, the issue is more that the complete workflow was never completed i.e. going from your function that collates food composition for a given food item to the national scale aggregations and data  presentations used by the other views.

@andy-bevan I suspect so!  But if you're feeling really efficient... :wink:

[andy-bevan]

@rbroth is this the issue you are currently working on?

[rbroth]

@rbroth is this the issue you are currently working on?

Yes, related to #283

[rbroth]

For reference, I just ran For reference, I've just run db\objects\food_matching\V0.2.0.804__match_fct_micronutrients.sql in commit 5a427fb618804f937579dfb5aed98e40b53c9aee and recorded the times when the various things started. Biggest Culprit is Populating fct_list_food_compostion table, taking 40 minutes

Creating distinct_fct_list table              12:00:10.002911 
Creating fct_list_food_compostion table       12:00:10.003009 
Populating household_fct_list table           12:00:10.003047 
Populating country_fct_list table             12:05:21.134212 
Populating distinct_fct_list table            12:06:00.031529 
Update references in household_fct_list table 12:06:00.035991 
Update references in country_fct_list table   12:06:00.319334 
Populating fct_list_food_compostion table     12:06:00.320692 
Populating final results table                12:46:05.930466 
End                                           12:47:16.888447