Joining tables in different databases with the CI

Question

I did search for a solution, but found nothing helpfull. This question was about using multiple databases with datamapper but I already use that method.

user model:

class User extends DataMapper {
    var $prefix = "app_";

    var $db_params = 'default';
...

company model:

class Company extends DataMapper {
    var $prefix = "tbl_";

    var $db_params = 'super';
...

I have the above models. They are related as user-has-one-company and company-has-many-user. When I create a company, a user will be created automatically and the relationship is made with succes.

However, when I try to delete a company, or try to acces the related company of a user. DataMapper looks for the user table in the wrong place.

I got the following error:

A Database Error Occurred

Error Number: 1146

Table 'a1210alf.app_users' doesn't exist

SELECT `tbl_companies`.* FROM (`tbl_companies`) LEFT OUTER JOIN `app_users` app_users ON `tbl_companies`.`id` = `app_users`.`company_id` WHERE `app_users`.`id` = 4

In my CI config I have two different settings for two databases 'default' and 'super'. And I set the correct one in the model. The table app_users does exist, all fields needed are there. Not sure what I am doing wrong.

Answer 1

I'm using this solution for a while now. It still didn't 'broke' in any case .. so I guess it's the correct way to do it.

First I created a 'BaseModel' which extends 'DataMapper'. The constructor looks like this:

/**
 * Constructor: calls parent constructor
 */
function __construct($id = NULL, $db = NULL)
{
    if ($db != NULL && $this->db_params == '') {    
        // use these params as default
        $this->db_params = array_merge(array( 
            'dbdriver'  => 'mysql',
            'pconnect'  => true,
            'db_debug'  => true,
            'cache_on'  => false,
            'char_set'  => 'utf8',
            'cachedir'  => '',
            'dbcollat'  => 'utf8_general_ci',
            'autoinit'  => true,
            'stricton'  => false,
        ), $db);
    }
    elseif($this->db_params == '') {
        $CI =& get_instance();
        $cid = $CI->session->userdata('cid');
        if($cid != false || $cid != '') {
            $userDB = new Database();
            $userDB->get_where(array('company_id'=>$cid));
            if($userDB->exists()) {
                $this->db_params = array(
                    'hostname'      => $userDB->dbhost,
                    'database'      => $userDB->dbname,
                    'username'      => $userDB->dbuser,
                    'password'      => $userDB->dbpass,
                    'dbdriver'  => 'mysql',
                    'pconnect'  => true,
                    'db_debug'  => true,
                    'cache_on'  => false,
                    'char_set'  => 'utf8',
                    'cachedir'  => '',
                    'dbcollat'  => 'utf8_general_ci',
                    'autoinit'  => true,
                    'stricton'  => false,
                );
            }
        }
    }
    parent::__construct($id);
}

Now if you create an instance of a user we should do $u = new User(NULL, $this->udb); where $this->udb are the connection params which are loaded in the main controller.

This way the model will get the correct params each time. AND if a related object is needed the BaseModel knows where to get the correct params.

All I care about is that it works :p

NOTE: there is no need to set var $db_params = 'default'; in each model. If you do, it will overwrite the params you set in the constructor of BaseModel.

Maybe this will be usefull to someone. But this was a special case and I don't encourage anyone to design their database this way if you are using CodeIgniter + Datamapper.

Answer 2

I think you can't resolve relations over different databases. You have to emulate this with your own logic.