Refactor indexed dimensions
Created by: pavril
Currently, there can only be a finite number of indexed dimensions, and those must be set by the activity manager. Once a dimension is indexed, the values collected after a VPL evaluation are duplicated in an auxiliary table (vplcc_columns). This table is then JOINed in the query which draws the values to create the leaderboard. This approach allows us to apply custom order rules and thus allow for custom rankings. This however has many downsides: fixed number of indexed dims, must duplicate values, costly DB operations when removing or creating an index for activities with existing leaderboard entries, ....
I just realised that indexed dimensions can be created on the fly when querying the database (naturally, I came up with it after I completing all of the above
Values collected from VPL evaluations are stored in the evaluation_facts
table together with their associated dimension and submission ids. We could therefore easily filter the table to obtain a column containing all the collected values for a dimension. If we do a join of this table to the query pulling the leaderboard data, we immediately have an index for each custom dimension, that we can then order and use for computed dimensions 🤯.
To implement this, we can modify the leaderboard_query class to produce the following query:
select ..., cast(D1.data as ...) as {dim1 slug}, ...
from vplcc_evaluations
inner join vpl_submissions on ...
left outer join vplcc_evaluation_facts as D1 on vplcc_evaluations.id = D1.vplccsubmissionid
where ..., D1.keyid = {dim1 id}, ...
or
select ..., {dim1 slug}.data as {dim1 slug}, ...
from vplcc_evaluations
inner join vpl_submissions on ...
left outer join (
select vplccsubmissionid, cast(data as ...)
from vplcc_evaluations_facts
where keyid = {dim1 id}
) as {dim1 slug} on vplcc_evaluations.id = {dim1 slug}.vplccsubmissionid
where ..., D1.key = {dim1 id}, ...
A downside of this query may be that we walk through the vplcc_evaluations_facts tables multiple times.