Code Igniter 4 Default pagination using Model Class has no order by and Join

Question

$postModel = new \App\Models\PostModel();
$pager = \Config\Services::pager();
$post = $postModel->orderBy('dteCreatedDate', 'DESC')->findAll();
$data = [
        'post' => $postModel->paginate(2,'post'),
        'pager' => $postModel->pager
     ];

I have the code above to create a simpleLink pagination in my code igniter 4 project. This pagination is working but it is lacking 1 information and an order by to the result.

I need to select a column from another table that connects to the table in PostModel. How to add a join and order by to the $postModel so that I can get all the data I need and have an order by on the result set.

the result being feed to the pagination() of the Model Class is paginated by default that is why I wanted to use this function

How to add a join and order by to Model Class Default CRUD

Answer 1

It appears that the paginate works directly upon the table declared in the model i.e what is declared in protected $table = 'table_name'.

So I was thinking, If you need to use a JOIN on one or more tables and some other stuff, "a way" around this, is to create a VIEW table.

I had a play with this and came up with some working code. It's rather trivial but it appears to prove the point.

I have two tables. (very loosely based upon Cluedo and lack of sleep)

Table 1

table_1
   id,
   name,
   type

with inserted data of
1, Fred, Baker
2, Sam , Candle Stick Maker

Table 2

table_2
   id,
   place

with inserted data of
1, Laundry
2, Bathroom

In the PostModel I have

protected $table = 'table_view';

/**
 * Only need to create the View so the pagination can access it via
 * $this->table (protected $table = 'table_view')
 */
public function create_view() {
    $sql = "CREATE OR REPLACE VIEW table_view AS ";
    // Whatever your SQL needs to be goes here
    $sql .= "SELECT t1.name, t1.type, t2.place FROM table_2 t2 
        JOIN table_1 t1 on t1.id = t2.table_1_id";
    echo $sql;
    $query = $this->db->query($sql);
}

Then your Paginate method could become

public function index() {
    $postModel = new \App\Models\PostModel();
    $postModel->create_view();
    $pager = \Config\Services::pager();
    $data = [
        'posts' => $postModel->paginate(2),
        'pager' => $postModel->pager
    ];

    echo view('users_view', $data);
}

My View is

<h2> The results</h2>

<?php
echo '<pre>';
echo 'LINE: ' . __LINE__ . ' Module ' . __CLASS__ . '<br>';
var_dump($posts);
echo '</pre>';
?>

<table>
    <?php foreach ($posts as $post): ?>
        <tr>
            <td><?= $post['name']; ?></td>
            <td><?= $post['type']; ?></td>
            <td><?= $post['place']; ?></td>
        </tr>
    <?php endforeach; ?>
</table>

Which gives the output of ( and I have not included the pagination but I did test it)

CREATE OR REPLACE VIEW table_view AS SELECT t1.name, t1.type, t2.place FROM table_2 t2 JOIN table_1 t1 on t1.id = t2.table_1_id
The results
LINE: 10 Module 
array(2) {
  [0]=>
  array(3) {
    ["name"]=>
    string(4) "Fred"
    ["type"]=>
    string(5) "Baker"
    ["place"]=>
    string(7) "Laundry"
  }
  [1]=>
  array(3) {
    ["name"]=>
    string(3) "Sam"
    ["type"]=>
    string(18) "Candle Stick Maker"
    ["place"]=>
    string(8) "Bathroom"
  }
}
Fred    Baker   Laundry
Sam Candle Stick Maker  Bathroom

It "appears" that the table name, in this case being a non existent table (until it is created) doesn't upset the Model's $table being set to a non existent table.

Summary

1. Create the Model

2. Declare $table to be your view name.

3. Create a method that creates (or replace) the view. That gets around the 1st time to create it and subsequent updates.

4. Call the Model method that creates the view

5. Use the Model in your pagination. (It now points at the View).

It's probably not the best of ideas, but it kind of makes it fit with the way it wants to work.

I had tried the "standard" ways, but they didn't want to play nice. I also used the most basic of CI SQL functionality, you can use the builder etc if you so desire.

I hope that gives you some ideas.