Kohana / PHP - 为“收藏夹”表定义ORM关系

Question

I have 3 tables in the following format.

• users

id

FirstName

LastName

• userjobs

id

jobinfo

• starredjobs

id

user_id

userjob_id

comments

enddate

The 'starredjobs' table holds all the jobs which an user starred/added to favorites. I have defined the following relationships in their respective model files.

• user.php

protected $_has_many = array( 'starredjobs' => array('model' => 'starredjobs' , 'foreign_key'=>'user_id'),

• starredjob.php

  protected $_belongs_to = array('user' => array('model' => 'user','foreign_key' => 'user_id'));

  protected $_has_many = array('jobs' => array('model'=> 'userjob', 'foreign_key'=> 'job_id'));

• userjobs.php

  none

The idea is to retrieve all the starred jobs and details regarding jobs from the user object. A user can 'n' number of jobs and A job can be starred by 'n' number of users.

Am i defining relationships correctly?

Answer 1

Short answer: No. What you have here is a typical n:m relationship which can easily be used in Kohana using has_many "through" (as is used in the default roles users-relationship). But this doesn't allow for extra attributes in the "middle" table, so you need to use 2 has_many with corresponding belongs_to.

This can be described in plain English like so:

• One user has many starredjobs.
• One job has many starredjobs.
• One starredjob belongs to one user and one job

Also consider the difference between far_key and foreign_key (official doc sadly doesn't cover it), but one easy rule to remember: The key in the other table is far away -> it is the far_key.

This would give you the following

user.php

$_has_many = array(
    'starredjobs' => array(
        'model' => 'Starredjob',
        'far_key' => 'user_id'
    )
);

userjob.php

$_has_many = array(
    'starredbyuser' => array(
        'model' => 'Starredjob',
        'far_key' => 'userjob_id'
    )
);

starredjob.php

$_belongs_to = array(
    'user' => array(
        'model' => 'User',
        'foreign_key' => 'user_id'
    ),
    'job' => array(
        'model' => 'Userjob',
        'foreign_key' => 'userjob_id'
    )
);

Now you can do various things such as:

//get all jobs starred by given $user
foreach ($user->starredjobs->find_all() as $starredjob) {
    //info on userjob via $starredjob->job->jobinfo, etc.
    //info from pivot table via $starredjob->comments, etc.
}

//get all users that starred a given $userjob
foreach ($userjob->starredbyuser->find_all() as $starredjob) {
    //info on user via $starredjob->user->FirstName, etc.
    //info from pivot table via $starredjob->comments, etc.
}