具有多个条件的Codeigniter加入 [英] Codeigniter Join with Multiple Conditions

问题描述

我正在使用Codeigniter Active Records类，并且想将users表与clients表联接，以便可以显示用户的真实"名称，而不仅仅是他们的ID.

I'm using Codeigniter Active Records class and I want to join my users table with my clients table, so that I can display the users' "real" names and not just their ID.

这是clients表的样子(示例)，并且列a_1，a_2和a_3对应于我的users表，特别是user id:

Here is what the clients table looks like (example) and the columns a_1, a_2, and a_3 correspond to my users table, specifically, the user id:

  clients
|--------|---------|----------|-----------|----------|
|  id    |   name  |   a_1    |    a_2    |   a_3    |
|--------|---------|----------|-----------|----------|
|   1    |   Paul  |    2     |     4     |    1     |

我的users表如下所示:

   users
|--------|---------|----------|
|  id    |  first  |   last   |  
|--------|---------|----------|
|   1    |   Paul  |  Blake   |
|--------|---------|----------|
|   2    |   Dan   |   Doe    |
|--------|---------|----------|
|   3    |  Steve  |  Smith   |
|--------|---------|----------|
|   4    |  Harry  |  Jones   |
|--------|---------|----------|

因此，从本质上讲，如果我是从clients表中选择并加入的，则它看起来像这样:

So, essentially, if I were select from the clients table and JOIN it, it would look like this:

  clients
|--------|---------|----------|-----------|----------|
|  id    |   name  |   a_1    |    a_2    |   a_3    |
|--------|---------|----------|-----------|----------|
|   1    |   Paul  | Dane Doe |Harry Jones|Paul Blake|

至此，我已经尝试过了(这没有用，它只是显示了所有的相同名称):

Thus far, I have tried (which hasn't worked, it just displays the same name for all):

<?
 $this->db
  ->select('name, a_1, a_2, a_3')
  ->from('clients')
  ->join('users', 'users.id=a_1 OR users.id=a_2 OR users.id=a_3');

任何帮助都会很棒！

解决方案:

这就是我能想到的可行的方法(感谢@elavarasan lee):

Here is what I was able to come up with which works (thanks to @elavarasan lee):

<?
 $this->db
  ->select('CONCAT(u1.first," ", u1.last) as a_1_name, CONCAT(u2.first," ", u2.last) as a_2_name, CONCAT(u3.first," ",u3.last) as a_3_name', FALSE)
  ->from('clients')
  ->join('users AS u1', 'u1.id=a_1', 'left')
  ->join('users AS u2', 'u2.id=a_2', 'left')
  ->join('users AS u3', 'u3.id=a_3', 'left');

推荐答案

@Dodinas:我得到了MYSQL Query的解决方案.我发现很难将查询转换为CI Active Record.但是尝试一下:

@Dodinas: I got the solution interms of MYSQL Query. I am finding it hard to convert the query into CI Active Record. But try this:

    $sql = "SELECT `name`, CONCAT(`u1`.`first`,' ', `u1`.`last`) as a_1, 
                           CONCAT(`u2`.`first`,' ', `u2`.`last`) as a_2, 
                           CONCAT(`u3`.`first`,' ', `u3`.`last`) as a_3 
            FROM `clients` 
            LEFT JOIN `users` as `u1` ON (`u1`.`id`=`a_1`)
            LEFT JOIN `users` as `u2` ON (`u2`.`id`=`a_2`)
            LEFT JOIN `users` as `u3` ON (`u3`.`id`=`a_3`)";

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

这篇关于具有多个条件的Codeigniter加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！