On a recent project I set up all my tables so that they had a primary key, auto-incrementing column named id. I figured this was a good practice, since it meant that all update queries could just reference the id column. All was well until I wanted to join two tables, and they both had id columns, and neither of them was going to be using the id column for the join.
This caused the id of the second table to overwrite that of the first, which is undesirable, and usually takes a programmer forever to figure out what the hell happened.
With regular queries you would alias the id column of the second table, which basically just changes it’s name temporarily for this query. But how do you do that in CodeIgniter when you’re using the Active Record Class (which I love)?
Well if your second table only has a few columns, it’s easy. If there are 50 columns, it’s sort of a pain in the ass and you might consider reworking your column naming.
$this->db->select('tableA.*', FALSE); $this->db->select('tableB.name, tableB.id AS new_name', FALSE); $this->db->from('tableA'); $this->db->join('tableB', 'tableA.name = tableB.name'); $query = $this->db->get();; $results = $query->result_array();
Line 2 is where the magic happens. To turn it into human language, it says “when we get to tableB, I only want the columns called name, and id, but I want you to rename id to whatever_you_want_to_call_it“.
If you need to refer to the id column from tableB, you can called it tableB.whatever_you_want_to_call_it.