php – Laravel – GroupBy as Alias-ThrowExceptions

Exception or error:

I have the following in one of my functions but would like to refer to it as an alias named Date:

$loads = $query->get()->groupBy(function($item){
    return Carbon::parse($item->dateTime)->format('F j, Y');
});

This $query starts off as: $query = DB::table('loads')...

and the field dateTime is a timestamp.

Is there a proper way to do this to include the alias? Thanks!

//Update with entire Controller Function:

public function apiList(Request $request){
    $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);
    }

    if($request->type == 1 && $request->status == 2){
        $query->whereIn('shipmentID', function ( $query ) {
            $query->select('shipmentID')->from('loads')->groupBy('shipmentID')->havingRaw('count(*) = 1');
        });
    }

    $loads = $query->get()->groupBy(function($item)
                {
                  return Carbon::parse($item->dateTime)->format('F j, Y');
                });

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

//UPDATE TO SHOW CURRENT RETURNED DATA:

enter image description here


///

Unfortunately, I get the following:

“SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘truckin.loads.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select loads.id, shipments.pro_number, shipments.id, employees.last_name as driver, tractor.unit_id as tractor, loads.dateTime as loadsDT, shipperCustomer.customer_name as ShipperCustomerName, consigneeCustomer.customer_name as ConsigneeCustomerName, shipments.cn_shipfromName as ShipperName, shipments.cn_shiptoName as ConsigneeName from loads left join shipments on loads.shipmentID = shipments.id left join equipment as tractor on loads.tractorID = tractor.id left join employees on loads.driverID = employees.id left join customers as shipperCustomer on shipments.ship_from = shipperCustomer.id left join customers as consigneeCustomer on shipments.ship_to = consigneeCustomer.id where loads.type = 1 and loads.status = 2 and shipmentID in (select shipmentID from loads group by shipmentID having count(*) = 1) group by DATE_FORMAT(loads.dateTime, “%M %e, %Y”))”

//Update with Another Return
enter image description here

//A couple of Updates — August 30, 2018

Based on your original suggestion, I made the updates but received this error:

“SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘truckin.shipments.pro_number’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select ANY_VALUE(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 from loads left join shipments on loads.shipmentID = shipments.id left join equipment as tractor on loads.tractorID = tractor.id left join employees on loads.driverID = employees.id left join customers as shipperCustomer on shipments.ship_from = shipperCustomer.id left join customers as consigneeCustomer on shipments.ship_to = consigneeCustomer.id where loads.type = 2 and loads.status = 1 group by DATE_FORMAT(loads.dateTime, “%M %e, %Y”))”

So I went ahead, and ended up having to do the following due to a cascading failure:

$loads = $query->groupBy(DB::raw(‘DATE_FORMAT(loads.dateTime, “%M %e, %Y”)’), ‘loads.dateTime’,’shipments.id’, ‘shipments.pro_number’,’driver’,’tractor’,’ShipperCustomerName’,’ConsigneeCustomerName’,’ShipperName’,’ConsigneeName’)->get();

Now obviously this isn’t right, but it was the only way I could get a return of any sort, which ended up being the following:

enter image description here

How to solve:

One last approach, change this:

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');

To this:

selectRaw('ANY_VALUE(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');

And leave the groupBy as

$query->groupBy(DB::raw('DATE_FORMAT(loads.dateTime, "%M %e, %Y")'))->get();

If you want to know more about this solution check: https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_any-value

Check the example on the link and take care of what im quoting down here:

The failure occurs because address is a nonaggregated column that is neither named among GROUP BY columns nor functionally dependent on them. As a result, the address value for rows within each name group is nondeterministic. There are multiple ways to cause MySQL to accept the query:

Alter the table to make name a primary key or a unique NOT NULL column. This enables MySQL to determine that address is functionally dependent on name; that is, address is uniquely determined by name. (This technique is inapplicable if NULL must be permitted as a valid name value.)

Use ANY_VALUE() to refer to address:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

In this case, MySQL ignores the nondeterminism of address values within each name group and accepts the query. This may be useful if you simply do not care which value of a nonaggregated column is chosen for each group. ANY_VALUE() is not an aggregate function, unlike functions such as SUM() or COUNT(). It simply acts to suppress the test for nondeterminism.

Disable ONLY_FULL_GROUP_BY. This is equivalent to using ANY_VALUE() with ONLY_FULL_GROUP_BY enabled, as described in the previous item.

Answer´╝Ü

In the configuration of database at config/database.php change strict =>false
Default it is true

Leave a Reply

Your email address will not be published. Required fields are marked *