如何在 Laravel eloquent 中进行子查询?

2021-12-26 00:00:00 php laravel laravel-5.3 laravel-eloquent

当我使用 db raw 时，它可以工作

When I use db raw, it works

我的查询使用的是这样的 db raw :

My query is using db raw like this :

$products = DB::select(DB::raw('SELECT * 
                FROM (
                    SELECT a.*, b.name AS store_name, b.address
                    FROM products a
                    JOIN stores b ON b.id = a.store_id
                    WHERE a.category_id = '.$category_id.'
                    ORDER BY a.total_sold DESC, a.updated_at DESC
                    LIMIT '.$num.'
                ) AS product
                GROUP BY store_id'));

它有效.但是我想改用laravel eloquent

It works. But I want to change it use laravel eloquent

我是这样尝试的:

$products = Product::where('category_id', '=', $category_id)
     ->with('store')
     ->groupBy('store_id')
     ->orderBy('total_sold','desc')
     ->orderBy('updated_at', 'desc')
     ->take($num)
     ->get();

它也有效.但是 orderBy updated_at 没有执行

It also works. But orderBy updated_at not executed

我该如何解决?

推荐答案

在我看来，您使用的 group by 不正确.即使您在查询之前检索到了正确的结果，我也认为这是偶然的.Group by 应该用于聚合查询结果并获取聚合列值.选择实际上未聚合的列如果使用不当可能会很危险.

It seems to me that you are using group by incorrectly. Even if you retrieved correct results for the query before it looks to me that it was by chance anyway. Group by should be used to aggregate query results and get aggregated column values. By choosing columns which are not actually aggregated can be dangerous if used incorrectly.

来自 5.6 版的 Mysql 文档:

From the Mysql docs for version 5.6:

MySQL 扩展了 GROUP BY 的标准 SQL 使用，以便选择列表可以引用未在 GROUP BY 子句中命名的非聚合列.这意味着前面的查询在 MySQL 中是合法的.您可以使用此功能通过避免不必要的列排序和分组来获得更好的性能.但是，这主要在未在 GROUP BY 中命名的每个非聚合列中的所有值对于每个组都相同时很有用.服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的.此外，添加 ORDER BY 子句不会影响从每个组中选择值.结果集排序发生在选择值之后，ORDER BY 不影响服务器选择每个组中的哪些值.

MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

此外，从 MySql 5.7.5 开始，默认 SQL 模式包括 ONLY_FULL_GROUP_BY 标志，它将:

Additionally as of MySql 5.7.5 the default SQL mode includes ONLY_FULL_GROUP_BY flag which will:

拒绝选择列表、HAVING 条件或 ORDER BY 列表引用非聚合列的查询，这些列既不在 GROUP BY 子句中命名，也不在功能上依赖于(唯一确定的)GROUP BY 列.

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

出于教育目的，您应该能够像这样使用 Laravel 实现完全相同的查询(未经测试且不使用表别名)，但我会避免使用它:

For educational purposes you should be able to achieve the exact same query with Laravel like this (untested and without the use of table aliases), but I would avoid using it:

$subQuery = Products::selectRaw('products.*, stores.name as store_name, stores.address')
    ->join('stores', 'stores.id', '=', 'products.store_id')
    ->where('products.category_id', '=', $category_id)
    ->orderBy('products.total_sold', 'DESC')
    ->orderBy('products.updated_at', 'DESC')
    ->take($num)

$products = DB::table(DB::raw('(' . $subQuery->toSql() . ') t'))
    ->groupBy('store_id')
    ->setBindings($subQuery->getBindings())
    ->get();

但在我看来，您想要做的似乎是将所有商店与您想要的类别中的产品放在一起.所以最 Laravel 的解决方案可能是这样的:

But to me it seems that what you're trying to do is get all the stores together with products in your desired category. So the most Laravel solution would probably be something like:

Stores::with(['products' => function($productsQuery) use ($category_id) {
    // This limits all the retrieved products to the provided category
    $productsQuery
        ->where('category_id', '=', $category_id)
        ->orderBy('total_sold', 'DESC')
        ->orderBy('updated_at', 'DESC');
}])->whereHas('products', function($productsQuery) use ($category_id) {
    // This makes sure that the store actually has at least one product from the category
    $productsQuery->where('category_id', '=', $category_id);
})->get();

通过查看您的查询，我可能做出了错误的假设，但目前没有多大意义......无论如何我都会从那里开始.

I might have made wrong assumptions by looking at your query but it doesn't make much sense at the moment... I would start from there anyway.

相关文章