Laravel group query first few records

In Laravel, we often need to query the first few records from the database, such as querying the top five sales products under a product category. In this case, we usually use grouping queries to achieve this.

This article will introduce how to perform group query in Laravel and query the first few records of each group.

1. Preparation

First, we need to have a data table to store product information. Suppose we have created a data table called "products" and inserted some data into it.

2. Writing query statements

We can use Laravel's query builder to write query statements. First, we need to group the products by category, and then query the top five products in each group. The query statement is as follows:

$products = DB::table('products')
            ->select('category', DB::raw('MAX(sales) as max_sales'))
            ->groupBy('category')
            ->orderByDesc('max_sales')
            ->limit(5)
            ->get();

Here we use the select() method to specify the fields to be queried, and the DB::raw() method to calculate the maximum sales volume of each group. The groupBy() method specifies grouping by classification field, the orderByDesc() method sorts by maximum sales in descending order, and the limit() method limits the number of queried records to 5. Finally, use the get() method to execute the query and return a result set with category as the key and maximum sales volume as the value.

3. Processing query results

Next, we need to process the query results to obtain the top five products of each group. We can process the result set in a loop, query the first five items in each group, and add them to a new array.

$result = [];
foreach ($products as $product) {
    $query = DB::table('products')
                ->select('*')
                ->where('category', $product->category)
                ->orderByDesc('sales')
                ->limit(5)
                ->get();
    $result[$product->category] = $query;
}

Inside the loop, we first query all products under each category, then sort by sales volume in descending order, limit the number of query records to 5, and finally add the query results to a new array. After the loop ends, we get a new array with category as the key and query result as the value. This array contains the top five sales products in each category.

4. Output results

Finally, we can traverse the new array and output the query results to the page.

foreach ($result as $category => $products) {
    echo '<h3>'.$category.'</h3>';
    echo '<ul>';
    foreach ($products as $product) {
        echo '<li>' . $product->name . '</li>';
    }
    echo '</ul>';
}

When outputting the results, we first output the name of the category, and then output the name of each product in a ul list.

5. Summary

In this article, we introduced how to use group query in Laravel to query the first few records of each group. By using select(), groupBy(), orderByDesc(), limit() and other methods, we can flexibly write query statements to achieve various complex query requirements. At the same time, we also learned how to use Laravel's query builder to write database query statements, and process and output query results.

The above is the detailed content of Laravel group query first few records. For more information, please follow other related articles on the PHP Chinese website!