Codeigniter活动记录左连接问题

Question

I have two tables 'accounts_transactions' and 'accounts_bills_transactions'. I have to left join these two using active record of codeigniter.But the names of key columns used to join are different.So I am not getting the key column from the left table in the output .What query should I write to get the key column from the left table included in the result. My code is

    $this->db->select('*');
    $this->db->from('accounts_transactions');
    $this->db->join('accounts_bills_transactions', 'accounts_transactions.id = accounts_bills_transactions.transaction_id','left');
    $query = $this->db->get();

So, as you see the key columns used to join here are , id from left table and transaction_id from second table.The problem is that I am not getting the id from left table in the result.But I am getting all other columns.I assume the problem is because of difference in column names used to join.ie both the column names are not named 'id' .So how can I get the id from left table included in the result.

Answer 1

You could alias them:

$this->db->select('accounts_transatctions.*, account_transactions.id AS a_id,
                   accounts_bills_transactions.*, 
                   account_bills_transactions.id AS ab_id');
$this->db->from('accounts_transactions');
$this->db->join('accounts_bills_transactions', 'accounts_transactions.id = accounts_transactions.transaction_id','left');
$query = $this->db->get();

The two IDs will now be available as a_id and ab_id (or whatever alias you choose)

Note: I'm not sure if you can alias in AR without avoiding escaping (haven't been using CI for a while). Should you get any error for that reason, just pass false as second parameter of $this->db->select():

$this->db->select('...', false);