-
spreaderman
Member
-
Posts: 93
Threads: 19
Joined: Jul 2015
Reputation:
0
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
-
spreaderman
Member
-
Posts: 93
Threads: 19
Joined: Jul 2015
Reputation:
0
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;
}
}
-
paliz
Member
-
Posts: 236
Threads: 19
Joined: Oct 2020
Reputation:
1
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
-
spreaderman
Member
-
Posts: 93
Threads: 19
Joined: Jul 2015
Reputation:
0
08-21-2021, 06:57 PM
(This post was last modified: 08-21-2021, 07:06 PM by spreaderman.
Edit Reason: Mistakenly thought was code was still incorrect, however, it was correct and the code works.
)
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'); ?>
|