How to put data from multiple tables into one model?

Question

How to make model, which connects three tables from a database? My DB structure is:

• users
  • id
  • other fields
• users_info
  • id
  • users_id - foreign key to 'users' table
  • other fields
• users_credentials
  • id
  • users_id - foreign key to 'users' table
  • other fields

Former, I wanted to have 2 tables bonded in one model, so I used addRelatedEntity method. Here is my code:

class Model_UserInfo extends Model_Table{
    public $entity_code = 'user_info';
    function init(){
        parent::init();
        $this->addField('phone')->caption('Nr tel');
        $this->addField('users_id');
    }
    function addRelation(){
        $this->addRelatedEntity('i','users','users_id','left outer');
    }

}

and then I extend it in other file -

class Model_User extends Model_UserInfo{
    function init(){
        parent::init();
        parent::addRelation();

        $this->newField('name')->caption('Imie')->mandatory(true)->relEntity('i','name');
    }

}

And that worked great. But what if I want to bond 3 tables into one model? I cannot extend 2 classes. Is there a way to use addRelatedEntity in Model_User, and have references to Model_UserInfo and Model_Credentials?

Answer 1

You would need to probably reverse the joins and start with the "user" table joined by 2 other tables, although it does not matter that much as long as you get the right query.

First, add $this->debug(); into initialization of your model. That will help you a lot when you will be debugging joins, updates etc.

class Model_User extends Model_Table {
    public $entity_code='users';
    function init(){
        parent::init();
        $this->newField('name')
            ->caption('Imie')
            ->mandatory(true);
    }
}

class Model_User_Combined extends Model_User {
    function init(){
        parent::init();

        $this->addRelatedEntity('info','users_info','inner','related');
        $this->addRelatedEntity('cred','users_credentials','inner','related');

        $this->addField('phone')->caption('Nr tel')->relEntity('info');
        $this->addField('password')->caption('Parol')->relEntity('cred');
    }
}

Compared to the solution you have right now, this uses "users" as master table and joins additional tables as "related".

Also you will benefit from inheritance, and Model_User will add some basic user fields for you.