How to make join in cakephp?

@deron 

To make a join in CakePHP, you can use the association methods provided by the CakePHP ORM. Here's an example of how to perform a join:

1. First, define the associations between the models in their respective table classes. For example, assume you have two tables - Users and Posts. In the User.php table class:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

namespace AppModelTable;

use CakeORMTable;

class UsersTable extends Table
{
    public function initialize(array $config): void
    {
        $this->hasMany('Posts');
    }
}

In the Post.php table class:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

namespace AppModelTable;

use CakeORMTable;

class PostsTable extends Table
{
    public function initialize(array $config): void
    {
        $this->belongsTo('Users');
    }
}

1. Now, in the controller or wherever you want to perform the join, load the necessary table classes:

1
2

use AppModelTableUsersTable;
use AppModelTablePostsTable;

1. Finally, perform the join using the find() method of the table. For example, to get all posts with their associated user details:

1
2
3
4

$postsTable = new PostsTable();
$posts = $postsTable->find('all')
    ->contain('Users')
    ->toArray();

The contain() method will perform a join on the associated table and retrieve the related data.

1. You can also specify specific fields to retrieve from the related table by passing an array of fields to the contain() method. For example, to retrieve only the username from the associated user table:

1
2
3
4
5

$posts = $postsTable->find('all')
    ->contain('Users', function($q) {
        return $q->select(['username']);
    })
    ->toArray();

This will optimize the retrieved data by only fetching the required fields.