[Solved] Codeigniter: Column ‘id’ in order clause is ambiguous

I’m using CodeIgniter’s Active Record Classes and I’m retrieving an error using the following code:

$this->db->select("*");
$this->db->order_by("id");
$this->db->limit($limit, $offset);
$this->db->from("atoms");
$this->db->join("atommeta", "atommeta.atom_id = atoms.atom_id");

$query  = $this->db->get();

It produces this error:

Error Number: 1052

Column 'id' in order clause is ambiguous

SELECT * FROM (`atoms`) JOIN `atommeta` ON `atommeta`.`atom_id` = `atoms`.`atom_id` ORDER BY `id` LIMIT 10

Filename: /Applications/MAMP/htdocs/atom/models/atom_model.php

Line Number: 197

Line 197: $query = $this->db->get();

Any ideas as to why? It seems to be something to do with the order_by

Enquirer: daryl

||

Solution #1:

The error means that you are trying to order by a column name that is used in more than one table. Update your order_by statement with the name of the table that has the column you want to order by. For example:

$this->db->order_by('atoms.id');
Respondent: birderic

Solution #2:

It looks like there is an id column in both your atommeta and atoms tables. Because you are joining these tables you will need to specify what column you want to order by.

You will want

$this->db->order_by("atoms.id");

or

$this->db->order_by("atommeta.id");
Respondent: nobsid

Solution #3:

You should specify which table that ‘id’ belogns to.

$this->db->select("*");
$this->db->from("atoms");
$this->db->join("atommeta", "atommeta.atom_id = atoms.atom_id");

pick one:

$this->db->order_by("atommeta.id");

or

$this->db->order_by("atoms.id");
Respondent: Damien Pirsy

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.