Codeigniter: Column 'id' in order clause is ambiguous

Question

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

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');

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");

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");

Comments

[birderic]

Leave it so that others who run into the same problem will have a solution :)

[daryl]

Not sure why others are answering when it's already been answered.

[Damien Pirsy]

@Brogrammer If you look at the time of when answers were posted, you could see that they're very close each other. Personally, I answered because I got notification of an already posted answer when mine was complete and I was about to hit the submit button. Nobdisd's answer, instead, comes exactly 15 secs before mine, it could be easily said that we answered at the same time. So, " why other are answering", told 9 minutes after the last answer, it's a strange question