laravel 自我加入 Eloquent - 如何调用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30595166/
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
Self Join in Eloquent - How To Call
提问by panthro
I recently asked a question regarding a self join
我最近问了一个关于自联接的问题
I got a great answer but I'm not sure how to call it.
我得到了一个很好的答案，但我不知道如何称呼它。
I need to do this query:
我需要做这个查询：
SELECT t2.title FROM products t1, products t2
WHERE t1.id = $id
AND t2.color_id = t1.color_id AND
t2.id != $id
I now have this on my products model:
我现在在我的产品模型上有这个：
public function parent()
{
return $this->belongsTo(self::class, 'color_id');
}
public function children()
{
return $this->hasMany(self::class, 'color_id');
}
But how do I call this?
但是我怎么称呼它呢？
Product::with('children')->find(1);
The above gets the product with id 1 but also gets children that have a color_id of 1, I need to get children who have a color_id the same as the id as product 1.
上面得到id为1的产品，但也得到color_id为1的孩子，我需要得到color_id与产品1相同的孩子。
eg.
例如。
Products
产品
id | title | color_id
1 dvd 2
When I select row 1 it should select all other products with a color_id of 2.
当我选择第 1 行时，它应该选择 color_id 为 2 的所有其他产品。
回答by MaGnetas
I believe your relations are not the way they're supposed to be. Usually it's one column (foreign key - color_id in your case) having a value of the other one (usually primary key - id in your case).
我相信你们的关系不是他们应该的样子。通常它是一列（在您的情况下为外键 - color_id）具有另一列的值（在您的情况下通常是主键 - id）。
What you have is basically a value the records share or a "category". So your products are not "children" but rather siblings (have the same parent color).
您拥有的基本上是记录共享的值或“类别”。所以你的产品不是“孩子”而是兄弟姐妹（具有相同的父颜色）。
Since withmethod is not build as a JOIN statement but as eager loading (separate query) you can do that manually.
由于with方法不是作为 JOIN 语句构建的，而是作为预加载（单独查询）构建的，因此您可以手动执行此操作。
Probably the most straight forward way:
可能是最直接的方法：
$product1 = Product::find(1);
$children = Product::where('id', '<>', $product1->id)->
where('color_id', $product1->color_id)->
get();
You can add select('title')to the second "builder" to get only title but that would not be your model anymore. Or you can use lists('title')to extract only titles if that's what you need.
您可以添加select('title')到第二个“构建器”以仅获得标题，但这不再是您的模型。或者，lists('title')如果您需要，您可以使用仅提取标题。
UPDATE:
更新：
If you decide you need the JOIN after all I'd suggest going with raw query builder and leave the Eloquent out of it:
如果您最终决定需要 JOIN，我建议您使用原始查询构建器并将 Eloquent 排除在外：
$res = DB::table('products as t1')->
select('t2.title')->
join('products AS t2', 't2.color_id', '=', 't1.color_id')->
where('t1.id', 1)->
where('td2.id', '<>', 't1.id')->
get();
I believe it should build something similar to what you need.
我相信它应该构建类似于您需要的东西。
回答by bharat
You can try this way:
你可以试试这个方法：
// Category.php
public function children()
{
return $this->hasMany(Category::class, 'parent_id');
}
public function parent()
{
return $this->belongsTo(Category::class, 'parent_id');
}
// To access
$category->children; // sub-categories collection
$category->parent; // parent instance
based on : https://laracasts.com/discuss/channels/eloquent/self-join-in-eloquent-model?page=1
基于：https: //laracasts.com/discuss/channels/eloquent/self-join-in-eloquent-model?page=1
回答by ChainList
You might want to do as follow :
您可能想要执行以下操作：
$id = 1;
$product = Product::with('children')->whereHas('children', function($q) use ($id)
{
$q->where('color_id', $id);
})->find($id);
回答by Ryan
See Advanced Join Clausesand adapt from my example here.
请参阅高级连接子句并从我的示例中改编。
It took me a long time to wrap my head around Laravel joins.
我花了很长时间才开始了解 Laravel 连接。
This:
这个：
$postIds = DB::table('comments as t1')
->select('t1.*')
->leftJoin('comments as t2', function ($join) {
$join->on('t1.postId', '=', 't2.postId')
->on('t1.created_at', '<', 't2.created_at');
})
->where('t2.id', '=', null)
->orderBy('t1.created_at', 'DESC')
->simplePaginate(20)
->pluck('postId');
seems to be the way to get an array of the postIds that would be revealed by this:
似乎是获取将由此显示的 postId 数组的方法：
SELECT t1.*
FROM comments a
LEFT OUTER JOIN comments b
ON t1.postId = t2.postId
AND t1.created_at < t2.created_at
WHERE t2.id IS NULL
ORDER BY t1.created_at DESC
