Cakephp Find Method With Join

CakePHP find method with JOIN

There are two main ways that you can do this. One of them is the standard CakePHP way, and the other is using a custom join.

It's worth pointing out that this advice is for CakePHP 2.x, not 3.x.

The CakePHP Way

You would create a relationship with your User model and Messages Model, and use the containable behavior:

class User extends AppModel {
    public $actsAs = array('Containable');
    public $hasMany = array('Message');
}

class Message extends AppModel {
    public $actsAs = array('Containable');
    public $belongsTo = array('User');
}

You need to change the messages.from column to be messages.user_id so that cake can automagically associate the records for you.

Then you can do this from the messages controller:

$this->Message->find('all', array(
    'contain' => array('User')
    'conditions' => array(
        'Message.to' => 4
    ),
    'order' => 'Message.datetime DESC'
));

The (other) CakePHP way

I recommend using the first method, because it will save you a lot of time and work. The first method also does the groundwork of setting up a relationship which can be used for any number of other find calls and conditions besides the one you need now. However, cakePHP does support a syntax for defining your own joins. It would be done like this, from the MessagesController:

$this->Message->find('all', array(
    'joins' => array(
        array(
            'table' => 'users',
            'alias' => 'UserJoin',
            'type' => 'INNER',
            'conditions' => array(
                'UserJoin.id = Message.from'
            )
        )
    ),
    'conditions' => array(
        'Message.to' => 4
    ),
    'fields' => array('UserJoin.*', 'Message.*'),
    'order' => 'Message.datetime DESC'
));

Note, I've left the field name messages.from the same as your current table in this example.

Using two relationships to the same model

Here is how you can do the first example using two relationships to the same model:

class User extends AppModel {
    public $actsAs = array('Containable');
    public $hasMany = array(
        'MessagesSent' => array(
            'className'  => 'Message',
            'foreignKey' => 'from'
         )
    );
    public $belongsTo = array(
        'MessagesReceived' => array(
            'className'  => 'Message',
            'foreignKey' => 'to'
         )
    );
}

class Message extends AppModel {
    public $actsAs = array('Containable');
    public $belongsTo = array(
        'UserFrom' => array(
            'className'  => 'User',
            'foreignKey' => 'from'
        )
    );
    public $hasMany = array(
        'UserTo' => array(
            'className'  => 'User',
            'foreignKey' => 'to'
        )
    );
}

Now you can do your find call like this:

$this->Message->find('all', array(
    'contain' => array('UserFrom')
    'conditions' => array(
        'Message.to' => 4
    ),
    'order' => 'Message.datetime DESC'
));

Issue with CakePHP 2 find method with JOIN

You can use below query

$joins = array(
        array('table' => 'orders',
            'alias' => 'Order',
            'type' => 'INNER',
            'conditions' => array(
                'Order.customer_id = Customer.id',
            )
        )
    );
    $conditions = [];
    $conditions[] = ['Customer.type' => 2];
    $conditions[] = ['Customer.status'=>1];
    $this->Customer->virtualFields['total_due'] = "sum(Order.due_amount)";
    $data_array = $this->Customer->find('all', ['conditions' => $conditions, 'group' => 'Customer.id', 'joins' => $joins, 'fields' => ['Customer.*', 'Customer.total_due']]);

Join query retrieving data from only one table cakephp 3

Manually adding joins won't cause any additional data to be selected, you'd have to do that on your own by specifying the fields to be selected via the select() method.

$events = $events_table
    ->find('all') 
    ->hydrate(false)
    ->select($events_table)
    // either add the fields one by one, or pass a table object
    // as above to select all fields of the table
    ->select(['CourseType.id', 'CourseType.xyz', /* ... */])
    // ...

I'd suggest to use use containments instead, ie setup the required association if not already present, and then simply use contain():

$events = $events_table
    ->find('all') 
    ->hydrate(false)
    ->contain('CourseType')
    ->where(['Events.id' => $id]);

How to make a Join on query with Cakephp?

Chnage your relationships to something like this:

$this->belongsTo('Commenters', [
    'className' => 'Users',
    'foreignKey' => 'commentator_id',
    'joinType' => 'INNER'
]);
$this->belongsTo('Users', [
    'foreignKey' => 'commented_id',
    'joinType' => 'INNER'
]);

So then you can contain the two different relationships.

$comments = TableRegistry::getTableLocator()
        ->get('Comments')
        ->find('all')
        ->contain(['Commenters', 'Users']);

And access them like this:

$comment->commenter->name; // Name of the person who commented
$comment->user->name; // Name of the person who was commented on

Cakephp 3 findAuth join table return array

What I finally ended up going with is this:

public function findAuth(\Cake\ORM\Query $query, array $options)
{
    $query
    ->select(['id', 'username', 'passwordHash', 'locked'])
    ->join([
        'table' => 'user_roles',
        'conditions' => ['user_roles.user_id = Users.id']])
     ->contain(['UserRoles.Roles'])
    ->join([
        'table' => 'roles',
        'conditions' => ['roles.id = user_roles.role']])            
    ->group(['Users.username']);
    return $query;
}

This gives me a multi-dimensional array of:
user_roles[index[id, user_id, roles[id, role]]]

in my case I have 2 entries for the index (0 and 1) and I can check the role of the user with the in_array function within a foreach loop

Cakephp 3 join satetment with where clause

The option is named join (singular), not joins, just like the corresponding query builder method.

I would also suggest that you look into setting up associations for your relationships, which would then allow you to use the innerJoinWith() and leftJoinWith() methods (among others), making your query building much easier.

Also note that you need to manually specify the fields the fields that you want to select from the joins.

Cakephp Find Method With Join