problem with advanced relationship using codeigniter and datamapper

Question

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

score 0 · Answer 1 · answered Aug 26 '11 at 18:31

0

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.

answered Aug 26 '11 at 18:31

Josh

932
7
12

its not that, its how do i define the relationship as - the field customer_id in the order table relates to user_id in the customer table- – Chris Mccabe Aug 26 '11 at 20:54
1

The user_id in the customer table is a foreign key for the id field in the user table correct? Have you tried $has_one = array('customer' => array('class'=>'user'))? – Josh Aug 29 '11 at 16:45

problem with advanced relationship using codeigniter and datamapper

1 Answers1