Suggestions for Returning JSON Collection from Within Date Collection

I have an interesting request from a friend that I know I've seen examples of around here and there, but am not currently quite sure how to implement it in the controller (or the blade, but I can work on that).

I have a collection of loads currently, which all pulled through this query:

        $query = DB::table('loads')
                  ->leftJoin('shipments', 'loads.shipmentID', '=', 'shipments.id')
                  ->leftJoin('equipment as tractor', 'loads.tractorID', '=', 'tractor.id')
                  ->leftJoin('employees', 'loads.driverID', '=', 'employees.id')
                  ->leftJoin('customers as shipperCustomer', 'shipments.ship_from', '=', 'shipperCustomer.id')
                  ->leftJoin('customers as consigneeCustomer', 'shipments.ship_to', '=', 'consigneeCustomer.id')
                  ->select('loads.id','shipments.pro_number','shipments.id','employees.last_name as driver','tractor.unit_id as tractor','loads.dateTime','shipperCustomer.customer_name as ShipperCustomerName','consigneeCustomer.customer_name as ConsigneeCustomerName','shipments.cn_shipfromName as ShipperName','shipments.cn_shiptoName as ConsigneeName');

        if($request->type){
            $query->where('loads.type', $request->type);
        }

        if($request->status){
            $query->where('loads.status', $request->status);
        }

        $loads = $query->get();

        return response()->json([
                    ['loads'=>$loads],
                    ['time'=>Carbon::now()]
                ]);

And this returns the following response:

[{"loads":[{"id":18296,"pro_number":232231,"driver":"Bettale","tractor":null,"dateTime":"2018-08-27 07:00:00","ShipperCustomerName":"ELECTROLUX HOME PRODUCTS","ConsigneeCustomerName":"LINN STAR","ShipperName":null,"ConsigneeName":null},
{"id":18298,"pro_number":232233,"driver":"Bettale","tractor":null,"dateTime":"2018-08-28 07:00:00","ShipperCustomerName":"ELECTROLUX HOME PRODUCTS","ConsigneeCustomerName":"LINN STAR","ShipperName":null,"ConsigneeName":null},{"id":18297,"pro_number":232232,"driver":null,"tractor":null,"dateTime":"2018-08-27 07:00:00","ShipperCustomerName":"JBS USA\/ GREELEY PLANT","ConsigneeCustomerName":"CONTAINER BUYERS","ShipperName":null,"ConsigneeName":null}]},{"time":{"date":"2018-08-27 11:44:21.304553","timezone_type":3,"timezone":"America\/Denver"}}]

Maybe this is right, I'm not sure at the moment, but what I would, in the end, like is to be able to print out into a table like the following:

-(row 1) August 27, 2018

--(row 2) Load ID: 18296 | Pro Number 232231 --(row 3) Load ID: 18297 | Pro Number 232232

-(row 4) August 28, 2018

--(row 5) Load ID: 18298 | Pro Number 232233

How would one suggest going about this? I am completely open for any suggestions, I've never implemented something like this before and am not even sure quite where to start.

I'd appreciate any help with this!

Thanks - Matt

jlrdw

7 years ago

Level 75

Instead of json just get results and loop as needed, no need for json here.

Cronix

7 years ago

Level 67

@jlrdw You assume you know how he's using the result. It's obviously not being sent to a view.

@mprythero Look into groupBy('loads.dateTime')

jlrdw

7 years ago

Level 75

Ah:

Taking only 2 loads as example I have:

    public function testJson3()
    {

        $data = '[{  
    "id":18296, "pro_number":232231, "driver":"Bettale", "tractor":null, "dateTime":"2018-08-27 07:00:00", "ShipperCustomerName":"ELECTROLUX HOME PRODUCTS", "ConsigneeCustomerName":"LINN STAR", "ShipperName":null, "ConsigneeName":null
},
{  
    "id":18298, "pro_number":232233, "driver":"Bettale", "tractor":null, "dateTime":"2018-08-28 07:00:00", "ShipperCustomerName":"ELECTROLUX HOME PRODUCTS", "ConsigneeCustomerName":"LINN STAR", "ShipperName":null, "ConsigneeName":null
}]';
        $loads = json_decode($data, true);
        $keys = array_keys($loads);

        for ($i = 0; $i < count($loads); $i++) {

            echo $keys[$i] . "{<br>";

            foreach ($loads[$keys[$i]] as $key => $value) {

                echo $key . " : " . $value . "<br>";
            }

            echo "}<br>";
        }
    }

Gives:

0{
id : 18296
pro_number : 232231
driver : Bettale
tractor : 
dateTime : 2018-08-27 07:00:00
ShipperCustomerName : ELECTROLUX HOME PRODUCTS
ConsigneeCustomerName : LINN STAR
ShipperName : 
ConsigneeName : 
}
1{
id : 18298
pro_number : 232233
driver : Bettale
tractor : 
dateTime : 2018-08-28 07:00:00
ShipperCustomerName : ELECTROLUX HOME PRODUCTS
ConsigneeCustomerName : LINN STAR
ShipperName : 
ConsigneeName : 
}

But when looping over, you need to insert the logic to format the date as needed.

Geeze looping multidimensional are tricky, I don't know how with loads inserted like you had

[{"loads":[{"id":18296,"pro_number":232231,....

I had to drop that.

I try to stay away from json, I'd rather use a simple array, i.e.,

18298|232233|Bettale etc
// with pipe as separator

I have never had problems using the pipe as a separator.

After all json is still just an array.

And @Cronix I know OP will need a group by or a group by, order by combination. I was just saying during the looping to display the final data OP will need to insert the correct logic to format the date as desired.

OP probably knows that already.

Also how would you loop that starting with

[{"loads":[{"id":18296,"pro_number":232231,....

I did not figure it out, but you are real good on the json, array stuff.

Please or to participate in this conversation.