Laravel - GroupBy as Alias

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

$query

$query = DB::table('loads')...

and the field dateTime is a timestamp.

dateTime

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:

///

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"))"

loads

id

shipments

pro_number

shipments

id

employees

last_name

driver

tractor

unit_id

tractor

loads

dateTime

loadsDT

shipperCustomer

customer_name

ShipperCustomerName

consigneeCustomer

customer_name

ConsigneeCustomerName

shipments

cn_shipfromName

ShipperName

shipments

cn_shiptoName

ConsigneeName

loads

shipments

loads

shipmentID

shipments

id

equipment

tractor

loads

tractorID

tractor

id

employees

loads

driverID

employees

id

customers

shipperCustomer

shipments

ship_from

shipperCustomer

id

customers

consigneeCustomer

shipments

ship_to

consigneeCustomer

id

loads

type

loads

status

shipmentID

shipmentID

loads

shipmentID

//Update with Another Return

//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"))"

loads

shipments

loads

shipmentID

shipments

id

equipment

tractor

loads

tractorID

tractor

id

employees

loads

driverID

employees

id

customers

shipperCustomer

shipments

ship_from

shipperCustomer

id

customers

consigneeCustomer

shipments

ship_to

consigneeCustomer

id

loads

type

loads

status

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:

2 Answers
2

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.

dateTime

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

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.