Hi,
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?
Thanks
[sql]
CREATE TEMPORARY TABLE extended_license AS
SELECT
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
FROM license;[/sql]