Get data from multiple tables: contain

Contents

CakePHP
1. Setting up CakePHP3 with CentOS

2. Bake and Migration

3. Add log-in feature to the website

4. Add a calendar page

5. Use "contain"

6. Use find('list')

Preparation

At first, add a column to the calendars table. Run the following command to do so:

$ bin/cake bake migration AddUserIdToCalendars user_id:int 

Then migrate the migration file:

$ bin/cake migrations migrate

Then change the calendars controller as follows:

<?php
namespace App\Controller;
use App\Controller\AppController;

class CalendarsController extends AppController
{
    public function index()
    {
        $calendars= $this->paginate($this->Calendars);
        $this->set(compact('calendars'));
    }

    public function add()
    {
        $calendars = $this->Calendars->newEntity();
        if ($this->request->is('post')) {
            $userLogingIn = $this->request->session()->read('Auth.User'); //Add this line!
            $calendars->user_id = $userLogingIn['id']; // Add this line!
            $calendars = $this->Calendars->patchEntity($calendars, $this->request->getData());
            if ($this->Calendars->save($calendars)) {
                $this->Flash->success(__('The event has been saved in the calendar.'));

                return $this->redirect(['action' => 'index']);
            }
            $this->Flash->error(__('The event could not be saved. Please, try again.'));
        }
        $this->set(compact('calendars'));
        $this->set('_serialize', ['calendars']);
    }
}

These lines were added:
            $userLogingIn = $this->Session->read('Auth.User'); //Add this line!
            $calendars->user_id = $userLogingIn['id']; // Add this line!

These lines are inserting the user's data to the entity and saving the entity. Thanks to these lines, we can save who saved the calendar information.

Maybe save a data for calendars as a test:

You can see that user_id is saved at a same time.

Bake the model again and rewrite the table file:

bin/cake bake model Calendars
bin/cake bake model Users

(Please make sure that you don't need to rewrite the entity files! If you mistakenly rewrite the entity files, you need to define the virtual fields again in the entity file. Also make sure that the id column's name must be tableNameWithoutS_id like user_id. Otherwise you can't use "contain". If you have created another parent table ("employee" or "customer" table for example) over user table, name the column like "employee_user_id" or "customer_user_id", then bake the models so that Cakephp will automatically create necessary files and codes to use "contain".)

Use "contain"

Change the Calendars controller's index method as follows:

    public function index()
    {
        $this->paginate = [
            'contain' => ['Users']
        ];
        $calendars = $this->paginate($this->Calendars);
        $this->set(compact('calendars'));
    }

Now you access both tables: calendars and users because of the "contain". This enables you to inner join the two tables.

Change the index.ctp of Calendars as follows:

<h1>Calendar</h1>
<p>This page is made to display calendar.</p>
<?php echo '<span class="text-center"><p>Today:'. Date('d/m/Y').'</p></span>';
$events = array();
$event = "";
foreach ($calendars as $c):
    //You can access the user table's information too!   
    $event = "Event: " . $c->event . ". Assignee: " . $c->user->username . ".";
    $events[$c->day][] = ['event'=>$event];
endforeach;
$this->Calendars->makeCalendar(Date('m'), Date('Y'), $events); ?>

This is how to get data from calendars and users at same time.

You can also use "contain" for find method:

$this->Calendars
        ->find()
        ->contain([
            'Users'
        ])
        ->all();

Then you can access relevant users table's information like the following:

foreach ($calendars as $c):
        echo $c->user->username;
endforeach;

You can use it for the get method too:

$calendars = $this->Calendars->get($id, [
    'contain' => ['Users']
]);

Or more usefully:

$this->Calendars
        ->find()
        ->contain([
            'Users'
        ])
        ->hydrate(false)
        ->indexBy('Calendars.id')
        ->toArray();

You can contain two or more tables also (only if you correctly associated the tables as written above).

$this->Calendars
        ->find()
        ->contain([
            //You need to associate Products table to Calendars table at first to contain Products table.
            //You can associate the tables in Table files.
            'Users', 'Products'
        ])
        ->hydrate(false)
        ->indexBy('Calendars.id')
        ->toArray();