Eloquent group by nested relationship

I have 3 models of Step, Tool, Settings. Each step can use multiple tools with multiple settings. There is a 3-way pivot table model which connects them and that model is called StepToolSetting with table Schema:

        Schema::create('step_tool_settings', function (Blueprint $table) {
            $table->id();
            $table->foreignIdFor(Step::class)->constrained();
            $table->foreignIdFor(Tool::class)->constrained();
            $table->foreignIdFor(Setting::class)->nullable()->constrained(); // A step can have a tool without a setting
            $table->string('value')->nullable();
            $table->timestamps();
        });

I am retrieving the tools and settings in the Step model using the following relationship:

class Step extends Model
{
    public function toolSettings(): HasMany
    {
        return $this->hasMany(StepToolSetting::class)->with(['tool', 'setting']);
    }
}

When I call

Step::with(['toolSettings'])->find($id)

It lists each item from the StepToolSetting model as follows:

array:2 [
  0 => array:9 [
    "id" => 1
    "step_id" => 15
    "tool_id" => 1
    "setting_id" => 1
    "value" => "1"
    "tool" => array:8 [▼
      "id" => 1
      "type" => "instrument"
      "name" => "DEWALT DCD771B"
      "model_version" => "DCD771B20V"
      "vendor" => "DEWALT"
      "serial_number" => "KJ12314"
    ]
    "setting" => array:7 [▼
      "id" => 1
      "tool_id" => 1
      "name" => "Bit Size"
      "examples" => "1"
      "default" => "1"
    ]
  ]
  1 => array:9 [▼
    "id" => 2
    "step_id" => 15
    "tool_id" => 1
    "setting_id" => 2
    "value" => "Yes"
    "tool" => array:8 [▼
      "id" => 1
      "type" => "instrument"
      "name" => "DEWALT DCD771B"
      "model_version" => "DCD771B20V"
      "vendor" => "DEWALT"
      "serial_number" => "KJ12314"
    ]
    "setting" => array:7 [▼
      "id" => 2
      "tool_id" => 1
      "name" => "Speed"
      "examples" => "1500 RPM"
      "default" => "1500 RPM"
    ]
  ]
]

As can be seen above, even though for the step there is a single instrument with two settings, it appears as two items. The desired output should have been grouped by the instrument i.e. DEWALT DCD771B How can I retrieve the toolSettings() so that it is grouped by the instrument?

Example of desired output:

[
  [
    "tool" =>  [▼
      "id" => 1
      "type" => "instrument"
      "name" => "DEWALT DCD771B"
      "model_version" => "DCD771B20V"
      "vendor" => "DEWALT"
      "serial_number" => "KJ12314"
      "toolSettings" => [
        0 =>  [▼
          "id" => 1
          "step_id" => 15
          "tool_id" => 1
          "setting_id" => 1
          "value" => "1"
          "setting" =>  [▼
            "id" => 1
            "tool_id" => 1
            "name" => "Bit Size"
            "examples" => "1"
            "default" => "1"
          ]
        ]
        1 => [▼
          "id" => 2
          "step_id" => 15
          "tool_id" => 1
          "setting_id" => 2
          "value" => "Yes"
          "setting" => [▼
            "id" => 2
            "tool_id" => 1
            "name" => "Speed"
            "examples" => "1500 RPM"
            "default" => "1500 RPM"
          ]
        ]
      ]
    ]
  ]
]

Update

I tried using groupBy on the toolSettings using $step->toolSettings->groupBy('tool') but that puts the tool as JSON, instead of nesting the array.

hamzaaslam

3 years ago

Level 1

you can modify the toolSettings method in the Step model to group the settings by the tool:

class Step extends Model
{
    public function toolSettings(): HasMany
    {
        return $this->hasMany(StepToolSetting::class)->with(['tool', 'setting'])
            ->get()
            ->groupBy('tool.id')
            ->map(function ($settings, $toolId) {
                $tool = $settings[0]->tool;
                $tool['toolSettings'] = $settings->toArray();
                return $tool;
            })
            ->values();
    }
}

Here, first load all the tool settings using the get method, then group them by the tool ID using groupBy. We then use map to transform the collection of settings for each tool into an array that includes the tool information and an array of tool settings.

Finally, we use the values method to re-index the array keys and remove any gaps, since map can create gaps in the array keys if the mapping function returns null for some items.

With this modification, when you call Step::with(['toolSettings'])->find($id), the tool settings will be grouped by the tool in the desired format.

2 likes

CLab

3 years ago

Level 3

@hamzaaslam I see the logic but I am getting the following error:

Method Illuminate\Database\Eloquent\Collection::addEagerConstraints does not exist.

I believe I cannot eager load using Step::with(['toolSettings'])->find($id) and will instead have to load it separately? Any way to eager load?

Please or to participate in this conversation.