Good morning,
I am currently working on a project that utilises Codeigniter and the Datamapper Library (http://datamapper.wanwizard.eu). The project uses a central database for user data (called "base") and a seperate database for the application data ("crm"). This is so that in the future, we may build other applications that use the user directory without being tied to a single server.
I have a problem in that I need to use the built in Datamapper relationships but across the two databases. Here is my setup so far:
Person Model
class person extends DataMapper {
var $db_params = 'base';
var $prefix = 'base_';
var $has_many = array(
'initiated_event' => array('class' => 'event','other_field' => 'initiator')
)
}
Event Model
class event extends DataMapper {
var $db_params = 'crm';
var $prefix = 'crm_';
var $has_one = array(
'initiator' => array('class' => 'person','other_field' => 'initiated_event')
)
}
The Problem
When I attempt to use $events->initiator->get(), I get the following error:
Error Number: 1146
Table 'circle_base.crm_events' doesn't exist
SELECT `base_persons`.* FROM (`base_persons`) LEFT OUTER JOIN `crm_events` initiated_event_crm_events ON `base_persons`.`id` = `initiated_event_crm_events`.`initiator_id` WHERE `initiated_event_crm_events`.`id` = 1
Filename: C:\xampp\htdocs\circle\crm\system\database\DB_driver.php
Line Number: 330
Could anybody shed some light on the matter? Does DataMapper actually support cross-database joins. If not, is there any way that I can achieve this?
You can't do this across databases, you can even see from your SQL that it is trying to query 1 single database (lack of . notation for database even). I am assuming both your databases are in the same server? In which case you could try writing a custom SQL query (not sure on your DBs) but in the query define the database.table relationship, and try with a simple statement.
Otherwise you will need to solve this conundrum programmatically via objects / arrays / etc;
References:
http://nathan.rambeck.org/blog/2-joining-mysql-tables-across-multiple-databases