如何在laravel中雄辩地进行子查询? [英] How can I make sub query in 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雄辩地更改它

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();

它也可以工作.但是orderByupdated_at没有执行

It also works. But orderBy updated_at not executed

我该如何解决?

推荐答案

在我看来，您错误地使用了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.

从mysql文档(版本5.6):

From the Mysql docs for version 5.6:

MySQL扩展了标准SQL对GROUP BY的用法，以便选择列表可以引用未在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.

这篇关于如何在laravel中雄辩地进行子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！