Trouble with Table Named user

Yii 2.0 General Discussions
1

I seem to be experiencing some issues with the user table, let me explain.

When on MySQL, the code works fine.
I had to migrate to Azure SQL and the same code fails because of the user table.

So in MySQL the following join works

->leftJoin('auth_assignment', 'user.id = auth_assignment.user_id')

but on Azure it fails and needs to be modified to

->leftJoin('auth_assignment', '[user].id = auth_assignment.user_id')

As you can see, I have to surround the table name with bracket.

Is there a way to code it so it works in either without needing to manually edit every instance or duplicate large amounts of code?

I tried adding an If, but that fails

ArrayHelper::map(User::find()
        ->select(['user.id', 'user.FirstName', 'user.LastName'])
        if(Yii::$app->db->getDriverName() === 'mysql'){
            ->leftJoin('auth_assignment', 'user.id = auth_assignment.user_id')
        }else{
            ->leftJoin('auth_assignment', '[user].id = auth_assignment.user_id')
        }
        ->where(['user.status' => 10])
        ->andWhere(['auth_assignment.item_name' => 'Finance'])
        ->orderBy([
            'FirstName'=>SORT_ASC,
            'LastName'=>SORT_ASC
        ])
        ->asArray()
        ->all(),
        'id', 
        function($model) {
            return $model['FirstName'].' '.$model['LastName'];
        }
    ),

Any guidance as to making the code backend flexible is greatly appreciated.

2

Some DB engines require some words to be explicitly stated as column names in order to work like with user here (I guess this is the same for PostgreSQL). If you are writing SQL statement directly and you know it will be run on different engines you should use special syntax. I guess some manual editing is required here but there should be no need for ifs.

2 Likes
3

Worked like a charm. Thank you.