CakePHP model linking, belongsTo, hasOne

Question

I read the CakePHP book but couldn't make it.
I have a table named frendslists. Every user (owner_user_id) has too many friends and I add the friends to friend_id column. (Model name is Friendslist)

CREATE TABLE IF NOT EXISTS `friendslists` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `owner_user_id` int(20) unsigned NOT NULL,
  `friend_id` int(20) NOT NULL COMMENT 'id of the friend',
  PRIMARY KEY (`id`),
  KEY `friend_id` (`friend_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--id-------owner_user_id-----friend_id
--------------------------------------
--1--------1234--------------9200-----
--2--------1234--------------3210-----
--3--------1234--------------7600-----

I also have a profiles table. Every unique person have a profile there. One person can have only one profile. (Model name is Profile)

CREATE TABLE IF NOT EXISTS `profiles` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `profile_user_id` int(20) NOT NULL,
  `name` varchar(50) NOT NULL,
  `location` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `profile_user_id` (`profile_user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--id------profile_user_id---name------location-
-----------------------------------------------
--1-------9200--------------michael----usa-----
--2-------3210--------------john-------uk------
--3-------7600--------------danny------denmark-

I want to link friendslists table to profiles table. Is this one to one (hasOne) or many to one (belongsTo) type relationship ?

When I query friendslists table I want to get profiles data of the friends. What should I do inside CakePHP models and tables?

I created a foreign key like this:

ALTER TABLE `friendslists`
  ADD CONSTRAINT `friendslists_ibfk_1` 
  FOREIGN KEY (`friend_id`) 
  REFERENCES `profiles` (`profile_user_id`) 
  ON DELETE CASCADE ON UPDATE CASCADE;

I changed model file to this:

class Friendslist extends AppModel {
    var $name = 'Friendslist';
    var $useTable = 'friendslists';

    public $belongsTo = array(
        'Profile' => array(
            'className'    => 'Profile',
            'foreignKey'   => 'friend_id'
        )
    )

    function getAll(){
        return $this->find('all');
    }
}

At last when I do this:

$records=$this->Friendslist->find('all', array('conditions' => array(
    'Friendslist.owner_user_id' => 1234)
));

I get these result:

     [Friendslist] => Array
            (
                [id] => 1
                [owner_user_id] => 1234
                [friend_id] => 9200
            )

        [Profile] => Array
            (
                [id] => 
                [profile_user_id] => 
                [name] => 
                [location] => 
             )

    )

I'm sure that profiles table has a record with profile_user_id=9200. But Profile records comes empty.

isick isick · Accepted Answer · 2013-10-08T16:45:04

I'm a little confused why you're linking friendslists to profiles. Wouldn't it make more sense to link people with people and then get the profile therefrom?

In any case, what you're describing is a HasAndBelongsToMany(HABTM)

So in your Person model you want to specify that they have many other people (or in your case Profiles) that they're associated with and specify the lookup table.

Something like...

public $hasAndBelongsToMany = array(
        'Person' => array(
            'className' => 'Profile',
            'joinTable' => 'friendslists',
            'foreignKey' => 'owner_id',
            'associationForeignKey' => 'friend_id'
        )
    );

And then within the friendslists model I'd describe it as a belongsTo listing both the owner and the profile.

Something like:

public $belongsTo = array(
    'Person' => array(
            'className' => 'Person'
           ,'foreignKey' => 'owner_user_id'
        ),
    'Profile' => array(
            'className' => 'Profile'
           ,'foreignKey' => 'friend_id'
        )
);

You may need to tweak the names as I had a hard time digesting exactly what entities are at play, but that should give you an idea at least.

CakePHP model linking, belongsTo, hasOne

1 Answers