Field names with JOIN queries

[eluser]deed02392[/eluser]
Hi all, 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.

I want to join a table called `companies` to the `users` table so I can get the name of the company etc the user is in. I've done this sort of successfully like so:

Code:

function get_profile_by_username($username)
{
    $this->db->join('companies', $this->table_name.'.company_id = companies.id');
    $this->db->where('LOWER(username)=', strtolower($username));
    $query = $this->db->get($this->table_name);
    if ($query->num_rows() == 1) return $query->row();
    return NULL;
}

However the issue being that the fields in `companies`, they are `id` and `name` are returned in that object as simply called `name`.

Normally when I would write the raw query I would give aliases to the tables and the result would be something like `u.company_id`, `c.name`. So I'd know `name` 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 `id` column obviously can't coexist in the result set, so one gets overwritten!

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?

[eluser]Unknown[/eluser]
If I understand correctly you are wanting to Alias columns that match when joining tables.
If so, I usually build out the query the old fashioned way:

Code:

$sql = "SELECT Table_1 *, `column`.`ID` AS `column_ID`
    FROM `Table_1`
    JOIN `Table_2` ON Table_2.ID = Table_1.column_ID
    WHERE 1";

  $query = $this->db->query($sql);

[eluser]deed02392[/eluser]
Well that is one solution, but I thought the point of using activerecord was to maintain portability of queries? Of course your solution is only applicable to MySQL.

If I was doing it as a raw query I'd do:

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

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.