Laravel query builder - re-use query with amended where statement

Question

My application dynamically builds and runs complex queries to generate reports. In some instances I need to get multiple, somewhat arbitrary date ranges, with all other parameters the same.

So my code builds the query with a bunch of joins, wheres, sorts, limits etc and then runs the query. What I then want to do is jump into the Builder object and change the where clauses which define the date range to be queried.

So far, I have made it so that the date range is setup before any other wheres and then tried to manually change the value in the relevant attribute of the wheres array. Like this;

$this->data_qry->wheres[0]['value'] = $new_from_date;
$this->data_qry->wheres[1]['value'] = $new_to_date;

Then I do (having already done it once already)

$this->data_qry->get();

Doesn't work though. The query just runs with the original date range. Even if my way worked, I still wouldn't like it though as it seems to be shot through with a precarious dependence (some sort of coupling?). Ie; if the date wheres aren't set up first then it all falls apart.

I could set the whole query up again from scratch, just with a different date range, but that seems ott as everything else in the query needs to be the same as the previous time it was used.

Any ideas for how to achieve this in the correct / neatest way are very welcome.

Thanks,

Geoff

Asylum

Answer 1

You can use clone to duplicate the query and then run it with different where statements. First, build the query without the from-to constraints, then do something like this:

$query1 = $this->data_qry;
$query2 = clone $query1;

$result1 = $query1->where('from', $from1)->where('to', $to1)->get();
$result2 = $query2->where('from', $from2)->where('to', $to2)->get();

Pelagi

This looked good so ended up using something along these lines. Had to change the flow in my app a bit but I think it was for the best, after all. Cheers! – Asylum

thank you so much i've been stuck in this for hours i thought u can just clone a query by doing $query2 = $query1; – Hypothermal

@YoussefBoudaya, true. Been quite annoyed as I did a randomization on my first query and the second one also being randomized. – Abrupt

Answer 2

The suggestion from @lukasgeiter using clone is definitely the way to go; the reason is that an Eloquent\Builder object contains an internal reference to a Query\Builder that needs to be duplicated.

To keep the flow of your app and get back to a more functional style, you can use Laravel's with() helper, which simply returns the object passed in:

$result1 = with(clone $this->data_qry)->where('from', $from1)->where('to', $to1)->get();
$result2 = with(clone $this->data_qry)->where('from', $from2)->where('to', $to2)->get();

Tall

Or simply wrap the clone statement in parentheses: (clone $this->data_qry)->where('from', $from1); – Cockneyfy

Wors really nice, also outside function – Aleksandropol

Answer 3

For the people who want a simpler and shorter syntax, you can daisy chain the clone() method on the query builder.

$result1 = $this->data_qry->clone()->where('from', $from1)->where('to', $to1)->get();
$result2 = $this->data_qry->clone()->where('from', $from2)->where('to', $to2)->get();

Levitan