laravel 如何在laravel中加入同一张表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28979866/
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
How to Join same table in laravel
提问by Thomas
I wan to wirte a join query to connect same table, and without ON, but when i write it in laravel without on it is showing error
我想编写一个连接查询来连接同一个表，并且没有ON，但是当我在 laravel 中编写它时没有显示错误
$key = DB::table('api_keys as ak')
->join('api_keys as bk','')
->where('ak.api_key', $api_key)->where('ak.user_id',0)
->pluck('api_key');
want to build the below query,
想要构建以下查询，
SELECT * FROM `api_keys` as ak
JOIN `api_keys` as bk
WHERE ak.`api_key`=$akey
and ak.`user_id`=$auser
and bk.`user_id`=$bsuer
and bk.`api_key`=$bkey
回答by Superfly
You must provide an ONclause for the join. More about where ONclauses are required can be found in this answer.
您必须为ON连接提供一个子句。ON可以在此答案中找到有关需要where子句的更多信息。
You can view the generated query using toSql() on a QueryBuilder object:
您可以在 QueryBuilder 对象上使用 toSql() 查看生成的查询：
echo $key = DB::table('api_keys as ak')
->join('api_keys as bk','')
->where('ak.api_key', $api_key)->where('ak.user_id',0)
->toSql();
Which in your case returns:
在您的情况下返回：
select * from `api_keys` as `ak` inner join `api_keys` as `bk`
on `` `` where `ak`.`api_key` = ? and `ak`.`user_id` = ?
In your case it isn't totally clear what you are trying to achieve, but you might consider joining on api_keyor the primary key of the api_keystable, if that is different:
在您的情况下，您要实现的目标并不完全清楚，但您可能会考虑加入表api_key的主键或主键api_keys，如果不同：
$key = DB::table('api_keys as ak')
->join('api_keys as bk','ak.api_key', '=', bk.api_key)
->where('ak.api_key', $api_key)->where('ak.user_id',0)
->pluck('api_key');
回答by Basit Munir
without using on clause in laravel query builder you can use following
不使用 laravel 查询生成器中的 on 子句，您可以使用以下内容
$key = DB::table(DB::raw('api_keys as ak, api_keys as bk'))
->where('ak.api_key', '=', $api_key)
->where('ak.user_id','=',0)
->where('ak.PK','=','bk.PK')
->pluck('ak.api_key')
where PK references to your table's primary key. result will in your case.
其中 PK 引用您的表的主键。结果会在你的情况下。
select * from api_keys as ak, api_keys as bk where ak.api_key= 'api_key_value' and ak.user_id = 0 and ak.PK = bk.PK
回答by HPage
I solved this by creating my own class and starting out with a base query which I modify to apply the join (using Laravel's joinSubfunction) as follows:
我通过创建自己的类并从我修改以应用连接（使用 Laravel 的joinSub函数）的基本查询开始解决了这个问题，如下所示：
public function __construct()
{
$this->query = DB::table('question_responses as BASE');
}
public function applyFilter($questionId, $questionValue) {
$filterTableStr = 'filter_table_'.$questionId;
$filterIdStr = 'filter_id_'.$questionId;
$filterQuery = DB::table('question_responses AS '.$filterTableStr)
->select('survey_response_id AS '.$filterIdStr)
->where($filterTableStr.'.question_short_name', $questionId)
->where($filterTableStr.'.value', $questionValue);
$resultTableStr = 'result_table_'.$questionId;
$this->query = $this->query
->joinSub($filterQuery, $resultTableStr, function($join) use ($resultTableStr, $filterIdStr) {
$join->on('BASE.survey_response_id', '=', $resultTableStr.'.'.$filterIdStr);
});
}
After applying my required filters I can just call $this->query->get()as normal to obtain the result.
应用我所需的过滤器后，我可以$this->query->get()正常调用以获得结果。
The important part was to make sure that each resulting table and join fields has unique names. With this method I can apply unlimited filters to my base query.
重要的部分是确保每个结果表和连接字段都有唯一的名称。使用这种方法，我可以将无限过滤器应用于我的基本查询。
回答by Praveen Soni
DB::table('registerusers as a')
->join('registerusers as b', 'a.id', 'b.refer_id')
->where('a.username', 'b.username')
->where('b.id', 'a.refer_id')
->value('b.id');
