One Model for multiple tables

Hey all,

I’ve been playing around with Yii for a few weeks now and love it.

I am looking at implementing a project using Yii for work but have hit a bit of an issue that I can’t seem to find a solution to.

I need to create a single model class but allow the table name to be dynamic. All the tables have the same columns and the data is related. I can’t create a separate model for each table since there are currently 400 tables (and more being added every 3 months).

We have a "logs" table which stores all the application logs in there…but this table gets really big, really quickly. Therefore, every 3 months the table is renamed to "logs_<month><year>" and a new one is created. So we have:

logs_012009

logs_042009

logs_072009

logs_102009

logs_032011

the webapp i am writing will be used internally. It will allow us to search these tables. For obvious reasons, I can’t create a model class for each table.

What is the best way around this?

I tried overriding the tableName in the __constructor but later discovered that the call to the tableName() function is done statically and thus won’t work.

This question has been asked many times on these forums without an answer…so i’m hoping somebody can help.

hello maybe this well help Link

Thanks for the link binkabir. I had previously read that thread.

The consensus there was to extend the main model creating more models for each Subject. The issue that I have is that I need to accomodate for an unknown amount of tables and hence must be able to dynamically specify my table name.

For example, I need to be able to do something like this:


$tableName = date("Ym") . "_logs";

$logs = new LogModel($tableName);

$logs->search();

Hello.

fortunately got another solution that is more impressive







$tableName = date("Ym") . "_logs";

$logs =  CActiveRecord::model($tableName)->findbyAttributes(array('month'=>'june','time'=>'blabla'));



now u can use ur active record for the different db tables

Ok. And what if I’m using multiple database ?

I’m using seperate CActiveRecord class from THIS post.

how to resolve that ?

Edit: I’ve just checked all and actually Your method want’s from me a model_file.php what in Your example should be table name…

I have a solution that:

Scenario:

We have some tables same structure. Ex:

tbl_posts_for_blog_1,

tbl_posts_for_blog_2,

tbl_posts_for_blog_3,

tbl_posts_for_blog_77,

tbl_posts_for_blog_78,

Expectation:

One model for above all tables.

My Solution:




class Post extends CActiveRecord {

	protected $table_id = NULL;

	

	public function __construct($scenario, $id){

		$this->table_id = $id;

		parent::__construct($scenario);

	}


	public static function model($className=__CLASS__){

		$className = (is_null($className)) ? __CLASS__ : $className;

		$params = func_get_args(); 

		$new_table_id = (is_array($params) && isset($params[1])) ? $params[1] : NULL;

		$model = new $className(null, $new_table_id);

		$model->_md=new CActiveRecordMetaData($model);

		$model->attachBehaviors($model->behaviors());

		return $model;

	}


	public function tableName() {

		return 'DB_NAME.tbl_posts_for_blog_'.$this->table_id;

	}

}



Usage:




$blog_id = 44; // set to tbl_posts_for_blog_44, 

$mt = Post::model(null, $blog_id)->findAll("status=:status LIMIT 0,10", array(':status'=>'publish'));


$blog_id = 18; // set to tbl_posts_for_blog_18,  

$post_id = 144; // for example

$md = Post::model(null, $blog_id)->findByPk($post_id);



Wish to peace,

Aydogmus

EDITED:

A little modification:

REMOVE $_model (Actually we don’t need it)


protected static $_models=array();

ADD following line to "__construct" function. i missed :frowning:


parent::__construct($scenario);

MODIFY:

  • remove the self::$_models[$className]

  • add a control for function arguments




$new_table_id = (is_array($params) && isset($params[1])) ? $params[1] : NULL;

$model = new $className(null, $new_table_id);



@aydogmus… I like your solution

had a similar problem too.

The thing is this, i’ve implemented your solution but i get the following errors…

-Missing argument 2 for GbAll::__construct(), called in /Applications/MAMP/htdocs/yii/framework/db/ar/CActiveRecord.php on line 371 and defined

used to have the same error on line 1856 till i overide the instantiate function, the model funtion is overriden already in your code…did u make any changes to your CActiveRecord.php? Need your help pls…thanks

Dear obinna;

Line 371 belongs to "Model function" in CActiveRecord,

we override the "model function" and we dont want to execute "parent::model".

And also we always construct fresh classes in our model function so we dont need instantiate.

I had some modification in the code. You can see above.

If you cant solve, could you please pass your code here for detailed analysis?

Wish to peace

Aydogmus

Note: If we inherit the parent::model, it checks if there is already an object built, in that case it returns the existing object. Otherwise it builds new one. This behaviour does not change the table name, as it built the object before.

Our goal is to set the table name before the object creation.

So, our model function first sets the table then creates a new object.

The crucial point is "Not to check if this object already exists"

obinna,

If your calling the function by


$model = new GbAll($scenario)

that will that will call the __construct method (not the static method) where there will be no id given:




public function __construct($scenario, $id){

                $this->table_id = $id; //no id

                parent::__construct($scenario);

        }



You could give it default values such as:




public function __construct($scenario=null, $id=null){

                $this->table_id = $id;

                parent::__construct($scenario);

        }



so that you’ll be able to still call GbAll() and GbAll($scenario).

Hi Obinna,

How did you override the function instantiate() by which we can access the value that we passed as an id as the second parameter? In this case, how would instantiate function get a hold on $blog_id ?

In the CActiveRecord class it is as below:


protected function instantiate($attributes)

	{

        	$class=get_class($this);                

		$model=new $class(null);

		return $model;

	}

Edit:

I figured out this by using the $this->id. But I am now stuck on the error:




Missing argument 2 for Comments::__construct(), called in /Applications/MAMP/htdocs/myproject/yii/framework/db/ar/CActiveRecord.php on line 371 and defined



Were you able to figure out how to resolve that error. Any help please?

Roy

Here is my implementation of CActiveRecord::model() method:

this is the variables, that we need:


private $tableName = 'price_cat';

private static $_models=array();      // class name => model

and model() method:




  public static function model($tableName = false, $className=__CLASS__)

  {

    if($tableName === null) $className=null; // this tring will save internal CActiveRecord functionality

    if(!$tableName)

      return parent::model($className);


    if(isset(self::$_models[$tableName.$className]))

      return self::$_models[$tableName.$className];

    else

    {

      $model=self::$_models[$tableName.$className]=new $className(null);

      $model->tableName = $tableName;

      $model->_md=new CActiveRecordMetaData($model);

      $model->attachBehaviors($model->behaviors());

      return $model;

    }

  }

I don’t think, that this is the best approach, but for me it’s looks better that the other in this thread.