Welcome Guest, Not a member yet? Register   Sign In
Paginate remove query condition
#1

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 `usersON `blog_posts`.`author_id` = `users`.`id`
WHERE `type` = 'post'
AND   (
`
titleLIKE '%%' ESCAPE '!'
OR  `contentLIKE '%%' ESCAPE '!'
 
)
ORDER BY `titleASC
 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

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?
Reply
#2

(This post was last modified: 04-22-2021, 11:17 AM by InsiteFX.)

PHP Code:
$builder->resetQuery(); 

Try that.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#3

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(); 
Reply
#4

(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?
Reply
#5

(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'
Reply




Theme © iAndrew 2016 - Forum software by © MyBB