Codeigniter: Column 'id' in order is ambiguous

Question

I use the Active Record CodeIgniter classes and I get 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 on why? Something seems to be related to order_by

Answer 1

The error means that you are trying to order by the column name, which is used in several tables. Update the order_by statement with the name of the table in which there is a column that you want to order. For instance:

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

Answer 2

It looks like there is an id column in your atommeta and atoms tables. Since you are joining these tables, you need to specify which column you want to order.

You need

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

or

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

Answer 3

You must indicate in which table the identifier is indicated.

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

choose one:

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

or

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