Laravel: Eloquent select returning different results

Question

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

why do you add count without using any group by ? if you want to count the total data returned from ->get() , what you can do is using $count = count($data); — Win
– Win, Commented Jan 26, 2023 at 17:13
extra note: you can use shorter code $startDate = Carbon::parse('2023-01-26 00:00:00', 'America/Chicago')->tz('UTC') and inside eloquent query ->whereBetween also accept Carbon, no need to convert it to DateTimeString. ... by the way be careful when search between 00:00:00 to 23:59:59, you might skip transaction happen exactly @ 23:59:59, best to do is $current_tz = 'America/Chicago'; $server_tz = 'UTC'; $date = Carbon::parse('2023-01-26', $current_tz); $startDate = $date->tz($server_tz); $endDate = $date->addDay()->tz($server_tz); — Win
– Win, Commented Jan 26, 2023 at 17:22
Also, Eloquent returns a collection, so $data->count() will also work — aynber
– aynber, Commented Jan 26, 2023 at 17:29

IGP · Accepted Answer · 2023-01-26 17:06:57Z

3

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:

id	cancelled
1	1
2	0
3	0
4	1

SELECT * FROM orders WHERE cancelled = 0 will return

id	cancelled
2	0
3	0

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

id	cancelled	count
2	0	2

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

id	cancelled	count
2	0	1
3	0	1

answered Jan 26, 2023 at 17:06

IGP

16.4k5 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AJK Over a year ago

Okay that makes perfect sense, and yea MySQL.. adding groupBy('id') fixed the issue I was running into! Thank you for the explanation!

Collectives™ on Stack Overflow

Laravel: Eloquent select returning different results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related