AllanJard
commented
1 month ago Thanks for rasing this. What I've thought of doing before to address exactly this issue is to simply assign a sequential alias to each - no need to zip/encode/hash I reckon.
It was set up the way it was originally as there was no ability at the time to support more complex statements like this. It was simply a column name which has its own limit in MySQL, thus it wasn't a problem.
It is on my todo list, and now with your vote for it, it becomes a higher priority, but I'm still not sure when exactly I'll get to it I'm afraid.
The workaround is to use a VIEW.
14nd90
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.