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.