Using Model::paginate() for SQL Join in CodeIgniter 4

From Model, in CodeIgniter 4, we can paginate existing query that we use in current table defined in $table property, eg in model, like the following:

<?php namespace App\Models;

use CodeIgniter\Model;

class ProductModel extends Model
{   
    /**
     * define properties table, returnType, allowedFields, validationRules, etc here
     */

    // ... 
	public function getPaginatedProductData(string $keyword = ''): array
	{
		if ($keyword)
		{
			$this->builder()
				 ->groupStart()
					 ->like('product_code', $keyword)
					 ->orLike('product_name', $keyword)
				 ->groupEnd();
		}

		return [
			'products'  => $this->paginate(),
			'pager'     => $this->pager,
		];
	}
    // ...

}

That’s for paginate rows in same table, how about in SQL Join? We can! For example, we have a use case to get product and price from the following table relation

that can be grabbed with join:

SELECT 
     `product`.`*`, 
     `price`.`price` 
         FROM 
             `product` 
         JOIN 
             `price` 
         ON 
             `product`.`id` = `price`.`product_id` 
         WHERE 
            `price`.`date` = DATE_FORMAT(NOW(),'%Y-%m-%d');

If we want an object representation with entity class, we can create an entity for that:

<?php namespace App\Entities;

use CodeIgniter\Entity;

class ProductWithPrice extends Entity
{
    protected $attributes = [
        'id'           => null,
        'product_code' => null,
        'product_name' => null,
        'price'        => null,
    ];
}

Now, in the model, we can query the join and then paginate:

<?php namespace App\Models;

use App\Entities\ProductWithPrice;
use CodeIgniter\Model;

class ProductModel extends Model
{   
    // ...
	public function getPaginatedProductWithPriceData()
	{
		$this->builder()
			 ->select(["{$this->table}.*", 'price.price'])
			 ->join('price', "{$this->table}.id = price.product_id")
			 ->where("price.date = DATE_FORMAT(NOW(),'%Y-%m-%d')");

		return [
			'productWithPrices'  => $this->asObject(ProductWithPrice::class)
                                         ->paginate(),
			'pager'              => $this->pager,
		];
	}
    // ...
}

That’s it, the paginate() function now will paginate the query join we have with the object entity for the result row.

How to Avoid –stderr When Running phpunit for Functional/Integration Testing

When you do a Functional/Integration test with session and/or header relation. It will force you to use --stderr when running phpunit, or it will got error, eg: you’re testing that on logout when session exists as user, page will be redirected to login page with status code 302, and it got the following error:

$ vendor/bin/phpunit test/Integration/LogoutPageTest.php
PHPUnit 8.5.2 by Sebastian Bergmann and contributors.

Logout Page (AppTest\Integration\LogoutPage)
 ✘ Open logout page as auser redirect to login page
   ┐
   ├ Failed asserting that 500 matches expected 302.
   │
   ╵ /Users/samsonasik/www/mezzio-authentication-with-authorization/test/Integration/LogoutPageTest.php:36
   ┴

Time: 155 ms, Memory: 10.00 MB


FAILURES!
Tests: 1, Assertions: 1, Failures: 1.

You can use --stderr option on running it:

$ vendor/bin/phpunit test/Integration/LogoutPageTest.php --stderr
PHPUnit 8.5.2 by Sebastian Bergmann and contributors.

Logout Page (AppTest\Integration\LogoutPage)
 √ Open logout page as auser redirect to login page

Time: 150 ms, Memory: 8.00 MB

OK (1 test, 2 assertions)

or define stderr=true in phpunit.xml configuration:

<?xml version="1.0" encoding="UTF-8"?>
<phpunit xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="vendor/phpunit/phpunit/phpunit.xsd"
         bootstrap="vendor/autoload.php"
         colors="true"
         testdox="true"
         stderr="true">

    <!-- testsuites, filter, etc config -->   

</phpunit>

Marking all test to be using stderr is a workaround, as not all tests actually require that, eg: unit test doesn’t need that. To avoid it, we can define @runTestsInSeparateProcesses and @preserveGlobalState disabled in the controller class that require that, so, the test class will be like the following:

<?php

declare(strict_types=1);

namespace AppTest\Integration;

use Laminas\Diactoros\ServerRequest;
use Laminas\Diactoros\Uri;
use Mezzio\Authentication\UserInterface;
use PHPUnit\Framework\TestCase;

/**
 * @runTestsInSeparateProcesses
 * @preserveGlobalState disabled
 */
class LogoutPageTest extends TestCase
{
    private $app;

    protected function setUp(): void
    {
        $this->app = AppFactory::create();
    }

    public function testOpenLogoutPageAsAuserRedirectToLoginPage()
    {
        $sessionData                    = [
            'username' => 'samsonasik',
            'roles'    => [
                'user',
            ],
        ];
        $_SESSION[UserInterface::class] = $sessionData;

        $uri           = new Uri('/logout');
        $serverRequest = new ServerRequest([], [], $uri);

        $response = $this->app->handle($serverRequest);
        $this->assertEquals(302, $response->getStatusCode());
        $this->assertEquals('/login', $response->getHeaderLine('Location'));
    }
}

That’s it!