CodeIgniter ActiveRecord field names in JOIN statement

Question

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:

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?

Edit:

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

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.

Answer 1

If you want to select some specific columns from table use db->select(). You can give alias to tables, add some conditions and etc. Send second parameter FALSE to not escape special characters.

$this->db->select('u.id, u.username, c.name', false);
$this->db->from('user as u');
$this->db->join('companies as c', 'u.company_id = c.id');
$this->db->where('LOWER(u.username)=', strtolower('foobar'));
$query = $this->db->get();