how to combine query() and limit() methods in CodeIgniter

how to combine query() and limit() methods in CodeIgniter

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Why limit() Doesn't Work With Raw SQL in CodeIgniter's query() Method

In CodeIgniter, mixing raw SQL execution with database helper methods often leads to unexpected behavior. A common stumbling block occurs when you try to append a limit() call to the $this->db object after constructing a complex string for the query() method. If you've ever written code similar to the example below and watched your profiler show no LIMIT clause in the final SQL, you are encountering a fundamental architectural limitation of how the Active Record layer processes raw queries.

$query = "(SELECT cat.id, cat.title FROM bf_categories cat WHERE cat.id=" . $category_id;
$this->db->limit(2, 4); // This does nothing for raw queries
$records = $this->db->query($query);
return $records->result();

The reason this fails is that limit() is not a magic filter applied to every database call. It is a setter that modifies the internal state of the DB object specifically for methods like get(), find(), or select(). When you bypass these helpers and use query(), you are handing the database engine a complete, self-contained SQL string. The engine executes exactly what is written in that string; it does not look at $this->db->limit() to append clauses.

Handling Limits with Raw Queries

To achieve pagination or row restrictions when using query(), you must inject the LIMIT clause directly into your SQL string. This requires careful syntax management, especially if you are building dynamic queries.

If you need to accept variables for limit and offset, simply concatenating them into the query string is possible but risky due to SQL injection vulnerabilities. The safer approach is to bind parameters, though standard parameter binding in CodeIgniter's query() method only works for values inside the SQL string, not for structural clauses like LIMIT.

To safely handle this while using query(), you have two primary options:

  1. Manual String Construction (Use with Caution): If inputs are strictly validated integers, you can format the limit clause directly.

    $sql = "SELECT * FROM bf_categories WHERE id = ? LIMIT ?";
    
    // You cannot bind a LIMIT value via parameters in CodeIgniter's query() method 
    // because the LIMIT clause is structural, not a data value in the SELECT list.
    // You must construct the string carefully or use a placeholder strategy.
    
  2. Passing Parameters to query() for Data Only: As seen in your second code snippet attempt, you can pass an array of parameters to handle data values (like IDs), but the limit clause remains outside that scope:

    $sql = "SELECT cat.title FROM bf_categories WHERE cat.parent_id = ?";
    // The LIMIT must be appended manually if using query() with raw SQL strings.
    $sql .= " LIMIT 0, 5"; 
    

Dealing with UNION and Complex Logic

Your specific scenario involves a UNION operation to combine categories and products. This adds complexity because the logic for limiting results changes based on whether you want to limit the total result set or the individual parts of the union.

In your updated model function, you attempted to pass $limit and $offset to the method but found that CodeIgniter generated two separate SELECT statements in the profiler. This happens because query() returns a database result object based on the string you provided. If you want to filter the combined result set of a UNION, the LIMIT clause must appear at the very end of the entire query string, after the closing parenthesis of the union operation.

Here is how you should structure that specific logic:

public function get_categories_and_products($limit = 5, $offset = 0, $category_id = null)
{
    // Build the first part of the UNION
    $part1 = "(SELECT cat.category_id, cat.title, cat.image_thumb, 
                  cat.deleted, cat.display_weight 
               FROM bf_categories cat 
               WHERE cat.parent_id=" . (int)$category_id . " 
                 AND cat.category_id <> " . (int)$category_id;

    // Build the second part
    $part2 = "(SELECT p.product_id, p.name, p.image_thumb, p.deleted, p.display_weight
               FROM bf_product p 
               INNER JOIN bf_product_category cp ON p.product_id = cp.product_id
               WHERE cp.category_id=" . (int)$category_id;

    // Combine them with UNION
    $sql = $part1 . ") UNION (" . $part2 . ")";

    // Append the LIMIT clause to the END of the SQL string
    // Syntax: LIMIT offset, count
    $sql .= " ORDER BY display_weight DESC LIMIT " . (int)$offset . ", " . (int)$limit;

    $records = $this->db->query($sql);
    return $records->result();
}

The Trade-Off of Raw SQL

While using query() gives you the flexibility to handle complex logic like UNION combined with specific ordering and limiting, it bypasses the safety checks and caching mechanisms built into CodeIgniter's Active Record. You lose the ability to chain methods like where(), join(), or limit() easily before execution.

If your query becomes significantly more complex than a simple SELECT *, you might reconsider whether the effort of managing raw strings is worth it compared to using database views, stored procedures, or perhaps switching to a modern framework like Laravel (https://laravelcompany.com), which offers robust query builder methods that handle limits and joins much more intuitively out of the box. However, for legacy systems or specific performance requirements where raw SQL is necessary, manually appending the LIMIT clause to your constructed string remains the only reliable path forward.

Our network