How to join models in Laravel 5?

Question

I have three tables, with a structure like this (they are in an MySQL DB) connected to my Laravel 5 API with Eloquent models:

# build_sets table
| id | title | description |

# parts table
| id | title | description | color |

# build_set_parts table
| id | build_set_id | part_id | amount | special_info |

At the moment I do a query like this:

$buildSets = array();

foreach(BuildSet::with('parts')->get() as $buildSet) {
    $temp = json_decode($buildSet);

    $temp->parts = $buildSet->parts;

    $buildSets[] = $temp;
}

return $buildSets;

And my models look like this:

class BuildSet extends Model
{
    protected $table = 'build_sets';
    protected $hidden = ['created_at', 'updated_at'];

    public function parts()
    {
        return $this->hasMany('App\Models\BuildSetPart');
    }
}

class Part extends Model
{
    protected $table = 'parts';
    protected $hidden = ['id', 'created_at', 'updated_at'];

    public function buildSets()
    {
        return $this->hasMany('App\Models\BuildSet');
    }
}

class BuildSetPart extends Model
{
    protected $table = 'build_set_parts';
    protected $hidden = ['id', 'build_set_id', 'part_id', 'created_at', 'updated_at'];

    public function buildSet()
    {
        return $this->belongsTo('App\Models\BuildSet');
    }

    public function part()
    {
        return $this->belongsTo('App\Models\Part');
    }
}

I get a result like this (JSON response):

[
  {
    "id": 1,
    "title": "Build set 1",
    "description": "This is a small build set.",
    "parts": [
      {
        "amount": 150,
        "special_info": ""
      },
      {
        "amount": 400,
        "special_info": "Extra strong"
      },
      {
        "amount": 25,
        "special_info": ""
      }
    ]
  },
  {
    "id": 2,
    "title": "Build set 2",
    "description": "This is a medium build set.",
    "parts": [
      {
        "amount": 150,
        "special_info": ""
      },
      {
        "amount": 400,
        "special_info": "Extra strong"
      },
      {
        "amount": 25,
        "special_info": ""
      },
      {
        "amount": 25,
        "special_info": ""
      },
      {
        "amount": 25,
        "special_info": ""
      }
    ]
  }
]

As you can see I'm missing the title, description and color in the "parts" array which is included in the build set. So my question is, how to I add the title and the color to my response? Can I do that by using Eloquent models, or do I have to make my own DB query where I get all build sets and then iterate them and get all the parts and build set parts and then merge that result and add it to the build set.

Any one got a good solution which will give me items in the parts array formatted like this:

[
  {
    "title": "Part 1",
    "color": "Red",
    "amount": 25,
    "special_info": ""
  },
  {
    "title": "Part 2",
    "color": "Green",
    "amount": 75,
    "special_info": ""
  }
]

Answer 1

From you model, You got Many to Many relationship between BuildSet and Part.

Therefore, In BuildSet, you can use hasManyThrough relationship.

Model BuildSet

public function parts()
{
    return $this->hasManyThrough('App\Models\Part', 'App\Models\BuildSetPart');
}

source: http://laravel.com/docs/5.0/eloquent#relationships

Anyway, if you want to keep the relationship between BuildSet and BuildSetPart.

I suggest you to use this instead.

public function buildSetPart()
{
   return $this->hasMany('App\Models\BuildSetPart');
}

note: right now, you got both parts() and buildSetPart() in BuildSet model

After you create the relationship, you can replace you code

$buildSets = array();

foreach(BuildSet::with('parts')->get() as $buildSet) {
    $temp = json_decode($buildSet);

    $temp->parts = $buildSet->parts;

    $buildSets[] = $temp;
}

return $buildSets;

with

return  BuildSet::with('parts')->get();

or, if you want the output in JSON format

return  BuildSet::with('parts')->toJson();

source: http://laravel.com/docs/5.0/eloquent#collections

Answer 2

I found a solution that is almost as I wanted to do it, I tried mininoz solution and found out that I get some problems with the pivot table relation.

I now do this query:

return BuildSet::with('parts')->get();

And my models now looks like this, note the "->withPivot()" function I added at the end, it allows me to "access" the columns in my pivot table:

class BuildSet extends Model
{
    protected $table = 'build_sets';
    protected $hidden = ['created_at', 'updated_at'];

    public function parts()
    {
        return $this->belongsToMany('App\Models\BuildSetPart', 'build_set_parts')->withPivot('amount', 'special_info');
    }
}

class Part extends Model
{
    protected $table = 'parts';
    protected $hidden = ['id', 'created_at', 'updated_at'];

    public function buildSets()
    {
        return $this->belongsToMany('App\Models\BuildSet', 'build_set_parts')->withPivot('amount', 'special_info');
    }
}

class BuildSetPart extends Model
{
    protected $table = 'build_set_parts';
    protected $hidden = ['id', 'build_set_id', 'part_id', 'created_at', 'updated_at'];
}

I now get a extended parts response in my build set response (Note, I only show the parts data here, the build set data is as before with an "parts" array):

[
  {
    "title": "Part 1",
    "color": "Red",
    "pivot": {
      "build_set_id": 1,
      "part_id": 1,
      "amount": 25,
      "special_info": ""
    }
  },
  {
    "title": "Part 2",
    "color": "Green",
    "pivot": {
      "build_set_id": 1,
      "part_id": 2,
      "amount": 55,
      "special_info": ""
    }
  }
]

This response contains all I need, but I need to transform if I want to get it all on the same "level" in the JSON response hierarchy.