Codeigniter活动记录左连接问题

问题描述：

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.

我有两个表'accounts_transactions'和'accounts_bills_transactions'。我必须使用活动记录将这两个表连接起来 codeigniter.But用于连接的键列的名称是不同的。所以我没有从输出中的左表中获取键列。我应该写什么查询以从结果中包含的左表中获取键列。我的代码是 p>

  $ this-＆gt; db-＆gt; select（'*'）; 
 $ this-＆gt; db-＆gt; from（'accounts_transactions'）  ; 
 $ this-＆gt; db-＆gt; join（'accounts_bills_transactions'，'accounts_transactions.id = accounts_bills_transactions.transaction_id'，'left'）; 
 $ query = $ this-＆gt; db-＆gt; get（）  ; 
  code>  pre> 
 
 因此，正如您所看到的那样，用于连接的键列是左表中的id和第二个表中的transaction_id。问题是我没有得到 结果中左表的id。但我得到所有其他列。我认为问题是因为colu的差异 用于连接的mn名称。两个列名都没有命名为'id'。那么如何从结果中包含的左表中获取id。 p> 
  div>

答

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

答

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

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

return $query;

答

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

Codeigniter活动记录左连接问题

相关推荐