問題描述

當(dāng)我使用 db raw 時(shí)，它可以工作

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 沒有執(zhí)行

It also works. But orderBy updated_at not executed

我該如何解決?

推薦答案

在我看來(lái)，您使用的 group by 不正確.即使您在查詢之前檢索到了正確的結(jié)果，我也認(rèn)為這是偶然的.Group by 應(yīng)該用于聚合查詢結(jié)果并獲取聚合列值.選擇實(shí)際上未聚合的列如果使用不當(dāng)可能會(huì)很危險(xiǎn).

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.

來(lái)自 5.6 版的 Mysql 文檔:

From the Mysql docs for version 5.6:

MySQL 擴(kuò)展了 GROUP BY 的標(biāo)準(zhǔn) SQL 使用，以便選擇列表可以引用未在 GROUP BY 子句中命名的非聚合列.這意味著前面的查詢?cè)?MySQL 中是合法的.您可以使用此功能通過避免不必要的列排序和分組來(lái)獲得更好的性能.但是，這主要在未在 GROUP BY 中命名的每個(gè)非聚合列中的所有值對(duì)于每個(gè)組都相同時(shí)很有用.服務(wù)器可以自由地從每個(gè)組中選擇任何值，因此除非它們相同，否則選擇的值是不確定的.此外，添加 ORDER BY 子句不會(huì)影響從每個(gè)組中選擇值.結(jié)果集排序發(fā)生在選擇值之后，ORDER BY 不影響服務(wù)器選擇每個(gè)組中的哪些值.

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 開始，默認(rèn) SQL 模式包括 ONLY_FULL_GROUP_BY 標(biāo)志，它將:

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.

出于教育目的，您應(yīng)該能夠像這樣使用 Laravel 實(shí)現(xiàn)完全相同的查詢(未經(jīng)測(cè)試且不使用表別名)，但我會(huì)避免使用它:

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

但在我看來(lái)，您想要做的似乎是將所有商店與您想要的類別中的產(chǎn)品放在一起.所以最 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();

通過查看您的查詢，我可能做出了錯(cuò)誤的假設(shè)，但目前沒有多大意義......無(wú)論如何我都會(huì)從那里開始.

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.

這篇關(guān)于如何在 Laravel eloquent 中進(jìn)行子查詢?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！