Create and use a temporary mysql table in GridView

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]

Hi alex,

I think it’s enough for you to define an ActiveRecord model for the temporary table and use it in your GridView, isn’t it?

For example, you could define "LicenseTemp" model extended from "License" model, and "LicenseTempSearch" model for filtering and sorting. You could use these models in your "license/index" action instead of "License" and "LicenseSearch" models.

Hi softark,

Thanks a lot for your suggestion. I’m new to Yii and I was expecting that the temporary table would have to be created using Yii2’s special facilities. I didn’t expect it to be this straightforward

I did like you suggested and it works well. I created a static createTemporaryTable() function in the new model and I’m calling it in the controller in index and view action handlers, before creating a model.

The only issue I had was that I had to override the primaryKey() function in the new model, since apparently Yii can’t guess the primary key column in it, so ActionColumn doesn’t work without this.

    public static function primaryKey()

	{

		return ['id'];

	}

Thanks!

Alex