Many-to-Many Relationships in Cohan 3.2

Question

I have problems trying to make this work. I have 2 models, User_Pro and Category. I read the relationship documentation in the kohana manual, and I understand that I need to define the $_has_many relationships on the User_Pro and Category models and create a user_pro_categories model with the own_to field.

User_Pro Model:

 protected $_has_many = array( 'categories' => array( 'model' => 'TM_Category', 'through' => 'user_pro_categories', 'foreign_key' => 'id_user_pro', ), ); 

Model Category:

 protected $_has_many = array( 'users_pro' => array( 'model' => 'TM_User_Pro', 'through' => 'user_pro_categories', 'foreign_key' => 'id_category', ), ); 

user_pro_categories model:

  protected $_belongs_to = array( 'user_pro' => array( 'model' => 'TM_User_Pro', 'foreign_key' => 'id_user_pro', ), 'category' => array( 'model' => 'TM_Category', 'foreign_key' => 'id_category', ), ); 

The error I am getting is:

 Database_Exception [ 1054 ]: Unknown column 'tm3_user_pro_categories.category_id' in 'on clause' [ SELECT `tm3_tm_category`.* FROM `tm3_categories` AS `tm3_tm_category` JOIN `tm3_user_pro_categories` ON (`tm3_user_pro_categories`.`category_id` = `tm3_tm_category`.`id_category`) WHERE `tm3_user_pro_categories`.`id_user_pro` = '2' ] 

I like that fk i'm defined and he wants to use the suffix thing ... any idea?

Answer 1

Here is an example to help you quickly understand how Kohana ORM works. And wish it was beneficial to others.

Student model

 <?php defined('SYSPATH') or die('No direct script access.'); class Model_Student extends ORM { protected $_primary_key = 'idstudent'; // take a look protected $_has_many = array( 'courses'=> array( 'model' => 'course', // Course model 'through' => 'students_courses', // many-to-may through 'far_key' => 'id_for_course', // "column name" relating to the Course Model in "students_courses" table 'foreign_key' => 'id_for_student' // "column name" relating to the Student Model in "students_courses" table ), ); } 

Course Model

 <?php defined('SYSPATH') or die('No direct script access.'); class Model_Course extends ORM { protected $_primary_key = 'idcourse'; // take a look protected $_has_many = array( 'students'=> array( 'model' => 'student', 'far_key' => 'id_for_student', 'through' => 'students_courses', 'foreign_key' => 'id_for_course' ), ); } 

SQL script

 CREATE TABLE IF NOT EXISTS `students` ( `idstudent` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`idstudent`) ) ENGINE=MyISAM; INSERT INTO `students` (`idstudent`, `name`) VALUES (1, 's1'), (2, 's2'); /* column idcourse and PR idcourseS ? */ CREATE TABLE IF NOT EXISTS `courses` ( `idcourse` int(11) NOT NULL, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`idcourse`) ) ENGINE=MyISAM; INSERT INTO `courses` (`idcourse`, `name`) VALUES (1, 'c1'), (2, 'c2'), (3, 'c3'); CREATE TABLE IF NOT EXISTS `students_courses` ( `id_for_student` int(10) unsigned NOT NULL, `id_for_course` int(10) unsigned NOT NULL ) ENGINE=MyISAM; INSERT INTO `students_courses` (`id_for_student`, `id_for_course`) VALUES (1, 1), (1, 3); 

---

  $student = new Model_Student(1); $courses = $student->courses->find_all(); echo Debug::vars($courses); foreach($courses as $course) { echo Debug::vars($course->object()); } 

Running the code above will create the following SQL query.

 SELECT `course`.* FROM `courses` AS `course` JOIN `students_courses` ON (`students_courses`.`id_for_course` = `course`.`idcourse`) WHERE `students_courses`.`id_for_student` = '1' 

Answer 2

You do not need to create a model for a pivot table for many-to-many relationships. Just define the through option in both models, make sure your primary / foreign keys follow the Kohana agreement, and you're ready to go. The following is an example of Kohana ORM user models and roles:

 class Model_User { protected $_has_many = array( 'roles' => array('model' => 'role', 'through' => 'roles_users'), ); } class Model_Role { protected $_has_many = array( 'users' => array('model' => 'user', 'through' => 'roles_users') ); }