CodeIgniter query builder: Column 'id' in order clause is ambiguous
ERROR 1052 (23000): Column 'id' in order clause is ambiguousShort answer
MySQL error 1052 means the column name exists in more than one joined table, so MySQL cannot pick one. CodeIgniter only passes your text through. Qualify the column with its table name, for example order_by('users.id'), or give the column an alias in select() and sort by the alias.
Fix it fast
1Find out which cause you have
Read the SQL on CodeIgniter's database error page, then see which joined tables both have the column. The words after 'in' in the message (order clause, where clause, on clause) show where to qualify it.
// CodeIgniter 3: print the last query before it fails or after it runs echo $this->db->last_query();2Match what you see, then jump to its fix
- 1
Column 'id' in order clause is ambiguousorder_by() uses a bare column name that two joined tables shareFix - 2
Column 'id' in where clause is ambiguous, or in on clause is ambiguouswhere() or the join condition uses an unqualified columnFix - 3
The select list holds several id columns and order_by() names only idTwo selected columns with the same name and a sort on that nameFix
- 1
What does the error look like?
ERROR 1052 (23000): Column 'id' in order clause is ambiguousColumn 'id' in order clause is ambiguousColumn 'id' in where clause is ambiguousColumn 'id' in on clause is ambiguous
Cause 1 of 3
order_by() uses a bare column name that two joined tables share
1Check
MySQL's error reference says the cause is a column used without proper qualification. A join of users and orders both with an id column makes ORDER BY id unclear.
$this->db->select('users.*, orders.total');
$this->db->from('users');
$this->db->join('orders', 'orders.user_id = users.id');
$this->db->order_by('id', 'DESC'); // fails: which id?2Fix
Prefix the column with the table name in order_by().
$this->db->order_by('users.id', 'DESC');
$query = $this->db->get();3Verify
Run the page again; the query returns rows ordered by that table's column.
echo $this->db->last_query();Confirmed by dev.mysql.com
Cause 2 of 3
where() or the join condition uses an unqualified column
1Check
The same rule applies in every clause. The MySQL reference gives an ON clause example, ON i = i, that fails because both sides are the same bare name.
$this->db->join('orders', 'user_id = id'); // ambiguous in on clause
$this->db->where('id', $id); // ambiguous in where clause2Fix
Qualify each column with its table in the ON condition and in where().
$this->db->join('orders', 'orders.user_id = users.id');
$this->db->where('users.id', $id);3Verify
Run the query again. No error 1052 appears.
echo $this->db->last_query();Confirmed by dev.mysql.com
Cause 3 of 3
Two selected columns with the same name and a sort on that name
1Check
A column alias can be used in ORDER BY, according to the MySQL manual. Giving each duplicate column its own name removes the doubt.
$this->db->select('users.id AS user_id, orders.id AS order_id, orders.total');2Fix
Alias the columns in select() and order by the alias.
$this->db->select('users.id AS user_id, orders.id AS order_id, orders.total');
$this->db->from('users');
$this->db->join('orders', 'orders.user_id = users.id');
$this->db->order_by('order_id', 'DESC');3Verify
Inspect the first row: it holds the aliased keys user_id and order_id.
var_dump($this->db->get()->row_array());Confirmed by dev.mysql.com
How do I stop it coming back?
- Always write table.column in any query that has a join, including where() and order_by().
- Give joined tables short aliases (users AS u) and qualify with the alias to keep lines short.
- Print last_query() while developing so you read the real SQL.
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Back end
CodeIgniter query builder: Unknown column when WHERE uses a SELECT alias
Unknown column 'cnt' in 'where clause'
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.
3 causes, each with a fixBack end
CodeIgniter 4: try/catch does not catch a failed insert in the model
DatabaseException
CodeIgniter throws a DatabaseException on a failed query only when DBDebug is true, and inside a transaction it does not throw unless you ask.
3 causes, each with a fixBack end
CodeIgniter 4 where() builds a different condition than you meant
A SQL syntax error that appears only when a value contains a quote character
The query builder joins every where() with AND and every orWhere() with OR, with no brackets.
3 causes, each with a fixBack end
Call to undefined function mysql_pconnect() after a PHP upgrade
Call to undefined function mysql_connect()
The old mysql_* functions belonged to the ext/mysql extension, which PHP removed in version 7.0.
2 causes, each with a fixBack end
CodeIgniter: Call to undefined method on the result object when using order_by() after get_where()
Call to undefined method CI_DB_mysql_result::order_by()
get_where() runs the query at once and returns a result object, not the query builder.
3 causes, each with a fixBack end
CodeIgniter cannot connect to the MySQL database server
Unable to connect to your database server using the provided settings.
CodeIgniter only reports that its connection failed.
4 causes, each with a fix