Error: Call to undefined method CodeIgniter\Database\MySQLi\Result::paginate()

Question

Struggling to wrap my head around paginate in CI4. All examples in the user guide shows the ->paginate() method linked directly onto the $model variable (without arguments) in the Controller.

I have all my queries in the Model, so I'm a bit confused if the paginate method goes onto the end of my query there, or where it's called in the Controller. I've tried both - neither work.

Model:

    public function brand_name($brand_name_slug)
    {
        return $this
                    ->db
                    ->table('shop a')
                    ->select()
                    ->join('(SELECT sku, MIN(sale_price) AS sale_price FROM shop GROUP BY sku) AS b', 'a.sku = b.sku AND a.sale_price = b.sale_price')
                    ->where('availability', 'in stock')
                    ->where('a.sku !=', '')
                    ->where('brand_name_slug', $brand_name_slug)
                    ->groupBy('a.sku')
                    ->orderBy('brand_name, subbrand_name, product, size, unit')
                    ->get()
                    ->paginate(10) // not working
                    ->getResult();
    }

It returns the error: Call to undefined method CodeIgniter\Database\MySQLi\Result::paginate()

My Controller:

class Shop extends Controller
{

    public function brand_name($brand_name_slug)
    {
        $model = new ShopModel();

        $data = [
            'category_menu' => $model->category_menu(),
            'brand_menu' => $model->brand_menu(),
            'nav' => $model->nav(),
            'subnav' => $model->subnav(),
            'shop' => $model->brand_name($brand_name_slug)->paginate(10), // also doesn't work
            'pager' => $model->pager
        ];

        if (empty($data['shop']))
        {
            throw new \CodeIgniter\Exceptions\PageNotFoundException('Cannot find the news item: '. $slug);
        }

        echo view('templates/header', $data);
        echo view('shop/view', $data);
        echo view('templates/footer', $data);
    }

Returns the error: Call to a member function paginate() on array

Can anyone give me any pointers to navigating around this?

Answer 1

You got to use pagination() function without get() or getResult() and note that it is a function from the Model class your custom model extend from. So you should call it directly from the model, not from $this->db (this is why he doesn't know it).

This should be the function in your model :

public function brand_name($brand_name_slug)
    {
        return $this
                    ->table('shop a')
                    ->select()
                    ->join('(SELECT sku, MIN(sale_price) AS sale_price FROM shop GROUP BY sku) AS b', 'a.sku = b.sku AND a.sale_price = b.sale_price')
                    ->where('availability', 'in stock')
                    ->where('a.sku !=', '')
                    ->where('brand_name_slug', $brand_name_slug)
                    ->groupBy('a.sku')
                    ->orderBy('brand_name, subbrand_name, product, size, unit')
                    ->paginate(10);
    }

And $data in your controller :

$data = [
            'category_menu' => $model->category_menu(),
            'brand_menu' => $model->brand_menu(),
            'nav' => $model->nav(),
            'subnav' => $model->subnav(),
            'shop' => $model->brand_name($brand_name_slug),
            'pager' => $model->pager
        ];