Replace the related model collection array with a new formatted one

user_7603806 0
Viewed 205

I have an order, lamp and lamp_order table. Currently I fetch the Orders and it's Lamp relationships. I also fetch the Images that are related to the Lamps like this:

 $orders = Order::with('lamps.image')->get();

I count how many times a Lamp is related to the Order grouped by room like this:

$lamps = DB::table('lamp_order')
            ->where('order_id', $order->id)
            ->select('lamp_id', 'order_id', 'room', DB::raw('count(*) as total'))
            ->groupBy('room')
            ->groupBy('lamp_id')
            ->get();

I now want to add the count and room name to the Lamp collection so I did this:

$foundLamp = Lamp::find($lamp->lamp_id);
            $collection = collect($foundLamp);
            $collection->put('count', $lamp->total);
            $collection->put('room', $lamp->room);
            $formattedLampsArray[] = $collection;

How can I replace the related Lamp collection from the Order with the new one I just formatted? This is the whole function:

  public function index()
    {
        // Fetch orders with their relationships relationships. 
        $orders = Order::with('lamps.image')->get();

        foreach ($orders as $order) {
            $lamps = DB::table('lamp_order')
                ->where('order_id', $order->id)
                ->select('lamp_id', 'order_id', 'room', DB::raw('count(*) as total'))
                ->groupBy('room')
                ->groupBy('lamp_id')
                ->get();

            $formattedLampsArray = [];

            foreach ($lamps as $lamp) {
                $foundLamp = Lamp::find($lamp->lamp_id);
                $collection = collect($foundLamp);
                $collection->put('count', $lamp->total);
                $collection->put('room', $lamp->room);
                $formattedLampsArray[] = $collection;
            }
        }

        return response()->json([
            'orders' => $orders
        ], 200);
    }

Basically I now would like to add the $formattedLampsArray to each $order. So I can easily get the new formatted related lamps for each order in the front-end. Does anyone know how I can 'replace' the lamps with the new ones?

The result would preferably look like this:

'id' => 1,
'name' => 'John Doe'
'email' => 'johndoe@gmail.com'
'notes' => 'Lorem ipsum dolor sit amet'
'created_at' => '2020-04-02 12:32:30' 
'updated_at' => '2020-04-02 12:32:30'
'lamps_count' => 20
'grouped_lamps' => [
  [
   'id' => 9
   'name' => 'Lamp 1'
   'fitting' => 'E14'
   'shape' => 'Candle'
   'room' => 'Hall'
   'count' => '5'
  ],
  [
   'id' => 9
   'name' => 'Lamp 1'
   'fitting' => 'E14'
   'shape' => 'Candle'
   'room' => 'Kitchen'
   'count' => '5'
  ],
  [
   'id' => 11
   'name' => 'Lamp 2'
   'fitting' => 'E27'
   'shape' => 'Pear'
   'room' => 'Livingroom'
   'count' => '5'
  ],
  [
   'id' => 12
   'name' => 'Lamp 3'
   'fitting' => 'E27'
   'shape' => 'Pear'
   'room' => 'Sleepingroom'
   'count' => '5'
  ],
];
eloquent laravel model collections
2 Answers

I think this is what you looking for
There is a withCount() method that does ->select('lamp_id', 'order_id', 'room', DB::raw('count(*) as total')) job for you
https://laravel.com/docs/5.8/eloquent-relationships#counting-related-models

Note that because ->lamps property is actually a relation we can't mutate it so we make it hidden and make a new property named grouped_lamps

public function index()
{
    $orders = Order::withCount('lamps')
        ->with('lamps.image')
        ->get()->map(static function (Order $order) {
            $order->lamps->each(static function (Lamp $lamp) {
                $lamp->room = $lamp['pivot']['room'];
            });

            return $order;
        })->each(function (Order $order) {
            $order->grouped_lamps = $order->lamps->groupBy(['room', 'id']);
            $order->makeHidden('lamps');

            return $order;
        });

    return response()->json([
        'orders' => $orders,
    ], 200);
}

Edit

I'm not certain if this is what you looking for but here we go

First we need to fetch that extra room column with our relation so make sure that in your Order model you add ->withPivot('room') like so 

public function lamps()
{
    return $this->belongsToMany(Lamp::class, 'lamp_order', 'lamp_id', 'order_id')
        ->withPivot('room');
}

And here is the code, we no longer need to have grouped_lamps

public function index()
{
    $orders = Order::withCount('lamps')
        ->with('lamps.image')
        ->get()->map(function (Order $order) {
            $order->lamps->each(static function (Lamp $lamp) use ($order) {
                $lamp->room = $lamp['pivot']['room'];
                $lamp->count = $order->lamps_count;
            });

            return $order;
        });

    return response()->json([
        'orders' => $orders,
    ], 200);
}

Hope it helps

Final Edit
Ok by reviewing my code I found out that I did an honest mistake

the lamps relation foreign_key and local_key placements were wrong this is the correct form 

public function lamps()
{
    return $this->belongsToMany(Lamp::class, 'lamp_order', 'order_id', 'lamp_id')
        ->withPivot('room');
}

And here is the new version of the code
Notice that If you are fine with previous format you don't need to use below code

public function index()
{
    $orders = Order::withCount('lamps')
        ->with('lamps.image')
        ->get()
        ->map(static function (Order $order) {
            $order->lamps->each(static function (Lamp $lamp) {
                $lamp->room = $lamp['pivot']['room'];
            });

            return $order;
        })->each(function (Order $order) {
            $groupedLamps = $order->lamps->groupBy('room');
            $order->lamps->each(function (Lamp $lamp) use ($groupedLamps) {
                $lamp->count = $groupedLamps[$lamp->room]->count();
            });
        });

    return response()->json([
        'orders' => $orders,
    ], 200);
}

And you will get result like this 

{
    "orders": [
        {
            "id": 1,
            "title": "ut fugit facilis",
            "lamps_count": 5, // Total lamps owned by this order
            "lamps": [
                {
                    "title": "ab sunt nostrum",
                    "room": "kitchen",
                    "count": 3, // Total number of kitchen lamps of order_id 1
                    "pivot": {
                        "order_id": 1,
                        "lamp_id": 1,
                        "room": "kitchen"
                    }
                },
                {
                    "title": "omnis dicta rerum",
                    "room": "hall",
                    "count": 2, // Total number of hall lamps of order_id 1
                    "pivot": {
                        "order_id": 1,
                        "lamp_id": 2,
                        "room": "hall"
                    }
                },
                {
                    "title": "eligendi deserunt et",
                    "room": "hall",
                    "count": 2, // Total number of hall lamps of order_id 1
                    "pivot": {
                        "order_id": 1,
                        "lamp_id": 3,
                        "room": "hall"
                    }
                },
                {
                    "title": "modi ad ea",
                    "room": "kitchen",
                    "count": 3, // Total number of kitchen lamps of order_id 1
                    "pivot": {
                        "order_id": 1,
                        "lamp_id": 4,
                        "room": "kitchen"
                    }
                },
                {
                    "title": "eos neque consequatur",
                    "room": "kitchen",
                    "count": 3, // Total number of kitchen lamps of order_id 1
                    "pivot": {
                        "order_id": 1,
                        "lamp_id": 5,
                        "room": "kitchen"
                    }
                }
            ]
        },
        {
            "id": 2,
            "title": "sapiente velit quas",
            "lamps_count": 2,
            "lamps": [
                {
                    "title": "eligendi deserunt et",
                    "room": "kitchen",
                    "count": 2,
                    "pivot": {
                        "order_id": 2,
                        "lamp_id": 3,
                        "room": "kitchen"
                    }
                },
                {
                    "title": "modi ad ea",
                    "room": "kitchen",
                    "count": 2,
                    "pivot": {
                        "order_id": 2,
                        "lamp_id": 4,
                        "room": "kitchen"
                    }
                }
            ]
        },
        {
            "id": 3,
            "title": "itaque sint voluptas",
            "lamps_count": 4,
            "lamps": [
                {
                    "title": "omnis dicta rerum",
                    "room": "kitchen",
                    "count": 1,
                    "pivot": {
                        "order_id": 3,
                        "lamp_id": 2,
                        "room": "kitchen"
                    }
                },
                {
                    "title": "eligendi deserunt et",
                    "room": "living_room",
                    "count": 1,
                    "pivot": {
                        "order_id": 3,
                        "lamp_id": 3,
                        "room": "living_room"
                    }
                },
                {
                    "title": "modi ad ea",
                    "room": "hall",
                    "count": 2,
                    "pivot": {
                        "order_id": 3,
                        "lamp_id": 4,
                        "room": "hall"
                    }
                },
                {
                    "title": "eos neque consequatur",
                    "room": "hall",
                    "count": 2,
                    "pivot": {
                        "order_id": 3,
                        "lamp_id": 5,
                        "room": "hall"
                    }
                }
            ]
        }
    ]
}
user_93780770

The answer of AH.Pooladvand helped me in the right direction but it didn't match my requirements. That's why I still gave it the +50 bounty award. A friend of me told me that I should query the pivot table directly. I combined both answers into this piece of code.

I need to mention that it isn't efficient yet, when I have the full answer I will post it here. But I want to post this to help others in the meantime.

   public function index()
    {

        // Get all Orders and count the amount of related Lamps.
        $orders = Order::withCount('lamps')
            ->with('lamps.image') // Also fetch related Image of the Lamp
            ->get()
            ->map(static function (Order $order) {
                // For each lamp count how many times it occurs per room.
                $order->lamps->each(static function (Lamp $lamp) {
                    $countedLamps = DB::table('lamp_order')
                        ->select(DB::raw('*, count(*) as count_lamps'))
                        ->groupBy('lamp_id', 'room')
                        ->get();

                    foreach ($countedLamps as $countedLamp) {
                        // If lamp_id and room are the same then add the count and room to the Lamp. 
                        if ($countedLamp->lamp_id == $lamp['id'] && $countedLamp->room == $lamp['pivot']['room']) {
                            $lamp->count = $countedLamp->count_lamps;
                            $lamp->room = $countedLamp->room;
                        }
                    }
                });

                // Remove duplicate Lamps an create an new attribute in the collection array.
                $collection = collect($order->lamps);
                $order->ordered_lamps = $collection->unique()->values()->all();
                // Make unordered Lamps array hidden.
                $order->makeHidden('lamps');
                return $order;
            });

        return response()->json([
            'orders' => $orders,
        ], 200);
    }

I fetch all the Orders,immediately count the amount of Lamps in that Order and get the related Image of that Lamp. For each Order I count the lamps per room and lamp_id. For each of this $groupedLamp I do a dirty check to add the count and room variable to the lamp object itself. Then in the end I remove the duplicates from the lamp array, make it hidden and create an order_lamps variable. Any improvements are welcome. I will edit this answer when it is optimized.

user_76038060
Related