CODEWITHSUNDEEP
phpmysqlsqlcakephpjoin
James Dawson
James Dawson

Reputation: 5409

SQL JOINs with CakePHP

I have an images table and a servers table. images has a server_id field which is a foreign key to the id field in the servers table. The servers table also has a field called name, which is what I want to retrieve.

Here's my controller action code:

$images = $this->Image->find('all', array(
    'conditions' => array('Image.user_id' => $this->Auth->user('id')),
    'order' => array('Image.uploaded DESC')
));
$this->set('images', $images);

It gets data like this:

Array
(
    [0] => Array
        (
            [Image] => Array
                (
                    [id] => 103
                    [orig_name] => Untitled-5.jpg
                    [hash] => MnfWKk
                    [filename] => MnfWKk.jpg
                    [uploaded] => 2012-07-12 00:09:08
                    [views] => 0
                    [album_id] => 
                    [user_id] => 15
                    [server_id] => 1
                )

        )
)

Instead of server_id, I want to get the name field from the servers table. How can I adapt my find() method to get this? I know it's an SQL join, but I have no idea how to tell Cake to do one in order to get the servers name.

Thanks.

Upvotes: 0

Views: 1516

Answers (3)

Samar Haider
Samar Haider

Reputation: 912

Easy and alternate way for begginers

$images = $this->Image->find('all', array(
    'conditions' => array('Image.user_id' => $this->Auth->user('id')),
    'joins' => array(
                    array(
                        'table' => 'servers',
                        'alias' => 'Server',
                        'type' => 'inner',  //join of your choice left, right, or inner
                        'foreignKey' => true,
                        'conditions' => array('Image.server_id=Server.id')
                    ),
                ),
    'order' => array('Image.uploaded DESC')
));

This is very good in performance

Upvotes: 0

Dave
Dave

Reputation: 29121

TLDR:

Set up the correct CakePHP associations, and use CakePHP's Containable. (with recursive -1).

Longer Description:

It's best practice to keep your find code in the model itself, so that's what I'll show, but feel free (if you must) to move it back into the controller.

Doing it this way allows you to call the same getImages() function from any controller, and just pass different parameters based on what you want returned. The benefit to coding like this is, you always know if you're looking for code related to queries/database, that you should be looking in the model. It's VERY beneficial when the next person who looks at your code doesn't have to go searching.

Because of the association set up between Image and Server, you can then "contain" the Server info when you query images. But - you can't use "contain" until you specify that you want your model to $actAs = array('Containable');. [ CakePHP Book: Containable ]

Lastly, in your AppModel, it's good practice to set $recursive = -1;. That makes it default to -1 for all models. If for some reason you're against doing that, just make sure to set recursive to -1 any time you use containable. And - once you learn to use containable, you'll never look back - it's awesome. There are a lot more things you can

Code:

//AppModel *******
//...
$recursive = -1;
//...

//Images controller *******
//...
public function whatever() {
    $opts = array();
    $opts['user'] = $this->Auth->user('id');
    $images = $this->Image->getImages($opts);
    $this->set(compact('images'));
}
//...


//Image model *******
//...
public $actsAs = array('Containable');

public belongsTo = array('Server');

public function getImages($opts = array()) {

    $params = array('condtions'=>array());

    //specific user
    if(!empty($opts['user'])) {
        array_push($params['conditions'], array('Image.user_id'=>$opts['user']);
    }

    //order
    $params['order'] = 'Image.uploaded DESC';
    if(!empty($opts['order'])) {
        $params['opts'] = $opts['order'];
    }

    //contain
    $params['contain'] = array('Server');

    //returns the data to the controller
    return $this->find('all', $params);
}

A few other notes

  • You should also set the association in your Server model.
  • The code example I gave is written fairly verbosely (is that a word?). Feel free to condense as you see fit
  • You can also extend the model's getImages() method to accept a lot more parameters like find, limit...etc. Customize this all you want - it's not THE way to do it - just similar to what I usually use.
  • Per your question, if you only need one field, you can specify in the "contain" what fields you want - see the book for details.
  • It might seem confusing now, but it's SOO worth learning how to do this stuff right - it will make your life easier.

Upvotes: 1

pollirrata
pollirrata

Reputation: 5286

Cake have a lot of model relationships to achieve this. Check out this page, I think you'll be using the belongsTo relationship

Upvotes: 0

Related Questions