title: CodeIgniter ActiveRecord field names in JOIN statement
body: <p>I am building a query involving a JOIN. This is the first time I've done db stuff with Active Record and I've hit a bit of a snag.</p>
  
  <p>I want to join a table called <code>companies</code> to the <code>users</code> table so I can get the name of the company etc the user is in. I've done this sort of successfully like so:</p>
  
  <pre><code>function get_profile_by_username($username)
  {
      $this-&gt;db-&gt;join('companies', $this-&gt;table_name.'.company_id = companies.id');
      $this-&gt;db-&gt;where('LOWER(username)=', strtolower($username));
      $query = $this-&gt;db-&gt;get($this-&gt;table_name);
      if ($query-&gt;num_rows() == 1) return $query-&gt;row();
      return NULL;
  }
  </code></pre>
  
  <p>However the issue being that the fields in <code>companies</code>, they are <code>id</code> and <code>name</code> are returned in that object as simply called <code>name</code>.</p>
  
  <p>Normally when I would write the raw query I would give aliases to the tables and the result would be something like <code>u.company_id</code>, <code>c.name</code>. So I'd know <code>name</code> had nothing to do with the user but of course is the name of the company. And although not an issue now but potentially in the future, the <code>id</code> column obviously can't coexist in the result set, so one gets overwritten!</p>
  
  <p>How can we get this sort of differentiating between the fields that come from certain tables? Or is there a better way of going about table joins and working with joined query data sets/objects?</p>
  
  <p>Edit:</p>
  
  <p>If I was doing it as a raw query I'd do:</p>
  
  <pre><code>SELECT u.id, u.username, c.name
  FROM users AS u
  JOIN companies AS c
  ON c.id = u.company_id
  WHERE u.username = 'foobar';
  </code></pre>
  
  <p>Which is great but if I tried to do that in active record I reckon that's pretty poor practice, if it works at all.</p>