Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

CodeIgniter query builder: Unknown column when WHERE uses a SELECT alias

ErrorUnknown column 'cnt' in 'where clause'

Short answer

The error comes from SQL, not from CodeIgniter: a WHERE clause cannot refer to an alias created in the select list, because WHERE is evaluated before that value exists. Repeat the expression inside where(), or for an aggregate such as COUNT move the condition to having() together with groupBy().

  • Applies to CodeIgniter 3.1, CodeIgniter 4.5, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print the SQL the builder generated (CodeIgniter 4) and see where the alias is used. On CodeIgniter 3, read the last query from the database error page instead.

    echo $builder->getCompiledSelect();
  2. 2Match what you see, then jump to its fix

    1. 1Unknown column 'full_price' in 'where clause' and full_price is defined as price * qty AS full_priceA calculated column alias is used in where()Fix
    2. 2Unknown column 'cnt' in 'where clause' on a query with groupByAn aggregate alias such as COUNT(*) is used in where()Fix
    3. 3The generated SQL shows backticks around parts of an expression, or a broken aliasA subquery or function in select is mangled by escapingFix

What does the error look like?

  • Unknown column 'cnt' in 'where clause'

Cause 1 of 3

A calculated column alias is used in where()

1Check

Find the alias in the select list, then find the same word in where(). Standard SQL disallows aliases in WHERE, because the value is not known when WHERE runs.

echo $builder->getCompiledSelect();

2Fix

Write the expression in the where() call. Pass false as the third argument so CodeIgniter does not try to quote the parts of the expression.

// CodeIgniter 4
$builder->select('id, price * qty AS full_price', false)
        ->where('price * qty >', 100, false);
// CodeIgniter 3
$this->db->select('id, price * qty AS full_price', FALSE);
$this->db->where('price * qty > 100', NULL, FALSE);

3Verify

Compile the query again; the WHERE line now holds the expression, not the alias.

echo $builder->getCompiledSelect();

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

An aggregate alias such as COUNT(*) is used in where()

1Check

WHERE decides which rows are grouped, so it cannot use a value that only exists after grouping. MySQL allows the alias in HAVING, GROUP BY and ORDER BY.

echo $builder->getCompiledSelect();

2Fix

Move the condition to having().

// CodeIgniter 4
$builder->select('user_id, COUNT(*) AS cnt')
        ->groupBy('user_id')
        ->having('cnt >', 5);

3Verify

The generated SQL shows HAVING cnt > 5 after GROUP BY.

echo $builder->getCompiledSelect();

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

A subquery or function in select is mangled by escaping

1Check

CodeIgniter protects field and table names. A compound expression in select() can break under that protection, which is why the second argument exists.

echo $builder->getCompiledSelect();

2Fix

Pass false as the second argument of select() so the expression goes out as written.

$builder->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id = invoices.id) AS amount_paid', false);

3Verify

Compile the query again and read the select line.

echo $builder->getCompiledSelect();

Confirmed by codeigniter.com

Did this fix it?

How do I stop it coming back?

  • Remember the order SQL works in: WHERE filters rows, then GROUP BY, then HAVING, then the select list names appear.
  • Print the compiled query while you build it, not after it fails.
  • Escape values yourself when you pass a custom where string and turn escaping off.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Databases

A JOIN query fails with 'Column ... is ambiguous'

ERROR 1052 (23000): Column 'i' in on clause is ambiguous

Error 1052 means a column name exists in more than one table of the query and MySQL cannot tell which you mean.

3 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.