Kohana many-to-many relationship “has many - through”

Question

I'm using kohana v3.3, and i would like to know if there is a possibility to get/save another data in the pivot table or not ?

let's take the Auth example :

So we have 3 tables (roles, users, roles_users) and i added another column "date" on the pivot table

Tables :

CREATE TABLE IF NOT EXISTS roles (

id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,

name varchar(32) NOT NULL,

description varchar(255) NOT NULL,

PRIMARY KEY (id),

UNIQUE KEY uniq_name (name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

CREATE TABLE IF NOT EXISTS roles_users (

user_id int(10) UNSIGNED NOT NULL,

role_id int(10) UNSIGNED NOT NULL,

date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (user_id,role_id),

KEY fk_role_id (role_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

CREATE TABLE IF NOT EXISTS users (

id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,

email varchar(254) NOT NULL,

username varchar(32) NOT NULL DEFAULT '',

password varchar(64) NOT NULL,

logins int(10) UNSIGNED NOT NULL DEFAULT '0',

last_login int(10) UNSIGNED,

PRIMARY KEY (id),

UNIQUE KEY uniq_username (username),

UNIQUE KEY uniq_email (email)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Models

Model Role

class Model_Auth_Role extends ORM {

protected $_has_many = array(
  'users' => array('model' => 'User','through' => 'roles_users'),
);

User Model

class Model_Auth_User extends ORM {

protected $_has_many = array(
  'roles'       => array('model' => 'Role', 'through' =>
'roles_users'),

);

Controller

public function action_create()

{

  $model = ORM::factory('user');

  $model->username = 'myusername';

  $model->password = 'password';

  $model->email = '[email protected]';

  $model->save();

  **// How can i set the "date" to "roles_users" table ?**

  $model->add('roles', ORM::factory('role')->where('name', '=',

'login')->find());

}

puboic function action_get()

{

$users = ORM::factory('user')->find_all();

  foreach ($users as $user)

  {

      $roles = $user->roles->find_all();

      echo $user->email." : <br>";

      **// How can i get the "date" from "roles_users" table ?**

      foreach ($roles as $role)

          echo " ->".$role->name."<br>";

  }

}

Questions

I have two questions :

1- How can i set the "date" to "roles_users" table ? on controller action_create()

2- How can i get the "date" from "roles_users" table ? on controller action_get()

Thanks in advance.

AmazingDreams AmazingDreams · Accepted Answer · 2014-01-23T08:19:53

If that is what you want, you can do two things:

Create a seperate Model_Roles_User and use it to create the relationship and get/set its properties.

This would require you to add an id AUTO_INCREMENT column as Kohana does not natively support compound keys; or be a good man and extend kohana's functionality to support it :-)

Leave it as is and use some custom queries to SELECT or UPDATE the rows

This should also be fairly easy:

$result = DB::select('date')
    ->from('roles_users')
    ->where('user_id', '=', $user_id)
    ->and_where('role_id', '=', $role_id)
    ->execute();

The answer to question #1 is rather easy. The database already takes care of this! (date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,)

Kohana many-to-many relationship “has many - through”

1 Answers