CakePHP-4-How-To-Work-with-MySQL-Left-Join-Tutorial

CakePHP 4 How To Work with MySQL Left Join Tutorial

by
Reading Time: 9 minutes
2,693 Views

Inside this article we will see CakePHP 4 How to work with Left Join. When we work with MySQL Queries, then definitely for some relational data we need to work with Joins. Inside this article we will see the concept of Left Join in CakePHP 4.

Joins in CakePHP 4 is the connection between one and more tables to get data. In MySQL we have Inner join, Left join, Right join. We will see the concept of Left Join in this CakePHP 4 article.

Learn More –

Let’s get started.

What is Left Join?

Left join works same the matched condition between two or more than two tables. But in this case we also get the rows of left table which doesn’t match with the condition with the right hand sided table. Means we get all rows of left table including values of matched with right table.

How can we use inside an application and get relational data we will see by making an application. Let’s create an application in which we use Left Join.

CakePHP 4 Installation

To create a CakePHP project, run this command into your shell or terminal. Make sure composer should be installed in your system.

$ composer create-project --prefer-dist cakephp/app:~4.0 mycakephp

Above command will creates a project with the name called mycakephp.

Create Database

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE mydatabase;

Successfully, we have created a database.

Database Connection

Open app_local.php file from /config folder. Search for Datasources. Go to default array of it.

You can add your connection details here to connect with your database. It will be like this –

//...

'Datasources' => [
        'default' => [
            'host' => 'localhost',
            /*
             * CakePHP will use the default DB port based on the driver selected
             * MySQL on MAMP uses port 8889, MAMP users will want to uncomment
             * the following line and set the port accordingly
             */
            //'port' => 'non_standard_port_number',

            'username' => 'root',
            'password' => 'sample@123',

            'database' => 'mydatabase',
            /*
             * If not using the default 'public' schema with the PostgreSQL driver
             * set it here.
             */
            //'schema' => 'myapp',

            /*
             * You can use a DSN string to set the entire configuration
             */
            'url' => env('DATABASE_URL', null),
        ],
  
     //...

//...

You can pass host, username, password and database.

Successfully, you are now connected with the database.

Create Migrations

Open project into terminal run these migrations command.

$ bin/cake bake migration CreateAuthors

Above command will create a file 20220317154654_CreateAuthors.php inside /config/Migrations folder.

$ bin/cake bake migration CreateBooks

Above command will create a file 20220317154654_CreateBooks.php inside /config/Migrations folder.

Open 20220317154654_CreateAuthors.php and write this following into it. It will create authors table into database.

<?php

declare(strict_types=1);

use Migrations\AbstractMigration;

class CreateAuthors extends AbstractMigration
{
    public function change()
    {
        $table = $this->table('authors');
        $table->addColumn("name", "string", [
            "limit" => 50,
            "null" => false
        ]);
        $table->addColumn("email", "string", [
            "limit" => 50,
            "null" => false
        ]);
        $table->addColumn("created_at", "timestamp", [
            "default" => "CURRENT_TIMESTAMP",
            "null" => false
        ]);
        $table->create();
    }
}

Open 20220317154654_CreateBooks.php and write this following code into it. This will create books table into table.

<?php

declare(strict_types=1);

use Migrations\AbstractMigration;

class CreateBooks extends AbstractMigration
{
    public function change()
    {
        $table = $this->table('books');
        $table->addColumn("author_id", "integer", [
            "limit" => 5,
            "null" => false
        ]);
        $table->addColumn("name", "string", [
            "limit" => 120,
            "null" => false
        ]);
        $table->addColumn("description", "text", [
            "null" => true
        ]);
        $table->addColumn("cost", "integer", [
            "limit" => 5,
            "null" => false
        ]);
        $table->addColumn("created_at", "timestamp", [
            "default" => "CURRENT_TIMESTAMP",
            "null" => false
        ]);
        $table->create();
    }
}

Run Migrations

Run all migrations to create database tables.

$ bin/cake migrations migrate

Table: authors

Table: books

MySQL Fake Data

Let’s insert some fake data into these tables.

Fake data for authors table:

--
-- Dumping data for table `authors`
--

INSERT INTO `authors` (`id`, `name`, `email`, `created_at`) VALUES
(1, 'Sanjay Kumar', 'sanjay@gmail.com', '2022-03-17 15:53:10'),
(2, 'Vijay Kumar', 'vijay@gmail.com', '2022-03-17 15:53:18'),
(3, 'Dhananjay Kumar', 'dj@gmail.com', '2022-03-17 15:53:32');

Fake data for books table:

--
-- Dumping data for table `books`
--

INSERT INTO `books` (`id`, `author_id`, `name`, `description`, `cost`, `created_at`) VALUES
(1, 1, 'Book 1', 'Sample content', 100, '2022-03-17 15:54:02'),
(2, 1, 'Book 2', 'Sample content', 75, '2022-03-17 15:55:02'),
(3, 2, 'Book 3', 'Sample content', 115, '2022-03-17 15:55:23'),
(4, 3, 'Book 4', 'Sample content', 150, '2022-03-17 15:55:37'),
(5, 4, 'Book 5', 'Sample content', 120, '2022-03-17 15:55:37'),
(6, 5, 'Book 6', 'Sample content', 180, '2022-03-17 15:55:37');

Execute these mysql queries to your database. It will insert few fake rows into both tables.

Create Model & Entity

We need to create models for both tables – authors & books. 

$ bin/cake bake model Authors --no-rules --no-validation

$ bin/cake bake model Books --no-rules --no-validation

It will create files for models and entities.

Open AuthorsTable.php file from /src/Model/Table folder. Write this following line of code into it.

<?php
declare(strict_types=1);

namespace App\Model\Table;

use Cake\ORM\Table;

class AuthorsTable extends Table
{
    public function initialize(array $config): void
    {
        parent::initialize($config);

        $this->setTable('authors');
        $this->setPrimaryKey('id');

        $this->hasMany('Books', [
            'foreignKey' => 'author_id',
        ]);
    }
}

Open BooksTable.php file from /src/Model/Table folder. Write this following line of code into it.

<?php
declare(strict_types=1);

namespace App\Model\Table;

use Cake\ORM\Table;

class BooksTable extends Table
{
    public function initialize(array $config): void
    {
        parent::initialize($config);

        $this->setTable('books');
        $this->setPrimaryKey('id');

        $this->belongsTo('Authors', [
            'foreignKey' => 'author_id',
            'joinType' => 'LEFT',
        ]);
    }
}

We can clearly see that author_id acts as the relational column between authors and books table. We will use this column for left join. Assuming books is the left hand side table and authors be right hand side.

Create Controller

Again back to terminal run this bake console command to create controller file. 

$ bin/cake bake controller Reader --no-actions

It will create a file ReaderController.php inside /src/Controller folder. Open controller file and write this code into it. In this code we used the concept of Left join to fetch the relational data between books and authors table.

<?php

declare(strict_types=1);

namespace App\Controller;

class ReaderController extends AppController
{
    private $bookTable;
    public function initialize(): void
    {
        parent::initialize();
        // Books is not table, it is table class name
        // Creating a table object
        $this->bookTable = $this->getTableLocator()->get("Books");
        $this->autoRender = false;
    }

    public function loadBookDetails()
    {
        $query = $this->bookTable->find();

        $data = $query->select([
            "Books.name",
            "Books.description",
            "Books.cost",
            "a.name",
            "a.email"
        ])->join([
            "table" => "authors",
            "alias" => "a",
            "type" => "LEFT",
            "conditions" => "Books.author_id = a.id"
        ])->toList();
      
        // print_r($data);

        if (count($data) > 0) {
            foreach ($data as $book) {
                echo "Book name: " . $book->name . ", Author name: " . $book->a['name'] . "<br/>";
            }
        }
    }
}

Create Route

Open routes.php file from /config folder. Add this route into it.

//...

$routes->connect(
    '/books',
    ['controller' => 'Reader', 'action' => 'loadBookDetails']
);

//...

Application Testing

To execute start development server.

$ bin/cake server

URL: http://localhost:8765/books

When we print all the data using commented line of code print_r($data); We will get this output.

When we loop through each data of the array.

We hope this article helped you to learn CakePHP 4 How To Work with MySQL Left Join Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.