Queries across multiple databases with CakePHP

Most CakePHP documentation assumes you’ll be working with tables in the same database. However, how do you query related data when your tables are spread across multiple databases? Fortunately, CakePHP makes this a breeze with the CakePHP model’s ‘useDbConfig’ variable and a little table prefix manipulation I picked up from this post in the CakePHP Google group.

Before the detailed explanation, you can view a working demo. Please note, this demo assumes you have basic knowledge of Cake models and associations.

Cake’s database configuration file (/app/config/database.php) can store multiple database connections along with a default configuration. If you have a table that lives in a database other than the database specified in the default configuration, simply set the useDbConfig variable in the corresponding model to point to custom entry in database.php. For example, if the table `cars` lives in database `automobile`, add a configuration in database.php to look something like this:

	var $automobile = array(
		'driver' => 'mysql',
		'persistent' => false,
		'host' => 'localhost',
		'login' => 'username',
		'password' => 'password',
		'database' => 'automobile',
		'prefix' => '',
		'encoding' => 'utf8'
	);

A simple Car model would look like this:

class Car extends AppModel {
	var $name = 'Car';
	var $useDbConfig = 'automobile';
}

Now, whenever you query the Car model, CakePHP will point to the `automobile` database. Even if your Car model contains a foreign key like `dealer_id` that points to a table in another database, Cake will handle the necessary connections.

However, what happens when models that have a hasAndBelongsToMany relationship are in different databases? In the demo, Dealer hasAndBelongsToMany Salesman meaning salesmen work for many dealers and dealers employ many salesmen. But the dealers table is in database `db2` and the salesmen table is in database `db3`. Also, the relationship table `dealers_salesmen` is in the database `db2`.

A problem arises when you query the Salesman model. The HABTM relationship is defined in the Salesman model.

class Salesman extends AppModel {
	var $name = 'Salesman';
	var $actsAs = array('Containable');
	var $useDbConfig = 'db3';


	var $hasAndBelongsToMany = array(
        'Dealer' =>
            array(
				'className'              => 'Dealer',
				'joinTable'              => 'dealers_salesmen',
				'foreignKey'             => 'salesman_id',
				'associationForeignKey'  => 'dealer_id',
				'unique'                 => true,
				'conditions'             => '',
				'fields'                 => '',
				'order'                  => '',
				'limit'                  => '',
				'offset'                 => '',
				'finderQuery'            => '',
				'deleteQuery'            => '',
				'insertQuery'            => ''
            )
    );

}

The HABTM relationship parameters were derived by the bake script when I set up this project. As it is, CakePHP will act as if the join table `dealers_salesmen` lives in the same database as `salesmen`. But remember, `dealers_salesmen` lives in database `db2`. In other, we’ll get an table not found error if we don’t make the following adjustment.

To point CakePHP to the correct database, we need to add the ‘with’ parameter to the relationship options in the Salesman model. By default, Cake will create the model that corresponds to your join table on the fly and assume it’s in the same database as the model referencing it. However, by adding the ‘with’ option, you point Cake to a model you’ve created. AND, in that model you can set useDbConfig to point to the correct database. So, our revised model should like this….I’ve only added the ‘with’ option to the HABTM array. I’ve also created a dealers_salesman.php model in /app/models.

class Salesman extends AppModel {
	var $name = 'Salesman';
	var $actsAs = array('Containable');
	var $useDbConfig = 'db3';


	var $hasAndBelongsToMany = array(
        'Dealer' =>
            array(
				'className'              => 'Dealer',
				'joinTable'              => 'dealers_salesmen',
				'foreignKey'             => 'salesman_id',
				'associationForeignKey'  => 'dealer_id',
				'unique'                 => true,
				'conditions'             => '',
				'fields'                 => '',
				'order'                  => '',
				'limit'                  => '',
				'offset'                 => '',
				'finderQuery'            => '',
				'deleteQuery'            => '',
				'insertQuery'            => '',
				'with' => 'DealersSalesman' // force Cake to use a premade relationship model that specifies the correct db source
            )
    );

}

We’re all setup to makes queries via the Salesman model. We’re halfway there. The Dealer model still thinks the join table is in database `db3`. So rather than making adjustments to the Dealer model, we’re going to change the table prefix on the fly in the Dealer controller. The index action in dealers_controller.php should look like this:

	function index() {
		$this->pageTitle = $this->name;
		$this->Dealer->DealersSalesman->tablePrefix = $this->Dealer->DealersSalesman->getDatabaseName() .'.'. $this->Dealer->DealersSalesman->tablePrefix; // prefix salesmen_dealers

		// make the query w the prefix set above
		$this->set('dealer', $this->Dealer->find('all'));

	}

The function getDatabaseName() is located in app_model.php and looks like this:

	function getDatabaseName() {
		$conn =& ConnectionManager::getInstance();
		$db_name = $conn->config->{$this->useDbConfig}['database'];
		return $db_name;
    }

This function checks the database configuration associated with the dealers_salesman.php relationship model and uses the database name as a temporary table prefix. When the query is made using $this->Dealer->find(‘all’), Cake alters the reference to the join table using the table prefix.

Again, you can view a working demo. You can also download the project files for this tutorial. Keep in mind you’ll need to change the entries in database.php to fit your needs. Thanks to Grant Cox for the Google Groups post. Please post any questions via comments below.

This entry was posted in CakePHP. Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment or leave a trackback: Trackback URL.

2 Trackbacks

  1. […] it out the Foldi blog for updates on work, clients, etc… « Queries across multiple databases with CakePHP […]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>