Laravel 从两列连接两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26880036/
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
Laravel join two table from two columns
提问by user173457
How do I achieve something like thisusing Laravel Eloquent? I didn't find how to name the same table twice using Eloquent.
如何实现像这样使用Laravel雄辩？我没有找到如何使用 Eloquent 两次命名同一个表。
Thanks.
谢谢。
Table users
表用户
id | first_name | last_name |
---+------------+-----------+
1 | John | Doe |
2 | Jane | Doe |
3 | Some | Name |
Table stamp
表印章
id | date | applicant_id | app_by_id |
---+------------+---------------+-----------+
1 | 2013-03-15 | 1 | 2 |
2 | 2013-03-10 | 2 | 3 |
3 | 2013-03-13 | 2 | 1 |
What I want to show:
我想展示的内容：
date | applicant | app_by |
------------+-----------+-----------+
2013-03-15 | John Doe | Jane Doe |
2013-03-10 | Jane Doe | Some Name |
2013-03-13 | Jane Doe | John Doe |
Desired equivalent SQL query:
所需的等效 SQL 查询：
SELECT s.date,
CONCAT_WS(' ', NULLIF(u1.first_name, ' '), NULLIF(u1.last_name, ' ')) AS applicant,
CONCAT_WS(' ', NULLIF(u2.first_name, ' '), NULLIF(u2.last_name, ' ')) AS app_by
FROM stamp s
LEFT JOIN users u1 ON s.applicant_id = u1.id
LEFT JOIN users u2 ON s.app_by_id = u2.id
回答by P??
Why don't you use the raw SQL statement like this if you already have it?
如果你已经有了它，为什么不使用这样的原始 SQL 语句呢？
$data = DB::Select("SELECT s.date,
CONCAT_WS(' ', NULLIF(u1.first_name, ' '), NULLIF(u1.last_name, ' ')) AS applicant,
CONCAT_WS(' ', NULLIF(u2.first_name, ' '), NULLIF(u2.last_name, ' ')) AS app_by
FROM stamp s
LEFT JOIN users u1 ON s.applicant_id = u1.id
LEFT JOIN users u2 ON s.app_by_id = u2.id;"
);
Eloquent was made to make your life easier. If you try to force an extensive SQL statement into Eloquent it doesn't make your life easier.
Eloquent 旨在让您的生活更轻松。如果你试图将大量的 SQL 语句强加到 Eloquent 中，它不会让你的生活更轻松。
回答by Fazal Rasel
You can join table like this-
你可以像这样加入表格 -
$stamp = Stamp::join('users as application', 'application.id', '=', 'stamps.applicant_id')
->join('users as app_by', 'app_by.id', '=', 'stamps.app_by_id')
->select(
'data',
DB::raw("CONCAT(application.first_name,' ', application.last_name) as application"),
DB::raw("CONCAT(app_by.first_name,' ', app_by.last_name) as app_by")
)
->get();
Now, If you like to do this by defining relation on model then-
现在，如果您想通过在模型上定义关系来做到这一点，那么-
class Stamp extends Eloquent {
public function application()
{
return $this->belongsTo('User', 'applicant_id');
}
public function appBy(){
return $this->belongsTo('User', 'app_by_id');
}
}
query
询问
$stamp = Stamp::with('application', 'appBy')->get();
foreach($stamp as $s){
echo $s->data . ' ';
echo $s->application->first_name. ' ', $s->application->last_name. ' ';
echo $s->appBy->first_name. ' ', $s->appBy->last_name;
echo '<br>';
}
