Search code examples
phpsqlmysqlcodeigniterpagination

Codeigniter get number of rows of complex query

I have a complex query with multiple inner joins in it. The query is written in pure SQL. I don't want to convert it to Active Record. For paging, I need to know the total number of rows that query will return but I don't want to execute it. I tried:

$this->db->count_all_results($query);

but it returns nothing. Is there any other way to get count of rows from a complex query written in SQL ?

Here is the query:

SELECT o.id, o.heading, o.description, p.product_name, s.company_name
            FROM ci_offer o 
            INNER JOIN ci_products p ON o.product_id = p.id
            INNER JOIN ci_suppliers s ON p.supplier_id = s.id
            ORDER BY o.modified DESC
Solution

  • If you want to use count_all_results you'll need to use ActiveRecord.

    But you can also replace the columns in the SELECT clause with

    COUNT(1) AS total