problem with advanced relationship using codeigniter and datamapper

i have three tables customer, order and user - user is the login table and customer holds address info and so on.

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `image_path` varchar(100) NOT NULL,
  `sample_path` varchar(255) NOT NULL,
  `status` enum('new','progress','completed','sent') NOT NULL,
  `placed` date NOT NULL,
  `updated_date` date NOT NULL,
  `will_send` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) 


CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `address_1` varchar(150) NOT NULL,
  `address_2` varchar(150) NOT NULL,
  `address_3` varchar(150) NOT NULL,
  `postcode` varchar(50) NOT NULL,
  `phone` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ;


CREATE TABLE IF NOT EXISTS `users` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` mediumint(8) unsigned NOT NULL,
  `ip_address` char(16) NOT NULL,
  `username` varchar(15) NOT NULL,
  `password` varchar(40) NOT NULL,
  `salt` varchar(40) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `activation_code` varchar(40) DEFAULT NULL,
  `forgotten_password_code` varchar(40) DEFAULT NULL,
  `remember_code` varchar(40) DEFAULT NULL,
  `created_on` int(11) unsigned NOT NULL,
  `last_login` int(11) unsigned DEFAULT NULL,
  `active` tinyint(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
)  ;

my problem lies in the the ID from the user table is the main one- it relates to customer (as user_id) then customer relates to order

what i want to do is have my order table have a relationship based on customer_id in both orders and开发者_运维百科 customers

i have tried this -

var $has_one = array(
                    'customer' => array('join_self_as'=>'customer','join_other_as' => 'user')
                    );

but thats not working as it produces the following query- which references customer.id instead customer.user_id

SELECT * FROM `customers` LIMIT 1 
0.0004       SELECT `customers`.*
FROM (`customers`)
LEFT OUTER JOIN `orders` orders ON `customers`.`id` = `orders`.`customer_id`
WHERE `orders`.`id` = 27 

hopefully this explains what i am trying to do

I don't think you want to join as a variable... I think what you want is a deep relationship query.

You can do this..

$order = new Order();
$order->include_related('customer/user', *, true, true)->get();

Then you can access the user like this:

$order->customer->user->name;

That is if I am understanding your problem correctly.