DMZ 1.7.1 (DataMapper OverZealous Edition)

[eluser]Jacob F.[/eluser]
Hiya,

I'm trying to use a deep relationship

Code:

$groups->include_related('user/order',array('tweight','tprice'), TRUE)->get();

These are legacy tables from before my time that I'm in the early stages of re-factoring (but can't change them yet). The orders table has a different/nonsense name, there are no foreign key constraints, and the field-names where there should be foreign keys have non-DataMapper-friendly names (like `id_user`).

The path from groups to users works fine (it's new), but when jumping from users to orders, DataMapper is convinced there is a many-to-many relationship (it's really one-to-many). I specified the following:

Code:

#User

$table = 'appuser';
$has_many = array(
'order' => array(
  'join_table' => 'oldorders',
  'join_self_as' => 'id_users'
)
);

Code:

#Order

$table = 'oldorders';
$has_one = array(
'user' => array(
  'join_table' => 'oldorders'
)
);

I feel like it's a hack to specify the join_table like that, but then DataMapper kind of accepts the one-to-many relationship: it eliminated the join for a relational table between users and orders, but it still uses the computed name of the non-existent relational table. Also, it doesn't completely respect the join_self_as: it takes the value and appends it with _id.

Here's the SQL DataMapper spits out:

Code:

#Without 'join_table'

SELECT
`groups`.*,
`oldorders`.`tweight` AS appuser_order_tweight,
`oldorders`.`tprice` AS appuser_order_tprice
FROM (`groups`)
LEFT OUTER JOIN `groups_appuser` groups_appuser
  ON `groups`.`id` = `groups_appuser`.`group_id`
LEFT OUTER JOIN `appuser` appuser
  ON `appuser`.`id` = `groups_appuser`.`appuser_id`
LEFT OUTER JOIN `oldorders_appuser` appuser_oldorders_appuser
  ON `appuser`.`id` = `appuser_oldorders_appuser`.`appuser_id`
LEFT OUTER JOIN `oldorders` appuser_oldorders
  ON `appuser_oldorders`.`id` = `appuser_oldorders_appuser`.`id_appuser_id`
ORDER BY `groups`.`name`;

Code:

#With 'join_table'

SELECT
`groups`.*,
`appuser_oldorders`.`tweight` AS appuser_order_tweight,
`appuser_oldorders`.`tprice` AS appuser_order_tprice
FROM (`groups`)
LEFT OUTER JOIN `groups_appuser` groups_appuser
  ON `groups`.`id` = `groups_appuser`.`group_id`
LEFT OUTER JOIN `appuser` appuser
  ON `appuser`.`id` = `groups_appuser`.`appuser_id`
LEFT OUTER JOIN `oldorders` appuser_oldorders
  ON `appuser`.`id` = `appuser_oldorders`.`id_appuser_id`
ORDER BY `groups`.`name`;

Code:

#Correct SQL

SELECT
`groups`.*,
`oldorders`.`tweight` AS order_tweight,
`oldorders`.`tprice` AS order_tprice
FROM (`groups`)
LEFT OUTER JOIN `groups_appuser` groups_appuser
  ON `groups`.`id` = `groups_appuser`.`group_id`
LEFT OUTER JOIN `appuser` appuser
  ON `appuser`.`id` = `groups_appuser`.`appuser_id`
LEFT OUTER JOIN `oldorders` oldorders
  ON `appuser`.`id` = `oldorders`.`id_appusers`
ORDER BY `groups`.`name`;

EDIT: I tried changing both models to use $has_one and it spat out the same code. Also, it seems to ignore any declaring I do to user in the order model.