Thread

That’s what I figured :/

Is that fk issue what’s causing DataMapper to think there is a many-to-many relationship?

That will not work, all FK’s are suffixed with ‘_id’, hardcoded.

I’ve looked at it, but it’s going to be very complex to change this, and changing this will break all existing applications that use this (and expect the suffix to be there).

Changing this behaviour is on the roadmap for 2.0 (no ETA), but that doesn’t help you now.

That’s what I figured :/

Is that fk issue what’s causing DataMapper to think there is a many-to-many relationship

Datamapper contains logic to see how the relationship is defined, as it supports join tables even for one-to-one relations.

It does that by looking at the PK’s and FK’s in the relationship definition, and if they are not found in the related tables, assume you have a join table that contains the FK’s.

This is the case here, as the generated FK is wrong so it can’t be found.

Damn :/

Thanks for the insight!

Sorry to hear that you are dropping this. I’d take it over, but I neither have the time nor the skill. I love this library, and hope someone decent picks it up for you.

Sorry to hear that you are dropping this. I’d take it over, but I neither have the time nor the skill. I love this library, and hope someone decent picks it up for you.

Who is dropping what?

I’m not dropping anything, I’ve only postponed development on 2.0 (which will be an entirely new codebase) until it’s clear where CI 3 is heading…

I think there might be a bug in where_between(): it doesn’t enclose dates with quotes (which causes the database to perform a mathematical operation):

# <a href="http://www.example.com/?range&#910&#93=2012-07-04&range&#911&#93=2012-07-06">http://www.example.com/?range[0]=2012-07-04⦥[1]=2012-07-06</a>

$range = ( isset($_GET['range']) )? $_GET['range'] : array( 'SYSDATE() - INTERVAL 30 DAY' , 'SYSDATE()');

$orders->where_between('date', $range[0], $range[1])->get();

#result:

SELECT * FROM `orders` WHERE `date` BETWEEN 2012-07-04 AND 2012-07-06;

But

$orders->where_between('date',"'".$range[0]."'","'".$range[1]."'")->get();

#result:

SELECT * FROM `orders` WHERE `date` BETWEEN '2012-07-04' AND '2012-07-06';

Also, (string)$range[0] and ”{$range[0]}” don’t produce quoted strings.

Well spotted, and thanks for reporting it.

Fixed: https://bitbucket.org/wanwizard/datamapper/changeset/00e9af373d9c

If you’re on 1.8.2, you can apply this fix to your version of Datamapper, or download the latest from bitbucket.

Wow, that was fast—thanks!

I have troubles getting/deleting related data when the tables are in different databases.

class Company extends DataMapper {

 var $model = 'company';
 var $table = 'companies';
 var $prefix = "tbl_";

 var $db_params = 'superadmin';

 var $has_many = array('user');
....

class User extends DataMapper {

 var $model = 'user';
 var $table = 'users';
 var $prefix = "app_";

 var $db_params = 'default';

 var $has_one = array('company');
...

Now when I try to get the related company from a user like this:

...
 if($u->login()) {
  $u->company->get();
  $cid = $u->company->id;
 ...
 }
...

I get the error : “Table does not exist”.
Datamapper looks for the users table in the database where the companies table is while I’ve set different db_params for both models.

Did I forget something? Or doesn’t datamapper support cross database joins?

I have not tried cross database joins with datamapper but what SQL does it give you when you try this:

echo $u->company->get_sql();

Are the db_params added correctly?

It prints

SELECT * FROM (`tbl_companies`)

Shouldn’t there be a WHERE part ?

If I do

$u->company->get();

I get the error:

A Database Error Occurred

Error Number: 1146

Table 'a1210alf_beheer.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` = 5

As you can see the format isn’t database.table.field , just table.field

Ok, I think cross database joins are not supported :( :

http://stackoverflow.com/questions/7724644/codeigniter-datamapper-cross-database-joins

The same SO question I stumbled on yesterday :D ...
I’ve been lucky this time, because the user table has a foreign key company_id, I can just do

$c = new Company($u->company_id);

and go on from there… I hope this was the only situation where I needed cross database joins :|

That is a limitation of MySQL (actually, of the driver).

You can only do cross-database joins when both databases are accessed via the same connection, and then prefix the table names (of the non-default database) in the query with the database name.

This is a not-supported setup in CI (the CI query builder doesn’t prefix the table names), and therefore also not in Datamapper (which uses the CI query builder to construct the queries).

Saving cross database relations does work. Getting and deleting however fails. Strange. I’ll have to move everything to the same database I guess.