I'm trying to understand how Eloquent select changes the results of a query. For example:

$startDate = Carbon::createFromFormat('Y-m-d H:i:s', '2023-01-26 00:00:00', 'America/Chicago')->timezone('UTC');
$endDate = Carbon::createFromFormat('Y-m-d H:i:s', '2023-01-26 23:59:59', 'America/Chicago')->timezone('UTC');
$data = Orders::where('canceled', 0)
    ->whereBetween('created_at', [$startDate->toDateTimeString(), $endDate->toDateTimeString()])
    ->where(function ($query) {
        $query->where('is_branding', 1)
            ->orWhere('is_premium_branding', 1);
        })
    ->get();

I have some other code running a foreach on this data to calculate and I end up with:

{
"branding_order_items_count": 12,
"branding_order_items_sales": 799.98,
"branding_order_items_margin": 169.71,
"branding_order_items_margin_percent": 0
}

However, if I run the same query but with an added select and calculate through the same foreach loop, I get a different result:

$startDate = Carbon::createFromFormat('Y-m-d H:i:s', '2023-01-26 00:00:00', 'America/Chicago')->timezone('UTC');
$endDate = Carbon::createFromFormat('Y-m-d H:i:s', '2023-01-26 23:59:59', 'America/Chicago')->timezone('UTC');
$data = Orders::where('canceled', 0)
    ->whereBetween('created_at', [$startDate->toDateTimeString(), $endDate->toDateTimeString()])
    ->where(function ($query) {
        $query->where('is_branding', 1)
            ->orWhere('is_premium_branding', 1);
        })
    ->select('*', DB::raw("count(*) as count")) // <<<<<<< Added this
    ->get();

With that added select, I get the following:

{
"branding_order_items_count": 11,
"branding_order_items_sales": 649.99,
"branding_order_items_margin": 142.12,
"branding_order_items_margin_percent": 0
}

The first result is the correct numbers, 12 items / 799.98, etc. So why does adding the select to the eloquent query return a different result, and how can I have it return the correct results while still using the select?

TIA

CodePudding user response：

I'm assuming this is using a MySQL database. PostgreSQL would throw a grouping error with that query.

The reason your results differ is most likely due to DB::raw("count(*) as count"). Without a GROUP BY clause, the results will only be 1 row of data.

If your orders table looks like this:

SELECT * FROM orders WHERE cancelled = 0 will return

SELECT *, COUNT(*) AS count FROM orders WHERE cancelled = 0 will return

SELECT *, COUNT(*) AS count FROM orders WHERE cancelled = 0 GROUP BY id will return