Suggestions to bypass max alias length limit #38
Description
My scenario is I have a dataset which I am using the Editor to nicely handle the server side processing, allowing me to easily join tables and utilise colvis and search builder to filter the data easily. It works a charm - thanks!
However the dataset has lots of granular data that I want to totalise whilst applying weight. This results in a field register which looks like the below
$editor->field(Field::inst('((spc.fulltime_female_year_group_1 + spc.fulltime_male_year_group_1) / 100 * 22.063) + ((spc.fulltime_female_year_group_2 + spc.fulltime_male_year_group_2) / 100 * 21.618) + ((spc.fulltime_female_year_group_3 + spc.fulltime_male_year_group_3) / 100 * 21.419) + ((spc.fulltime_female_year_group_4 + spc.fulltime_male_year_group_4) / 100 * 21.274) + ((spc.fulltime_female_year_group_5 + spc.fulltime_male_year_group_5) / 100 * 20.589) + ((spc.fulltime_female_year_group_6 + spc.fulltime_male_year_group_6) / 100 * 18.914) + ((spc.fulltime_female_year_group_7 + spc.fulltime_male_year_group_7) / 100 * 14.576) + ((spc.fulltime_female_year_group_8 + spc.fulltime_male_year_group_8) / 100 * 14.705) + ((spc.fulltime_female_year_group_9 + spc.fulltime_male_year_group_9) / 100 * 14.653) + ((spc.fulltime_female_year_group_10 + spc.fulltime_male_year_group_10) / 100 * 14.392) + ((spc.fulltime_female_year_group_11 + spc.fulltime_male_year_group_11) / 100 * 14.357) + ((spc.fulltime_female_year_group_12 + spc.fulltime_male_year_group_12) / 100 * 17.662) as weighted_year_groups');
This causes issues because of the way Editor sets up the query, specifically the way that my designated alias gets replaced with the whole statement. MySQL has an alias character limit of 256, which the statement itself obviously hugely exceeds.
Short of processing these totals while I am importing the dataset, is there any other way to deal with this use case?
I had thought one easy fix could be to look at gzipping/base64 encoding the dbField when used an alias and then unzipping/decoding when processing the result, but this would still maintain a ceiling (in my use case I would be straddling the line at ~250 chars).
Another thought is that there's actually no reason to force an alias at all - as far as I can tell not providing an alias would work.
I am sure there is a reason the library was setup to build queries in this way, but it does introduce a limitation that wouldn't exist if using the user's defined aliases. I think the idea of pre-processing is going to be my way out - but I wanted to highlight this as a small issue others may run into.