⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Custom Query Pagination Problem - Solution



marlaaragao
  • profile picture
  • Member

Posted 30 July 2015 - 14:24 PM

Hello!

 

I followed this topic: /topic/1963-simple-guide-to-executing-custom-queries/

to learn how to use custom queries. It's great! 

 

The problem is that the results did not respect pagination, so even when you have max 10 lines per page, it shows all the results. After some struggling, I managed to get the pagination working adding the 'limit' and 'get_total_results' functions. So if anyone is having this problem, the custom_model gets like this:

 
class Custom_Query_model extends grocery_CRUD_Model {

    private $query_str = '';

    function __construct() {
        parent::__construct();

    }

    function get_list() {
        
        $query = $this->db->query($this->query_str);

        $results_array = $query->result();
        return $results_array;
    }

    public function set_custom_query($query_str) {
        $this->query_str = $query_str;
    }

    /* Adding this does the trick for limiting the results per page! */
    function limit($value, $offset = ''){
        $this->query_str .= ' LIMIT '.($offset ? $offset.', ' : '').$value;
    }

    /* This method is to count the results of your query. If you don't add this
     * the total results on 'Displaying 1 to n of x items' can be wrong, 'cause 
     * grocery will get the total amount of rows from the table */
    function get_total_results() {
        return count($this->get_list());
    }

I tested in some of my tables that use custom query, and it's all good, but if anyone has other solution or find some error, please let me know. 

 

PS.: I'm creating new topic because the other one is a little old and it's not about pagination. Thank you very much!


michaelh613
  • profile picture
  • Member

Posted 11 August 2015 - 14:41 PM

I see how pagination seems to work but filtering seems to be a problem.  Have you worked on that?


marlaaragao
  • profile picture
  • Member

Posted 09 September 2015 - 01:07 AM

I see how pagination seems to work but filtering seems to be a problem.  Have you worked on that?

 

Hello! Unfortunetly, I could not find a solution.. Sorry for the late response, but I was so busy at work, I kinda let the topic die. I can't find a solution, and I'm not with a lot of time to try to find it (knowing that I may not even get it). The search problem is more complicated. So, I hope someone can find it and share with us.. For now I'll take off the search boxes on the custom queries views.. Ugly (but that's what I've got for today, hehe).

 

Thank you!