Note that it's midnight here and I just got home from a 10 hour code binge at work, so my mind is almost done, but this is how I imagine it could be done. Understand that I know very little of your schema or your current setup. Also know that this approach will require quite a few more queries than @JarekTkaczyk's query will, however the benefits are that it is cleaner, easier to maintain, and it gets the relationships setup in Eloquent which you can reuse infinitely.
We start with the Room. If I read your queries correctly, every room can have many different equipments in it. So we setup that relationship.
$room = Room::find(4);
d($room->equipment); // array of all pieces of equipment in the room
class Room extends Model {
public function equipment()
{
return $this->belongsToMany('Equipment')->withPivot('quantity');
}
}
Since we got our Room set up and we know that it has Equipment in it, let's create the Equipment. Based on your query I noticed that all equipment can belong to a (single) certain category. So we set up that relationship here. I wasn't a big fan of your choice in key names on this one, it deviates from Laravel's recommendation of CamelCase being translated to snake_case). Anyway, we already established that all Equipment belongs to a Room. So we setup the inverse of the relationship we did on the Room here. I noticed that you have a "quantity" field on your pivot table, so you'll see we're pulling that in here too. You'll see the "getCategoryFullName" attribute -- we'll come back to that.
foreach (Equipment::all() as $equipment)
{
d($equipment->equipmentCategory); // will dump out the category that each piece of equipment belongs to.
}
class Equipment extends Model {
protected $with = ['full_name'];
public function equipmentCategory()
{
return $this->belongsTo('EquipmentCategory', 'equipmentcategory_id', 'equipmentcategory_id');
}
public function room()
{
return $this->belongsToMany('Room')->withPivot('quantity');
}
public function getFullNameAttribute()
{
return sprintf(
'%s - %s - %d',
$this->equipmentCategory->equipmentcategory_name,
$this->equipment_name,
$this->pivot->quantity
);
}
}
Lastly, we create the EquipmentCategory, since we already know our Equipment has to belong to it. This one is easy and the only relationship we'll set up here is the inverse of the one we did for the Equipment. This relationship will just list all Equipment that belongs to that EquipmentCategory. (Wasn't a huge fan of your column names on this one either.)
$equipmentCategory = EquipmentCategory::find(7);
d($equipmentCategory->equipment); // array of all Equipment that belongs to EquipmentCategory#7
class EquipmentCategory extends Model {
public function equipment()
{
return $this->hasMany('Equipment', 'equipmentcategory_id', 'equipmentcategory_id');
}
}
Now, the reason we did this, let's go back to the Equipment model. We've set up all relationships between these three objects, essentially we've programmatically accomplished the joins you had written. What this allows us to do is add an easy property onto each piece of Equipment that also gets you the EquipmentCategory name as well as the quantity of that equipment. All we have to do here is call the property.
$equipment = Equipment::find(13);
echo $equipment->full_name; // If I did all this write, this should print out your example of "Audio - Mic - 4"
class Equipment extends Model {
protected $with = ['full_name'];
/* snip snip */
public function getFullNameAttribute()
{
return sprintf(
'%s - %s - %d',
$this->equipmentCategory->equipmentcategory_name,
$this->equipment_name,
$this->pivot->quantity
);
}
}
In order to get a list of all pieces of Equipments' full name, you could do this:
$equipmentList = Equipment::all()->list('full_name');
Want just a list of Equipment from a certain room? No problem (this is why having all your relationships set up is nice).
$room2 = Room::find(2);
$equipmentList = $room2->equipment->list('full_name');
I hope I got all that right. I also wouldn't expect you to even put this to the test because it would be a lot to set up, but I figured I'd just show you another method (and probably the more OOP approach) to get this done. The downside is it will take more than the single query from the join you've already derived :)
