I need to create and use a temporary MySQL table in Yii2’s GridView.
Current situation: My GridView shows some columns from table license with one additional column that is computed at runtime. I can do this easily (using property transformation in the model), however, I need sorting / filtering as well. I’ve resolved this in the past (in my own framework) using views or temporary tables.
I have ported the column value calculation to SQL. The reason I want to use a temporary table and not a view is that the column value calculation depends on runtime configuration of the application. Below is a temporary table I want to use.
Basically, I need to auto-create a temporary table on license index page access and use it in a model (with sorting and filtering).
I have not found any documentation or examples on how to do this in Yii2. Are there any out there? Maybe someone here has done a similar thing and can post some sample code?
CREATE TEMPORARY TABLE extended_license AS
license.*, (CASE -- license expired WHEN (license.valid_until_date < UTC_DATE()) THEN 'License Expired' -- support valid until year_month(begin_date) + 1year <= year_month(today). WHEN (license.branding_name IN ('this_is_runtime_generated_list')) THEN CASE ( Year(license_begin_date) + 1 < Year(UTC_DATE()) OR (Year(license_begin_date) + 1 = Year(UTC_DATE()) AND Month(license_begin_date) <= Month(UTC_DATE)) ) WHEN 1 THEN 'Support Expired' ELSE 'Valid' END -- support valid if major version is current year WHEN (license.branding_name IN ('this_is_runtime_generated_list')) THEN CASE (license.program_major_ver != YEAR(UTC_DATE())) WHEN 1 THEN 'Support Expired' ELSE 'Valid' END -- support valid ELSE 'Valid' END) AS status