Laravel Eloquent 如何加入查询而不是表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37770105/
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 Eloquent how to join on a query rather than a table?
提问by Harrison
I want to achieve this in Laravel:
我想在 Laravel 中实现这一点：
SELECT * FROM products JOIN
(SELECT product_id, MIN(price) AS lowest FROM prices GROUP BY product_id) AS q1
ON products.id = q1.product_id
ORDER BY q1.lowest;
I wrote this, but clearly there is something wrong:
我写了这个，但显然有问题：
$products = new Product();
$products = $products->join(
Price::whereNotNull('price')->select('product_id', DB::raw('min(price) as lowest'))->groupBy('product_id'), 'products.id', '=', 'product_id'
)->orderBy('lowest')->get();
The error I got:
我得到的错误：
ErrorException in Grammar.php line 39:
Object of class Illuminate\Database\Eloquent\Builder could not be converted to string.
I'm currently using join(DB::raw('(SELECT product_id, MIN(price) AS lowest FROM prices WHERE price IS NOT NULL GROUP BY product_id) AS q1'), 'products.id', '=', 'q1.product_id')as a workaround. Just wondering how to do this in the Eloquent way? Thanks.
我目前正在使用join(DB::raw('(SELECT product_id, MIN(price) AS lowest FROM prices WHERE price IS NOT NULL GROUP BY product_id) AS q1'), 'products.id', '=', 'q1.product_id')作为一种解决方法。只是想知道如何以雄辩的方式做到这一点？谢谢。
回答by Trong Lam Phan
In Eloquent, follow me, you need to think a little bit differently from normal mysql query. Use Model instead of complicated query.
在 Eloquent 中，跟我来，你需要和普通的 mysql 查询有点不同。使用模型而不是复杂的查询。
First of all, you need to create Product and Price models with relationship between them:
首先，您需要创建 Product 和 Price 模型以及它们之间的关系：
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Product extends Model
{
public function prices()
{
return $this->hasMany('\App\Price');
}
}
class Price extends Model
{
public function product()
{
return $this->belongsTo('\App\Product');
}
}
Then, you need to select all products:
然后，您需要选择所有产品：
$products = \App\Product::get();
foreach ($products as $product)
{
$minPrice = $product->prices->min('price');
echo 'Min price of product ' . $product->name . ' is: ' . $minPrice;
}
EDIT
编辑
If you have a problem with the performance, just get the product's id.
如果您对性能有疑问，只需获取产品的 id。
$products = \App\Product::get(['id']);
If you don't like this way of Eloquent, you may use Query Builder like that:
如果你不喜欢 Eloquent 的这种方式，你可以像这样使用 Query Builder：
$result = \App\Price::join('products', 'prices.product_id', '=', 'products.id')
->select('product_id', 'name', \DB::raw("MIN(price) AS min_price"))
->groupBy('product_id')
->orderBy('min_price')
->get();
or you can do like that:
或者你可以这样做：
\App\Price::join('products', 'prices.product_id', '=', 'products.id')
->selectRaw("name, MIN(price) AS min_price")
->groupBy('product_id')
->orderBy('min_price')
->get()
回答by Kevin Pe?a
If you want to make a single query for efficiency reasons in this case Eloquent is not going to help you much, it is possible but is a hassle. For cases like this you have QueryBuilder.
如果你想在这种情况下出于效率原因进行单个查询，Eloquent 不会帮助你太多，这是可能的，但很麻烦。对于这种情况，您可以使用 QueryBuilder。
DB::table('products')
->join(
DB::raw('(SELECT product_id, MIN(price) AS lowest FROM prices GROUP BY product_id) AS q1'),
'products.id', '=', 'q1.product_id'
)
->orderBy('q1.lowest')->get();
If you change get()for getSql()you get the following
如果你改变get()了getSql()你会得到如下
select * from `products` inner join
(SELECT product_id, MIN(price) AS lowest FROM prices GROUP BY product_id) AS q1
on `products`.`id` = `q1`.`product_id` order by `q1`.`lowest` asc
Unfortunately as far as I know you can't use a subquery without DB::raw, nevertheless it is not insecure as long as you don't put user input in the query. Even in that case you can use it securely by using PDO.
不幸的是，据我所知，您不能在没有 DB::raw 的情况下使用子查询，但是只要您不将用户输入放入查询中，它就不是不安全的。即使在这种情况下，您也可以通过使用 PDO 安全地使用它。
As for Eloquent, your product model doesn't even have a pricefield (it probably has a prices()function returning a relationship object) so it makes no sense to get a Productmodel with a single price asociated to it.
至于 Eloquent，你的产品模型甚至没有一个price字段（它可能有一个prices()返回关系对象的函数），所以获得一个Product与它相关的单一价格的模型是没有意义的。
Edit:
编辑：
You can also eager loadthe relationship, i.e. (assuming you have the model relationship set as Trong Lam Phan's example)
您也可以预先加载关系，即（假设您将模型关系设置为 Trong Lam Phan 的示例）
$products = Product::with('prices')->get();
foreach ($products as $product)
{
$minPrice = $product->prices->min('price');
// Do something with $product and $minPrice
}
This will only run a single query but the min()operation is not done by the database.
这只会运行一个查询，但min()操作不是由数据库完成的。
