@WanWizard,
Below is my sample codes
class Model_User extends DataMapper {
public $table = 'users';
public $has_many = array(
'orders' => array(
'class' => 'model_item',
'other_field' => 'orders',
'join_table' => 'orders',
'join_self_as' => 'user',
'join_other_as' => 'item'
),
'favorites' => array(
'class' => 'model_item',
'other_field' => 'favorites',
'join_table' => 'favorites',
'join_self_as' => 'user',
'join_other_as' => 'item'
)
);
function __construct($id = NULL)
{
parent::__construct($id);
}
}
class Model_Item extends DataMapper {
public $table = 'items';
public $has_many = array(
'orders' => array(
'class' => 'model_user',
'other_field' => 'orders',
'join_table' => 'orders',
'join_self_as' => 'item',
'join_other_as' => 'user'
),
'favorites' => array(
'class' => 'model_user',
'other_field' => 'favorites',
'join_table' => 'favorites',
'join_self_as' => 'item',
'join_other_as' => 'user'
)
);
function __construct($id = NULL)
{
parent::__construct($id);
}
}
And here are my tables:
CREATE TABLE IF NOT EXISTS `mc_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(100) NOT NULL,
`password` varchar(100) NOT NULL,
`created` datetime NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `mc_items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`created` datetime NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `mc_orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`item_id` int(11) NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `mc_favorites` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`item_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
)
This is the code in my controller:
$this->output->enable_profiler(TRUE);
$user = new Model_User(1);
$user->orders->where_join_field('orders', 'status', 1)->get();
With this line of code, I got the following error:
Unknown column 'mc_orders.status' in 'where clause'
SELECT `mc_items`.* FROM (`mc_items`) LEFT OUTER JOIN `mc_orders` orders_orders ON `mc_items`.`id` = `orders_orders`.`item_id` WHERE ( `mc_orders`.`status` = 1 ) AND `orders_orders`.`user_id` = 1
I’ve tried any combination of codes using where_join_field, include_join_field, where_related, and a lot more I can think of, but still no luck.
Really need your help here, thanks.
