I have the following tables for my CakePHP app that allows friendships between users:

**Users**
id
username
password

**Profiles**
id
firstname
lastname
user_id

**Friends**
id
user_id_to
user_id_from
status

So basically a user has a profile and a user can be friends with another user and this is recorded in the database table called friends with a simple status of confirmed or not using either 0 or 1 (it's an int). So friends is the join between two users.

I'm trying to list the friends for a user so for example if I get a url like:

/people/cameron/friends it will list the friends for the user Cameron.

However I'm struggling with the find statement to pass the user and find them (notice I contain the profile data) and then list friends that are related to that user. Can anyone help?

These are the Friend, User and Profile models:

class Friend extends AppModel
{
    public $name = 'Friend';

    public $belongsTo = array('User');

    public $actsAs = array('Containable');

}

User.php

   class User extends AppModel
    {
        public $name = 'User';

    public $hasOne = 'Profile';

    public $hasMany = array(
        'Post',
        'Answer',
        'Friend' => array(
            'foreignKey' => 'user_id_to'
        )
    );

public $belongsTo = array(
        'Friend' => array(
            'foreignKey' => 'user_id_from'
        )
    );

    public $actsAs = array('Containable');

        public function getFriends($username)
        {
        return $this->find('all',
        array('conditions' => array('User.username' => $username, 'Friend.status'=>1),
            'contain' => array('Friend' => array('User'))
        ));
        }
    }

Profile.php

class Profile extends AppModel
{
    public $name = 'Profile';

    public $belongsTo = 'User';

    public $actsAs = array('Containable');
}

and this is my method for showing the friend list for a user:

public function index( $username )
{
    $friends = $this->User->getFriends($username);

    $this->set('friends', $this->paginate());
}

I'm currently getting this error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'User.user_id_from' in 'on clause'
SQL Query: SELECT `User`.`id`, `User`.`username`, `User`.`password`, `User`.`email`, `User`.`status`, `User`.`code`, `User`.`lastlogin`, `Friend`.`id`, `Friend`.`user_id_from`, `Friend`.`user_id_to`, `Friend`.`datetime`, `Friend`.`status` FROM `db52704_favorr`.`users` AS `User` LEFT JOIN `db52704_favorr`.`friends` AS `Friend` ON (`User`.`user_id_from` = `Friend`.`id`) WHERE `User`.`username` = 'cameron' AND `Friend`.`status` = 1

It looks as though the app thinks the foreign keys are in the User table rather than the friend table even though they called within the Friend association... Any ideas what the problem is?

Any time you specify foreign keys for $belongsTo, remember that the name you specify is the name of the field in the current table, not the other table.

So, for example, your $belongsTo references to 'foreignKey' => 'user_id_to' should be in the Friends model, not the Users model.

Re-read Cake's docs, as it does get confusing (even after years of Cake apps, I still need to refresh when I start a new project): http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html

more