Laravel按sizebarcode分组时排除空值聚合并保留空值产品

Hey there! Let's figure out how to solve this grouping issue in your Laravel setup. The core problem here is that you want to group products by sizebarcode only when the field has a value, while keeping every product with a null sizebarcode as individual entries instead of lumping them into a single group. Here are a couple of solid approaches to make this work:

Approach 1: Database-Level Union Query (For Paging Grouped Results) #

This method splits your query into two parts: one for grouping non-null sizebarcode values, and another for fetching all null sizebarcode products. We then combine them using a union and paginate the final result.

First, define the two sub-queries, making sure they return the same set of fields:

// Get grouped products with non-null sizebarcode
$groupedQuery = Category::where('slug', $slug)
    ->first()
    ->products_front()
    ->whereNotNull('sizebarcode')
    ->select([
        'id', 
        'description', 
        'barcode', 
        'sizebarcode', 
        'price',
        \DB::raw('COUNT(*) as product_count') // Optional: add a count of items in each group
    ])
    ->groupBy('sizebarcode');

// Get all products with null sizebarcode (no grouping)
$nullSizeQuery = Category::where('slug', $slug)
    ->first()
    ->products_front()
    ->whereNull('sizebarcode')
    ->select([
        'id', 
        'description', 
        'barcode', 
        'sizebarcode', 
        'price',
        \DB::raw('1 as product_count') // Match the field from grouped query
    ]);

// Combine and paginate
$combinedResults = $groupedQuery->union($nullSizeQuery)->paginate(12);

Notes: #

• The product_count field is added to ensure both queries return identical column sets (required for unions). For null entries, we set it to 1 since each is an individual "group".
• If you don't need a count, you can omit that field from both queries (just make sure the selected columns match exactly).

Approach 2: Collection-Level Processing (For Paging Individual Products First) #

If you want to keep the original product pagination logic but restructure the results into groups afterward, you can fetch all paginated products first, then manipulate the collection:

// Get paginated products as you originally did
$products = Category::where('slug', $slug)->first()->products_front()->paginate(12);

// Initialize a collection to hold your final grouped structure
$finalGroups = collect();

// Add grouped non-null sizebarcode products
$nonNullGroups = $products->whereNotNull('sizebarcode')->groupBy('sizebarcode');
$finalGroups = $finalGroups->merge($nonNullGroups);

// Add each null sizebarcode product as its own "group"
$products->whereNull('sizebarcode')->each(function ($product) use (&$finalGroups) {
    $finalGroups->push(collect([$product]));
});

// Now $finalGroups contains:
// - One group per unique non-null sizebarcode
// - One single-product group for each null sizebarcode entry

Notes: #

• This approach paginates individual products first, then groups them. So your pagination will reflect the total number of products, not the number of groups.
• Use this if you need to maintain the original 12-products-per-page behavior but display them in the grouped format you want.

Which Approach to Choose? #

• Use Approach 1 if you want to paginate the groups themselves (e.g., 12 groups per page, regardless of how many products are in each group).
• Use Approach 2 if you need to keep paginating individual products but organize them into groups after fetching.

内容的提问来源于stack exchange，提问作者Benfactor