Hello,
The original Grocery_crud_model gets the total number of rows by performing the exact same query without the 'LIMIT x' portion
and then counts the number of rows with codeigniter's num_rows() function. For small tables this approach does not
seem noticeable time wise.
I had a table with more than one hundred thousand (100k+) rows and the list would render kind of slow. To improve this,
I made adjustments to Custom_grocery_crud_model from /topic/3620-custom-queries-with-pagination-sorting-and-searching/
I added to the model 'SELECT COUNT(primary_key) FROM (' + original query + ')' for the get_total_results function, this increased time of rendering by about 4x.
Example in controller:
public function my_table()
{
$crud = new grocery_CRUD();
$crud->set_model('custom_grocery_crud_model');
$crud->set_table('my_table');
$crud->columns('col1', 'col2', 'col3');
$crud->basic_model->set_custom_query('
SELECT primary_key, col1, col2, another_table.col3
FROM my_table
LEFT JOIN another_table ON (another_table.id = my_table.id)
', 'GROUP BY my_table.col1', $query_binding=array());
// for searching with WHERE clause on col3
$crud->basic_model->set_column_table(array(
'col3' => 'another_table',
));
// for making the second query of getting the row count faster
// The alias for COUNT has to be num_rows as that is the name used in the model.
$crud->basic_model->set_header_footer_sql('
SELECT COUNT(a.primary_key) as num_rows
FROM
(
', ') a');
$output = $crud->render();
$this->load->view('my_view.php', $output);
}
I also added the set_column_table() function for searching on those columns from other tables by WHERE, and there is a query binding argument to the custom query if needed.
I attached the model file.
I hope this helps.