+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: CI4 with join and pagination (/showthread.php?tid=79948)
CI4 with join and pagination - spreaderman - 08-20-2021
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` DESCThis 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:42RE: CI4 with join and pagination - InsiteFX - 08-21-2021
We need to see the complete model code to be able to help you.
RE: CI4 with join and pagination - spreaderman - 08-21-2021
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;
}
}RE: CI4 with join and pagination - paliz - 08-21-2021
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());
RE: CI4 with join and pagination - spreaderman - 08-21-2021
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/65275626/codeigniter-4-pagination-with-join-tables-showing-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'); ?>