MySQL Codeigniter 加入多个条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15992236/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Codeigniter Join with Multiple Conditions
提问by Dodinas
I'm using Codeigniter Active Records class and I want to join my userstable with my clientstable, so that I can display the users' "real" names and not just their ID.
我正在使用 Codeigniter Active Records 类，我想将我的users表与我的clients表连接起来，这样我就可以显示用户的“真实”姓名，而不仅仅是他们的 ID。
Here is what the clientstable looks like (example) and the columns a_1, a_2, and a_3 correspond to my userstable, specifically, the user id:
这是clients表的样子（示例），列 a_1、a_2 和 a_3 对应于我的users表，具体来说，是user id：
clients
|--------|---------|----------|-----------|----------|
| id | name | a_1 | a_2 | a_3 |
|--------|---------|----------|-----------|----------|
| 1 | Paul | 2 | 4 | 1 |
And my userstable looks like this:
我的users桌子看起来像这样：
users
|--------|---------|----------|
| id | first | last |
|--------|---------|----------|
| 1 | Paul | Blake |
|--------|---------|----------|
| 2 | Dan | Doe |
|--------|---------|----------|
| 3 | Steve | Smith |
|--------|---------|----------|
| 4 | Harry | Jones |
|--------|---------|----------|
So, essentially, if I were select from the clientstable and JOIN it, it would look like this:
所以，基本上，如果我从clients表中选择并加入它，它看起来像这样：
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');
Any help would be great!
任何帮助都会很棒！
Solution:
解决方案：
Here is what I was able to come up with which works (thanks to @elavarasan lee):
这是我能够想出的工作（感谢@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');
采纳答案by Elavarasan M Lee
@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:
@Dodinas：我得到了 MYSQL 查询的解决方案。我发现很难将查询转换为 CI Active Record。但是试试这个：
$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);
回答by whispersan
Try this
尝试这个
$this->db
->select('*')
->from('clients')
->join('users', 'users.id = clients.a_1 OR users.id=clients.a_2 OR users.id = clients.a_3');
$query = $this->db->get();
return $query->result();
