How to create pagination links when combining data from two different queries

Question

I have a controller with a method that looks something like this:

    public function browsecategory($category_id)
    {               

        //find any subcategories for this category
        $this->load->model('category/category_model');
                    $this->load->model('category/product_category_model');  
        $records['categories'] = $this->category_model->find_all_by('parent_id', $category_id);

                    //add some product data too. 
                    $records['products'] = $this->product_category_model->find_all_by('category_id', $category_id); 
        Template::set('records', $records);
        Template::render();
    }//end browsecategory

All the examples I've seen for the codeigniter pagination "stuff" is using one query. I need to combine two data sets and serve on one view.

Any suggestions?

EDIT 1

I've tried to follow MDeSilva's suggestion below. And although the pagination object is correctly calculating the number of links to create, all items appear on all pages.

Here's the code in the model that gets the data:

public function get_categories_and_products($limit=12, $offset=0, $category_id=null)
{
    print '<BR>the function got the following offeset:'.$offset;
    $query = "(SELECT cat.category_id, cat.title, cat.image_thumb, cat.deleted, cat.display_weight ";
    $query = $query."FROM bf_categories cat ";
    $query = $query."WHERE cat.parent_id=".$category_id;
    $query = $query." AND cat.category_id <>".$category_id;
    $query = $query.") UNION (";
    $query = $query."SELECT p.product_id, p.name, p.image_thumb, p.deleted , p.display_weight";
    $query = $query." FROM bf_product p ";
    $query = $query."Inner join bf_product_category cp ";
    $query = $query."on p.product_id=cp.product_id ";
    $query = $query."Where cp.category_id=".$category_id.")";

    $this->db->limit($limit, $offset);
    $catsandprods= $this->db->query($query);
    return $catsandprods->result() ;
}

And here's the code in the controller:

public function browsecategory($category_id, $offset=0)
    {
            $this->load->library('pagination');

            $total = $this->product_model->get_cats_prods_count($category_id);

            $config['base_url'] = site_url('/product/browsecategory/'.$category_id);
            $config['uri_segment'] = 4;
            $config['total_rows'] = $total;
            $config['per_page'] = 5;
            $config['num_links'] = 10;

            $this->pagination->initialize($config);
            $offset = ($this->uri->segment($config['uri_segment'])) ? $this->uri->segment($config['uri_segment']) : 0;
                        print $offset;
            //Call the model function here to get the result, 
            $records= $this->product_model->get_categories_and_products(5,$offset,$category_id);
            //add to breadcrumb trail
            $this->build_bread_crumb_trail($category_id);
            $breadcrumbs = $this->breadcrumbs->expand_to_hyperlinks();

            Template::set('currentcategory',$category_id);
            Template::set('breadcrumbs', $breadcrumbs);
            Template::set('records', $records);
            Template::render();     
    }

I've debugged and I can see that the line of code "$this->db->limit($limit, $offset);" in the model is not working. It always returns the full record set... Can you tell me what I'm missing? Thanks.

Answer 1

This is the way to generate pagination links in CI, for your requirement have a query with a join,

public function index($offset = 0) {
   $language_id = 1;

    $artwork_id = null;

    if ($_SERVER['REQUEST_METHOD'] == 'POST')
    {
        $artwork_id = $this->input->post('serach_artwork_id', TRUE) ? $this->input->post('serach_artwork_id', TRUE) : null;
        $data['artwork_id'] = $artwork_id;

    }

    $this->load->library('pagination');
    $limit = 10;

    $total = $this->Artwork_model->get_artwork_count($language_id, $artwork_id);

    $config['base_url'] = base_url().'artwork/index/';
    $config['total_rows'] = $total;
    $config['per_page'] = $limit;
    $config['uri_segment'] = 3;

    $config['first_link'] = '<< First';
    $config['last_link'] = 'Last >>';
    $config['next_link'] = 'Next ' . '&gt;';
    $config['prev_link'] = '&lt;' . ' Previous';
    $config['num_tag_open'] = '<span class="number">';
    $config['num_tag_close'] = '</span>';

    $config['cur_tag_open'] = '<span class="current"><a href="#">';
    $config['cur_tag_close'] = '</a></span>';

    $this->pagination->initialize($config);

    //Call the model function here to get the result, 
    $data['artworks'] = $this->Artwork_model->get_artworks($language_id, $limit, $offset, $artwork_id);

    $this->template->write('title', 'Artwork : Manage Artwork');
    $this->template->write_view('content', 'artwork/index', $data);
    $this->template->render();
}

Here is an example for query with multiple joins in the model

public function get_artworks($language_id = 1, $limit = 10, $offset = 0, $arwork_id = null)
    {        
        $this->db->select('a.id, a.price, a.is_shop, at.title,at.status,at.date_added,ats.name as artist_name');
        $this->db->from('artworks a');
        $this->db->join('artwork_translations at', 'a.id = at.artwork_id');
        $this->db->join('artists ats', 'a.artist_id = ats.id');
        $this->db->where('at.language_id', $language_id);
        if(!is_null($arwork_id) && !empty($arwork_id) && $arwork_id != 'all')
        {
            $this->db->where('a.id =', $arwork_id);
        }
        $this->db->order_by('a.id DESC');
        $this->db->limit($limit, $offset);

        $artworks = $this->db->get();

        return $artworks->result();
    }

In the View

<?= $this->pagination->create_links(); ?>

Answer 2

Simply use PHP function array_merge

<?php $beginning = 'foo'; $end = array(1 => 'bar');
$result = array_merge((array)$beginning, (array)$end);
$this->load->view('view.php', $result);
?>

and extract according to keys for array used. It is really simple & working

Answer 3

The pagination class doesn't care about the how the data source is constructed, just so you hand it the data result object it wants. So you would just pass limits & offsets into your data queries, or else pull all your data and slice it up afterwards.

However, I don't really understand how you are thinking to combine these different bits of data into a single result to display - are you trying to display all categories, then paginate the products? If so, you are set up incorrectly