CI4 with join and pagination

I have joined three tables but my pagination only gives me my post table fields and not fields from the other 2 tables. I can see in the debugging bar that the proper sql is being executed. I can run that sql directly in mysql and the expected results are returned (eg fields from all three tables). Why are the other fields from the other tables now available please?

Here is my controller;

Code:

function index(){
        $post_category_id = null; // null means all categories
        $category_is_published = 1; // 1 means only published articles
        $post_is_published = 1;  // 1 means only published articles
        $pagination = 1; // 1 means to the model to send back an object for pagination
        $articles_per_page = 6;
        $this->data['posts']    = $this->PostModel->getPostsByCategory($post_category_id, $category_is_published, $post_is_published, $pagination )->paginate($articles_per_page, 'bootstrap');
        $this->data['pager']    = $this->PostModel->pager;
        return view('Post/post_index', $this->data);
    }

This is the sql that is produced;

Code:

SELECT `post`.`post_id`, `post`.`post_category_id`, `post`.`post_title`, `post`.`post_slug`, `post`.`post_body`, `post`.`post_is_published`, `post`.`post_updated_at`, `post`.`post_created_at`, `post`.`post_user_id`, `post`.`post_image`, `user`.`name`, `user`.`email`, `category`.`category_id`, `category`.`category_user_id`, `category`.`category_name`, `category`.`category_is_published`, `category`.`category_created_at`, `category`.`category_updated_at` FROM `post` JOIN `category` ON `category`.`category_id` = `post`.`post_category_id` JOIN `user` ON `user`.`id` = `post`.`post_user_id` WHERE `post`.`post_is_published` = 1 AND `category`.`category_is_published` = 1 ORDER BY `post`.`post_updated_at` DESC

This is the view output;

Code:

[post_id] => 264
                    [post_category_id] => 93
                    [post_user_id] => 53
                    [post_title] => This is the last post
                    [post_slug] =>
                    [post_body] => some text
                   [post_is_published] => 1
                    [post_image] => IMG_20210420_151153_copy_1600x1200.jpg
                    [post_created_at] => 2021-06-05 10:19:13
                    [post_updated_at] => 2021-08-16 05:03:42

We need to see the complete model code to be able to help you.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Thank you InsiteFx. Here is my model

Code:

namespace App\Models;

class PostModel extends \CodeIgniter\Model {
    
    protected $table      = 'post';
    protected $primaryKey = 'post_id';

    //protected $useAutoIncrement = true;

    protected $returnType     = 'App\Entities\PostEntity';
    //protected $useSoftDeletes = true;

    protected $allowedFields = [
                                    'post_id',
                                    'post_category_id',
                                    'post_user_id',
                                    'post_title',
                                    'post_slug',
                                    'post_body',
                                    'post_is_published',
                                    'post_image',
                                ];

    protected $useTimestamps    = true;
    protected $createdField     = 'post_created_at';
    protected $updatedField     = 'post_updated_at';
    //protected $deletedField  = 'deleted_at';

    protected $validationRules    = [
            'post_category_id'  => 'required',
            'post_title'        => 'required|is_unique[post.post_title,post_title,{post_title}]|min_length[5]|max_length[255]',
            'post_body'         => 'required|min_length[5]'
        ];
    
    protected $validationMessages = [
        'post_category_id' => [
            'required' => 'The Category is required',
            ],
        'post_title' => [
            'required'      => 'The title name is required',
            'is_unique'     => 'That title already exists',
            'min_length'    => 'The minimum length of a title is 5 characters',
            'max_length'    => 'The maximum length is 255 characters'
            ],
          'post_body' => [
              'required' => 'The body of a post is required.',
               'min_length' => 'The minimum length of the body is 5 characters',
             ],
        ];
    //protected $skipValidation     = false;


    function getPostsByCategory($post_category_id = null, $category_is_published = 1, $post_is_published = 1, $pagination = 0 ) {
        $builder = $this->builder('post');
        $builder->select(
            '
            post.post_id,
            post.post_category_id,
            post.post_title,
            post.post_slug,
            post.post_body,
            post.post_is_published,
            post.post_updated_at,
            post.post_created_at,
            post.post_user_id,
            post.post_image,
            user.name,
            user.email,
            category.category_id,
            category.category_user_id,
            category.category_name,
            category.category_is_published,
            category.category_created_at,
            category.category_updated_at
            ');
        $builder->join('category', 'category.category_id = post.post_category_id');
        $builder->join('user', 'user.id = post.post_user_id');
        $builder->orderBy('post.post_updated_at', 'DESC');
        if (!empty($post_category_id)) {
            $builder->where('post.post_category_id', $post_category_id);
        }
        $builder->where('post.post_is_published', $post_is_published);
        $builder->where('category.category_is_published', $category_is_published);
        // there will be many so return an array
        $query = $builder->get();
        $result = $query->getResult();
        if ($pagination == 1) {
            // see this https://forum.codeigniter.com/thread-77644.html
            return $this;
        } else {
            return $result;
        }
    }

PHP Code:

result = $newsPostModel->select('news_post.*,news_category.name as category,news_category.language ,news_sub_category.name as subCategory,users.username ,users.first_name as firstName, users.last_name as lastName')
            ->join('news_category', 'news_category.id = news_post.category_id', 'left')
            ->join('news_sub_category', 'news_sub_category.id = news_post.sub_category_id', 'left')
            ->join('users', 'users.id = news_post.user_id', 'left')
            
            ->where(['id'=>1])
        
            ->paginate($this->urlQueryParam->getLimit(), 'default', $this->urlQueryParam->getPage(), $this->urlQueryParam->getOffset());
      


Enlightenment  Is  Freedom

Thank you for the code snippet. I realized I was not making a model object properly. I read a bit more and found this link;  https://stackoverflow.com/questions/6527...wing-error  It says pagination only works on model objects (far above I was creating a builder object).  I have revised my model and controller per below.  It is now working. Thank you.

Code:

    function index(){
        $this->data['posts'] = $this->PostModel->paginatePosts();
        $this->data['pager']    = $this->PostModel->pager;
    return view('Post/post_index', $this->data);
    }

and this is my new method in the model;

Code:

    function paginatePosts($post_category_id = null, $category_is_published = 1, $post_is_published = 1, $articles_per_page =6 ) {
       
        return $this->select('
            post.post_id,
            post.post_category_id,
            post.post_title,
            post.post_slug,
            post.post_body,
            post.post_is_published,
            post.post_updated_at,
            post.post_created_at,
            post.post_user_id,
            post.post_image,
            user.name,
            user.email,
            category.category_id,
            category.category_user_id,
            category.category_name,
            category.category_is_published,
            category.category_created_at,
            category.category_updated_at')
            ->join('category', 'category.category_id = post.post_category_id')
            ->join('user', 'user.id = post.post_user_id')
            ->orderBy('post.post_updated_at', 'DESC')
            ->where('post.post_is_published', $post_is_published)
            ->where('category.category_is_published', $category_is_published)
            ->where('post.post_is_published', $post_is_published)
            ->where('category.category_is_published', $category_is_published)
            ->paginate($articles_per_page, 'bootstrap');
    }

and there is my call to echo links to the view;

Code:

<?= $pager->links('bootstrap', 'bootstrap4_pagination'); ?>