How can I avoid Eloquent query to be chained?

Question

I'm new to Laravel.

I wonder how I can avoid query to be chained.

$visitRecords = VisitRecord::whereDate('visited_at', '=', Carbon::today()->toDateString());


$knockBounce = $visitRecords->where("bounce_zone", "1")->get()->count();


$approachBounce = $visitRecords->where("bounce_zone", "2")->get()->count();

This is the code I wrote but this gives me the result I don't expect...

Result

select * from `visit_records` where date(`visited_at`) = '2017-05-12' 

select * from `visit_records` where date(`visited_at`) = '2017-05-12' and `bounce_zone` = '1'

select * from `visit_records` where date(`visited_at`) = '2017-05-12' and `bounce_zone` = '1' and `bounce_zone` = '2'

I checked the query conducted and this is what I got.

What I expect...

select * from `visit_records` where date(`visited_at`) = '2017-05-12' 

select * from `visit_records` where date(`visited_at`) = '2017-05-12' and `bounce_zone` = '1'

select * from `visit_records` where date(`visited_at`) = '2017-05-12' and `bounce_zone` = '2'

I want to conduct this query instead via Eloquent methods.

Check [this](http://stackoverflow.com/a/27522556/6140684) - are you sure it is chained? it shouldn't — Antonios Tsimourtos, May 12 '17 at 13:27

Luke · Accepted Answer · 2017-05-12T14:16:41.257

1

You've only instantiated a single QueryBuilder object.

You should create a second QueryBuilder object for the second query.

//Only create one carbon object
$date = Carbon::today()->toDateString()

$knockBounce = VisitRecord::whereDate('visited_at', '=', $date)->where("bounce_zone", "1")->count();

$approachBounce = VisitRecord::whereDate('visited_at', '=', $date)->where("bounce_zone", "2")->count();

Updated as per Matthew's comment, Laravel will execute a ->get() anyway for aggregate functions (count, min, max, avg) under the hood so it's not needed.

edited May 12 '17 at 14:16

answered May 12 '17 at 13:34

Luke

3,481
6
39
63

2

You should use directly ->count() without ->get() :) – Mathieu Ferre May 12 '17 at 14:02

score 0 · Answer 2 · answered May 12 '17 at 13:32

You need to have two different objects

you might try below code

$visitRecords = VisitRecord::whereDate('visited_at', '=', Carbon::today()->toDateString());

$visitRecords1 = clone $visitRecords;    

$knockBounce = $visitRecords->where("bounce_zone", "1")->get()->count();    

$approachBounce = $visitRecords1->where("bounce_zone", "2")->get()->count();

Here I have used clone to copy the $visitRecords object

Further reading about php clone

Mathieu Ferre · Answer 3 · 2017-05-12T13:44:14.520

-1

Edit to take only the needed bounce_zone

Alternatively, you can use collections :

$visitRecords = VisitRecord::whereDate('visited_at', '=', Carbon::today()->toDateString())->where("bounce_zone", "1")->orWhere("bounce_zone", "2")->get();


$knockBounce = $visitRecords->where("bounce_zone", "1")->count();


$approachBounce = $visitRecords->where("bounce_zone", "2")->count();

You run only one query and use all the power of Laravel :)

edited May 12 '17 at 13:44

answered May 12 '17 at 13:37

Mathieu Ferre

4,246
1
14
31

Run query once but at what cost? Depends on business logic but I'd avoid fetching data you don't intend to process. e.g. all the records `where bounce_zone not in (1, 2)` could be millions of rows you never need ? – Luke May 12 '17 at 13:40
@luke does this approach require more calculations? – May 12 '17 at 13:43
This answer also uses the Collection version of `where()`, which could end up being extremely slow. – Samsquanch May 12 '17 at 13:46
@Hayatomo Not more calculations but potentially returns values in the collection you will never need. If you have other values than `1` or `2` for `bounce_zone` they will be returned in the `$visitRecords` collection but just sit there until the collection was removed by garbage collector as they wouldn't be used in `$knockBounce` or `$approachBounce` – Luke May 12 '17 at 13:57

How can I avoid Eloquent query to be chained?

Result

What I expect...

3 Answers3