CodeIgniter Active Record aliasing column names to prevent overwriting (especially columns named id)

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.

5 thoughts on “CodeIgniter Active Record aliasing column names to prevent overwriting (especially columns named id)”

  1. I came across this whilst searching for a way to automate this process. Any ideas?

    Just as a side note in addition to the above tutorial which may be of use to someone is that you can also alias table names.

    $this->db->select('a.*', FALSE);
    $this->db->select('b.name, b.id AS new_name', FALSE);
    $this->db->from('tableA a');
    $this->db->join('tableB b', 'a.name = b.name');
    $query = $this->db->get();;
    $results = $query->result_array();

    Ben

  2. If I understand your query correctly, you want to make column selection and aliasing automatic.
    I’m not sure exactly why you’d want to automate this. This code is part of a model, and by definition, code in a model need only be written once, and will always be custom.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>