CODEWITHSUNDEEP
phpmysqlcakephp
user1104854
user1104854

Reputation: 2167

looking up table-join value from database with cakephp

I'm trying to access a user's name in a view on cakephp. I'm given a userid and I need to use that to look up their username which is stored in a different table.

The table that I'm retrieving displaying is messages and is as follows

id |  title | message | from_user  | to_user | date

The users table which contains the username

id | username | email | password

Anyway how can I look up the username of from_user in the message? I'm still getting used to cake so I don't know how to make a "join" statement without actually writing a query.

Should I send the username from the action in the controller or is it OK to do this from the view?

edit- I got it to work

Here is my index function now

public function index() {

  $options['joins'] = array(
    array('table' => 'Users',
        'type' => 'inner',
            'fields' => array('user.username'),
        'conditions' => array(
            'Message.from_user = User.id',
            'alias' => 'user_id')
    )
);

$message = $this->Message->find('all', $options);

$this->set('messages', $message);
}

I added this to my Message controller and the username now displays, but ONLY for the first message. All of the others it's blank.

public $belongsTo = array('User');

Upvotes: 0

Views: 555

Answers (2)

Krishna
Krishna

Reputation: 1540

You have to redefine your relationship in Message model :

public $belongsTo = array(
    'Sender' => array(
        'className' => 'User',
        'foreignKey' => 'from_user'
    ),
    'Recipient' => array(
        'className' => 'User',
        'foreignKey' => 'to_user'
    )
);

Upvotes: 1

tigrang
tigrang

Reputation: 6767

You can use containable behavior to retrieve that record. What I do is add the behavior to AppModel and set recursive to -1 in AppModel.

Then your read() call would turn into

public function view($id) {
    $message = $this->Message->find('first', array(
        'conditions' => array('Message.id' => $id),
        'contain' => array(
            'FromUser' => array('fields' => array('FromUser.username')),
        ),
    ));
    $this->set('message', $message'); // or just $this->set(compact('message'));
}

In your view:

$message['FromUser']['username'];

You may also do a debug($message); either in the view or controller to see what exactly is inside the variable and the structure of it.

Assuming you have your relations set up correctly, this should retrieve the FromUser.username field associated with the message record.

More on containable: http://book.cakephp.org/2.0/en/core-libraries/behaviors/containable.html (2.x) and http://book.cakephp.org/view/1323/Containable (1.3)

Upvotes: 0

Related Questions