php Laravel LeftJoin where
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26711573/
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 LeftJoin where
提问by lggg3
I have some problems with my eloquent model, where I try to join two different tables in one
我的雄辩模型有一些问题，我尝试将两个不同的表合二为一
Now I have two tables like.
现在我有两张桌子。
First tables name "company_saved_cv"
第一个表名称“company_saved_cv”
|id|company_id|worker_id |
--------------------------
|1| 2 | 61 |
|3| 3 | 66 |
|4| 2 | 69 |
--------------------------
Second tables name "workers"
第二个表名称“工人”
|id|location|name_and_surname|
------------------------------
|61|London | John John |
|62|London | John John |
|66|London | John John |
|67|London | John John |
|68|London | John john |
And I whana get table like this.
我想得到这样的桌子。
|id|location|name_and_surname|worker_id|
---------------------------------------|
|61|London | John John | 61 |
|62|London | John John | NULL |
|66|London | John John | 66 |
|67|London | John John | NULL |
|68|London | John john | NULL |
where is my model function
我的模型函数在哪里
public static function CvSearch($interested_field, $location)
{
$match = "MATCH( `interested_fields`) AGAINST (?)";
$query = Worker::select('workers.name_and_surname', 'workers.id', 'workers.location','company_saved_cv.worker_id')
->leftJoin('company_saved_cv', function($leftJoin)
{
$leftJoin->on('company_saved_cv.worker_id', '=', 'workers.id')
->where('company_saved_cv.company_id', '=', Session::get('company_id') );
})
->where('workers.location', '=', $location)
->whereRaw($match, array($interested_field))
->orderBy('workers.created_at')
->paginate(10);
return $query;
}
If I comment this line:->where('company_saved_cv.company_id', '=', Session::get('company_id') );It is working, but I need get only rows from workers table where company_id = 2 (in my example);
如果我评论这一行：->where('company_saved_cv.company_id', '=', Session::get('company_id') );它正在工作，但我只需要从工人表中获取 company_id = 2 的行（在我的示例中）；
Session::get('company_id') = 2
I will check it by logging it in laravel.log file.
我将通过在 laravel.log 文件中记录它来检查它。
I fink it is problem with where('company_saved_cv.company_id', '=', Session::get('company_id') ); request, but I can't figure it out, maybe some one can help me?
我认为 where('company_saved_cv.company_id', '=', Session::get('company_id') ); 有问题 请求，但我无法弄清楚，也许有人可以帮助我？
回答by lggg3
I solve this problem with this code
我用这段代码解决了这个问题
$query = Worker::select('workers.name_and_surname', 'workers.id', 'workers.location','company_saved_cv.worker_id')
->leftJoin('company_saved_cv', function($leftJoin)use($company_id)
{
$leftJoin->on('workers.id', '=', 'company_saved_cv.worker_id');
$leftJoin->on(DB::raw('company_saved_cv.company_id'), DB::raw('='),DB::raw("'".$company_id."'"));
})
回答by Abhishek Gupta
Well when you use LEFT JOIN with where it acts as an INNER JOIN
好吧，当您将 LEFT JOIN 用作 INNER JOIN 时
Use LEFT JOIN and WHERE like
使用 LEFT JOIN 和 WHERE 之类的
SELECT workers.name_and_surname, workers.id, workers.location,company_saved_cv.worker_id
FROM workers LEFT JOIN company_saved_cv
ON company_saved_cv.worker_id= workers.id
WHERE company_saved_cv.company_id = 2
OUTPUT
输出
|id|location|name_and_surname|worker_id|
---------------------------------------|
|61|London | John John | 61 |
|66|London | John John | 66 |
Use LEFT JOIN and AND like
使用 LEFT JOIN 和 AND 之类的
SELECT workers.name_and_surname, workers.id, workers.location,company_saved_cv.worker_id
FROM workers LEFT JOIN company_saved_cv
ON company_saved_cv.worker_id= workers.id
AND company_saved_cv.company_id = 2
OUTPUT
输出
|id|location|name_and_surname|worker_id|
---------------------------------------|
|61|London | John John | 61 |
|62|London | John John | NULL |
|66|London | John John | 66 |
|67|London | John John | NULL |
|68|London | John john | NULL |
New query
新查询
$query = Worker::select('workers.name_and_surname', 'workers.id', 'workers.location','company_saved_cv.worker_id')
->leftJoin('company_saved_cv', function($leftJoin)
{
$leftJoin->on('company_saved_cv.worker_id', '=', 'workers.id')
->on('company_saved_cv.company_id', '=', Session::get('company_id') );
})
回答by Donny van V
You can easily use the Model for it. The last table, you can remove the location because you can get it with and from the model. Create easily a many-2-many relationship or something you want. Check the docs: http://laravel.com/docs/4.2/eloquent#relationships
您可以轻松地使用模型。最后一个表，您可以删除位置，因为您可以从模型中获取它。轻松创建多对多关系或您想要的东西。检查文档：http: //laravel.com/docs/4.2/eloquent#relationships
