mysql - Laravel 查询构建器返回的行数超出应有的行数

Question

表结构如下:

 properties
 ------------
 id
 property_name

 buildings
 -------------
 id
 building_name
 property_id

 floors
 -------------
 id
 floor_coode
 building_id

 units
 -------------
 id
 unit_number
 floor_id

 amenities
 ------------
 id
 amenity_name
 amenity_value
 unit_id

 categories
 -----------
 id
 category_name
 parent_id

amenity_categories
 ------------
amenity_id
category_id

现在，我尝试运行以下查询:

        $query = \DB::table('amenities')
            ->join('units', 'units.id', 'amenities.unit_id')
            ->join('floors', 'floors.id', 'units.floor_id')
            ->join('buildings', 'buildings.id', 'floors.building_id')
            ->join('properties', 'properties.id', 'buildings.property_id')
            ->leftjoin('amenity_category', 'amenities.id', 'amenity_category.amenity_id')
            ->leftjoin('categories', 'categories.id', 'amenity_category.category_id')
            ->select('amenities.id as ame_id', 'amenities.amenity_name', 'amenities.amenity_value','units.id as unit_id', 'units.unit_number','amenity_category.*','categories.parent_id as cat_parent_id')
            ->where('buildings.id',$building_id)
            ->orderBy('units.unit_number','asc');

        $data = $query->get();

这样，它返回的行比应有的多，例如让我们看看特定单位的 block :107

[300] => stdClass Object
                (
                    [ame_id] => 2
                    [amenity_name] => CU
                    [amenity_value] => 20
                    [unit_id] => 1
                    [unit_number] => 107
                    [amenity_id] => 2
                    [category_id] => 8
                    [cat_parent_id] => 5
                )

            [301] => stdClass Object
                (
                    [ame_id] => 3
                    [amenity_name] => VR
                    [amenity_value] => 50
                    [unit_id] => 1
                    [unit_number] => 107
                    [amenity_id] => 
                    [category_id] => 
                    [cat_parent_id] => 
                )

            [302] => stdClass Object
                (
                    [ame_id] => 4
                    [amenity_name] => SF
                    [amenity_value] => 320
                    [unit_id] => 1
                    [unit_number] => 107
                    [amenity_id] => 4
                    [category_id] => 6
                    [cat_parent_id] => 5
                )

            [303] => stdClass Object
                (
                    [ame_id] => 1
                    [amenity_name] => BO
                    [amenity_value] => 20
                    [unit_id] => 1
                    [unit_number] => 107
                    [amenity_id] => 1
                    [category_id] => 6
                    [cat_parent_id] => 5
                )

            [304] => stdClass Object
                (
                    [ame_id] => 4
                    [amenity_name] => SF
                    [amenity_value] => 320
                    [unit_id] => 1
                    [unit_number] => 107
                    [amenity_id] => 4
                    [category_id] => 9
                    [cat_parent_id] => 5
                )

            [305] => stdClass Object
                (
                    [ame_id] => 1
                    [amenity_name] => BO
                    [amenity_value] => 20
                    [unit_id] => 1
                    [unit_number] => 107
                    [amenity_id] => 1
                    [category_id] => 8
                    [cat_parent_id] => 5
                )

            [306] => stdClass Object
                (
                    [ame_id] => 4
                    [amenity_name] => SF
                    [amenity_value] => 320
                    [unit_id] => 1
                    [unit_number] => 107
                    [amenity_id] => 4
                    [category_id] => 10
                    [cat_parent_id] => 5
                )

            [307] => stdClass Object
                (
                    [ame_id] => 1
                    [amenity_name] => BO
                    [amenity_value] => 20
                    [unit_id] => 1
                    [unit_number] => 107
                    [amenity_id] => 1
                    [category_id] => 9
                    [cat_parent_id] => 5
                )

            [308] => stdClass Object
                (
                    [ame_id] => 4
                    [amenity_name] => SF
                    [amenity_value] => 320
                    [unit_id] => 1
                    [unit_number] => 107
                    [amenity_id] => 4
                    [category_id] => 11
                    [cat_parent_id] => 5
                )

            [309] => stdClass Object
                (
                    [ame_id] => 1
                    [amenity_name] => BO
                    [amenity_value] => 20
                    [unit_id] => 1
                    [unit_number] => 107
                    [amenity_id] => 1
                    [category_id] => 10
                    [cat_parent_id] => 5
                )

            [310] => stdClass Object
                (
                    [ame_id] => 2
                    [amenity_name] => CU
                    [amenity_value] => 20
                    [unit_id] => 1
                    [unit_number] => 107
                    [amenity_id] => 2
                    [category_id] => 6
                    [cat_parent_id] => 5
                )

在这里，您可以看到实际上只有 4 个不同的 ame_id，范围从 1-4 以及它应该如何返回。我哪里做错了？另外，删除该数据透视表并不是一个主意，因为我还需要加载该关系数据。

Answer 1

您还可以使用 groupby 来获取唯一的行

$query = \DB::table('amenities')
    ->join('units', 'units.id', 'amenities.unit_id')
    ->join('floors', 'floors.id', 'units.floor_id')
    ->join('buildings', 'buildings.id', 'floors.building_id')
    ->join('properties', 'properties.id', 'buildings.property_id')
    ->leftjoin('amenity_category', 'amenities.id', 'amenity_category.amenity_id')
    ->leftjoin('categories', 'categories.id', 'amenity_category.category_id')
    ->select('amenities.id as ame_id', 'amenities.amenity_name', 'amenities.amenity_value','units.id as unit_id', 'units.unit_number','amenity_category.*','categories.parent_id as cat_parent_id')
    ->where('buildings.id',$building_id)
    ->orderBy('units.unit_number','asc')
    ->groupby('amenities.id');

$data = $query->get();