+- 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: Paginate remove query condition (/showthread.php?tid=79117)
Paginate remove query condition - sfarzoso - 04-22-2021
I've discovered a strange behavior on the query builder, and I don't know if there is an issue or am I doing something wrong.
Essentially I have this query:
PHP Code:
$builder = $this->post
->select('blog_posts.*, users.username as author')
->join('users', 'blog_posts.author_id = users.id', 'left')
->where('type', $type)
->groupStart()
->like('title', $search)
->orLike('content', $search)
->groupEnd()
->orderBy($order, $sort);
as you can see I have applied a "where" with a group condition that contains the "like" operator.
When I execute the query using:
PHP Code:
$posts = $builder->limit($rows, $page)->paginate(1);
I get:
PHP Code:
SELECT `blog_posts`.*, `users`.`username` as `author`
FROM `blog_posts`
LEFT JOIN `users` ON `blog_posts`.`author_id` = `users`.`id`
WHERE `type` = 'post'
AND (
`title` LIKE '%%' ESCAPE '!'
OR `content` LIKE '%%' ESCAPE '!'
)
ORDER BY `title` ASC
LIMIT 1
Until here no problem, but I'm trying to get the next set of data using:
PHP Code:
$hasNext = !empty($builder->limit($rows, $page + 1)->paginate(1));
and the query is completely wrong and different (I'm using the same $builder):
PHP Code:
SELECT *
FROM `blog_posts`
LIMIT 1, 1
if I comment the previous line:
PHP Code:
$posts = $builder->limit($rows, $page)->paginate(1);
the query is correct, why happen this? Should I create a new builder each time?
RE: Paginate remove query condition - InsiteFX - 04-22-2021
PHP Code:
$builder->resetQuery();
Try that.
RE: Paginate remove query condition - iRedds - 04-22-2021
The paginate method is a wrapper for get methods.
When the get method is called, all query data is reset. This is normal.
Yes, you need to create a query each time.
But you can put the query in a separate method and call it.
Like this:
PHP Code:
public function myPostModelMethod()
{
return $this->select()....;
}
$some = $this->post->myPostModelMethod()->like()->paginate();
$some2 = $this->post->myPostModelMethod()->like()->paginate();
RE: Paginate remove query condition - sfarzoso - 04-25-2021
(04-22-2021, 02:11 PM)iRedds Wrote: The paginate method is a wrapper for get methods.
When the get method is called, all query data is reset. This is normal.
Yes, you need to create a query each time.
But you can put the query in a separate method and call it.
Like this:
PHP Code:public function myPostModelMethod()
{
return $this->select()....;
}
$some = $this->post->myPostModelMethod()->like()->paginate();
$some2 = $this->post->myPostModelMethod()->like()->paginate();
Thanks! Do you know how to execute a query with IS NOT NULL condition using the codeigniter 4 query builder?
RE: Paginate remove query condition - iRedds - 04-26-2021
(04-25-2021, 06:49 AM)sfarzoso Wrote: Thanks! Do you know how to execute a query with IS NOT NULL condition using the codeigniter 4 query builder?
PHP Code:
->where('field IS NOT NULL')