如何对含三级关联的实体执行链式groupBy并实现结果嵌套？

Got it, let's work through this nested grouping problem together. The raw groupBy on your query only gets you partway—since we need a structured hierarchy (FurnitureMasterTypes → FurnitureTypes → Countries → FurnitureItem), we'll combine eager loading to fetch all related data efficiently, then build the nested structure in the application layer (database-level groupBy is better for aggregations, not this kind of entity nesting).

Step 1: Eager Load All Relationships First #

First, make sure you fetch all related entities in one go to avoid the N+1 query problem. Assuming you're using an ORM like Eloquent (adjust syntax for your framework if needed):

// Fetch all FurnitureItems with their full nested relationships
$furnitureItems = FurnitureItem::with([
    'furnitureTypes.furnitureMasterTypes', // Chain the master type through type
    'countries' // Direct country relation on item
])->get();

Step 2: Build the Nested Hierarchy Manually #

This approach gives you full control over the structure. Loop through each item and build out the hierarchy level by level:

$hierarchy = [];

foreach ($furnitureItems as $item) {
    // Skip items with missing relationships (add null checks if needed)
    if (!$item->furnitureTypes || !$item->furnitureTypes->furnitureMasterTypes || !$item->countries) {
        continue;
    }

    // 1. Level: FurnitureMasterTypes
    $masterTypeId = $item->furnitureTypes->furnitureMasterTypes->id;
    if (!isset($hierarchy[$masterTypeId])) {
        $hierarchy[$masterTypeId] = [
            'id' => $masterTypeId,
            'name' => $item->furnitureTypes->furnitureMasterTypes->name, // Replace with your actual column
            'furniture_types' => []
        ];
    }

    // 2. Level: FurnitureTypes (under Master Type)
    $typeId = $item->furnitureTypes->id;
    $masterType = &$hierarchy[$masterTypeId];
    if (!isset($masterType['furniture_types'][$typeId])) {
        $masterType['furniture_types'][$typeId] = [
            'id' => $typeId,
            'name' => $item->furnitureTypes->name, // Replace with your column
            'countries' => []
        ];
    }

    // 3. Level: Countries (under Type)
    $countryId = $item->countries->id;
    $type = &$masterType['furniture_types'][$typeId];
    if (!isset($type['countries'][$countryId])) {
        $type['countries'][$countryId] = [
            'id' => $countryId,
            'name' => $item->countries->name, // Replace with your column
            'furniture_items' => []
        ];
    }

    // 4. Level: FurnitureItem (under Country)
    $country = &$type['countries'][$countryId];
    $country['furniture_items'][] = [
        'id' => $item->id,
        'name' => $item->name,
        // Add any other fields you need from FurnitureItem
    ];
}

// Convert associative arrays to indexed arrays for cleaner output
$hierarchy = array_values($hierarchy);
foreach ($hierarchy as &$masterType) {
    $masterType['furniture_types'] = array_values($masterType['furniture_types']);
    foreach ($masterType['furniture_types'] as &$type) {
        $type['countries'] = array_values($type['countries']);
    }
}

Step 3: Alternative (Using Collection Methods for Cleaner Code) #

If you're using Laravel, you can leverage collection methods like groupBy and map to build the hierarchy in a more declarative way:

$hierarchy = FurnitureItem::with([
    'furnitureTypes.furnitureMasterTypes',
    'countries'
])->get()
    // Group by master type ID
    ->groupBy(fn($item) => $item->furnitureTypes->furnitureMasterTypes->id)
    ->map(function ($masterGroup) {
        // Group each master type's items by furniture type ID
        return $masterGroup->groupBy(fn($item) => $item->furnitureTypes->id)
            ->map(function ($typeGroup) {
                // Group each type's items by country ID
                return $typeGroup->groupBy(fn($item) => $item->countries->id)
                    ->map(function ($countryGroup) {
                        // Map country group to item data
                        $items = $countryGroup->map(fn($item) => [
                            'id' => $item->id,
                            'name' => $item->name
                        ])->values();
                        // Attach country details
                        $country = $countryGroup->first()->countries;
                        return [
                            'id' => $country->id,
                            'name' => $country->name,
                            'furniture_items' => $items
                        ];
                    })->values();
            })
            ->map(function ($countries) {
                // Attach furniture type details
                $type = $countries->first()['furniture_items']->first()->furnitureTypes;
                return [
                    'id' => $type->id,
                    'name' => $type->name,
                    'countries' => $countries
                ];
            })->values();
    })
    ->map(function ($types) {
        // Attach master type details
        $masterType = $types->first()['countries']->first()['furniture_items']->first()->furnitureTypes->furnitureMasterTypes;
        return [
            'id' => $masterType->id,
            'name' => $masterType->name,
            'furniture_types' => $types
        ];
    })->values();

Key Notes #

• Always add null checks for relationships if there's a chance any entity could be missing (e.g., a FurnitureItem without a FurnitureTypes association).
• Database-level groupBy isn't ideal here because it's designed for aggregating values (like counts or sums), not nesting entire entity records. Fetching all data first and structuring it in the app layer is more straightforward for this use case.

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