Kohana 3 ORM: How to get data from a pivot table? and all other tables on this subject

Question

I am trying to use ORM to access data stored in three mysql tables 'users', 'items' and a pivot table for many-many relationships: 'user_item'

I followed the directions of Kohana 3.0.x ORM: read extra columns in pivot tables

and tried

$user = ORM::factory('user',1); $user->items->find_all(); $user_item = ORM::factory('user_item', array('user_id' => $user, 'item_id' => $user->items)); if ($user_item->loaded()) { foreach ($user_item as $pivot) { print_r($pivot); } } 

But I get an SQL error:

"Unknown column 'user_item.id' in 'order clause' [SELECT user_item . * FROM user_item WHERE user_id = '1' AND item_id = '' ORDER BY user_item . id ASC LIMIT 1]"

This is clearly a mistake because Kohana is trying to arrange items by a column that does not exist: user_item.id. This id does not exist, since the primary keys of this pivot table are the foreign keys of two other tables: users and members.

Attempted use:

 $user_item = ORM::factory('user_item', array('user_id' => $user, 'item_id' => $user->items)) ->order_by('item_id', 'ASC'); 

It doesn't matter, since it seems that order_by() or any sql queries are ignored if the second factory argument is specified.

Another obvious mistake with this request is that item_id = '' when it should contain all the elements.

So my question is: how can I access the data stored in the pivot table , and in fact , how can I access all the elements belonging to a particular user, how did I have problems with this?

thanks

Answer 1

By default, all Kohana ORM models expect the primary key of the table to be "id". You need to set $ _primary_key in your model to something else.

Answer 2

$user_item = ORM::factory('user_item', array('user_id' => $user, 'item_id' => $user->items));

I think you need to provide one item_id value for this, not an array of objects.

In addition, to find all the entries for a single user, you must do this: $user_items = ORM::factory('user_item', array('user_id' => $user));

Does this answer your question?