Codeigniter active record left join issue

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.

Solution 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);

Solution 2

you can try this if you confuse of using $this->where or $this->join

$query = $this->db->query("select ......"); 

return $query;

Solution 3

You problem is so simple. You can use this query

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

When same column are used in join it selects only one. You need to give alise to the other column in second table. The best practice is to use a structure like this

accounts_transatctions
--------------------------
accounts_transatctions_id
other_columns

accounts_bills_transactions
---------------------------
accounts_bills_transactions_id
accounts_transatctions_id
other_columns

Comments

[Damien Pirsy]

You could try listing all the columns in your select. Are you sure, also, that your query is fine? I mean, maybe you don't want a LEFT join...

[Jinu Joseph Daniel]

I strictly need left join :)