Welcome Guest, Not a member yet? Register   Sign In
Datamapper relation between tables in different databases
#1

[eluser]Unknown[/eluser]
Hi all,

I am using CI together with Datamapper as ORM. I have 2 database from which I want to make a relationship between two tables. It works with the two tables in the same database but not apart.

I have a model 'Aircraft', which is connected with a table in one database, which CI connects to
Code:
class Aircraft extends DataMapper {
    var $table = 'aircraft';
    
    var $has_one = array(
        'type' => array(
            'class' => 'type',
            'other_field' => 'type'),
        'airport' => array(
            'class' => 'airport',
            'other_field' => 'id',
            'join_table' => 'flightbook.airports')
    );

and a model 'Airport', connected to another database 'flightbook'
Code:
class Airport extends DataMapper {
    var $table = 'flightbook.airports';
    
    var $has_many = array('aircraft');

The relation I'm trying to use is
Code:
$aircraft->select('airport_id')->include_related('airport', array('name', 'ICAO'))->group_by('airport_id')->get();

The following error occurs:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.airports ON `flightbook`.`airports`.`id` = `aircraft`.`airport_id` GROUP BY `ai' at line 3

SELECT `aircraft`.`airport_id`, `flightbook`.`airports`.`name` AS airport_name, `flightbook`.`airports`.`ICAO` AS airport_ICAO FROM (`aircraft`) LEFT OUTER JOIN `flightbook`.`airports` flightbook.airports ON `flightbook`.`airports`.`id` = `aircraft`.`airport_id` GROUP BY `aircraft`.`airport_id`

Searching the internet/forums left me stranded

Thanks in advance
#2

[eluser]WanWizard[/eluser]
You can only run queries over multiple databases if you RDBMS platform supports that.

In case of MySQL, it is only supported if you access both through the same connection. Datamapper by default creates a unique $this->db instance for every model, because CI isn't "thread-safe" (due to the singleton architecture, simultaneously creating two queries on two different model objects would never work if only one instance would be used).

You can however configure Datamapper to do this through the db_param configuration value.




Theme © iAndrew 2016 - Forum software by © MyBB