How to use model with multiple identical tables, for data isolation?

Question

I am writing a simple SaaS application for small construction companies to use and am trying to achieve mid-level data isolation by having each company have their own unique data tables that aren't shared.

This is also nice in case the WHERE {group_id} clause is missing, other group data won't be exposed.

I am able to use the command builder to create these tables dynamically, prefixing them with the group number like grp_37645_projects.

But I am stuck on how to use my model classes as the table names change.

After login, I want to set the table names. These won't change as users aren't allowed to be a part of more than one group.

I have read about changing the tableName, but that is a STATIC function, and I have read a little about creating classes on the fly, but neither option was detailed or complete.

I also know this touches on the single table inheritance, but once again, every example use a little different scenario.

Do you have a recommended solution for setting the tableNames dynamically?

Answer 1

Add some logic for tableName:

namespace app\models;

use yii\db\ActiveRecord;

class Project extends ActiveRecord
{
    /**
     * @return string the name of the table associated with this ActiveRecord class.
     */
    public static function tableName()
    {
        //some logic for getting current "group_id" for current user
        $current_group_id = \Yii::$app->user->identity->group_id;
        return 'grp_'.$current_group_id.'_projects';
    }
}