Laravel: Using Query Multiple Times with different Wheres

Question

I have been struggling with this for efficiency purpose. Logically, this should work. But, once I call count() or get(), the 2nd usage does not work. If you see the following code, it'll become clear to you:

$query = Address::where('area_id',$area->id);
if($order === 'house_no')
    $query->orderBy('house_no','ASC');
$query->join('members as m','addresses.member_id','=','m.id');
if($order === 'reg')
    $query->orderBy('batch','ASC')->orderBy('reg','ASC');
if($types != '')
    $query->whereIn('m.type', explode(',', $types));

$male = $query->where('m.gender','male')->get();
$female = $query->where('m.gender','female')->get(); //this does not work

As you can see, calling the $query variable in both the $male and $female should logically work. But I only get the result of the first call, it seems that once the count() or get() is called, the $query variable no longer does anything else.

I have also tried to store $query to two different variables for those two collections, but same result.

Any suggestions?

I could do the same thing twice but it would be inefficient (me thinks).

Thanks in advance!

lukasgeiter · Accepted Answer · 2015-02-10 12:24:03Z

16

cloneing it should work. Otherwise you're operating always on the same builder instance.

$maleQuery = clone $query;
$femaleQuery = clone $query;

$male = $maleQuery->where('m.gender','male')->get();
$female = $femaleQuery->where('m.gender','female')->get();

answered Feb 10, 2015 at 12:24

lukasgeiter

154k29 gold badges349 silver badges280 bronze badges

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

4 Comments

Laurence Over a year ago

In addition to Lukas answer - even if you buffer the query - the main 'effort' is the SQL call itself - which you have to run twice. There is no way around it.

Yousof K. Over a year ago

Thanks @lukasgeiter. Life saver. @ The Shift Exchange yes I kept it in mind, but no point writing 16 lines of code twice. Thanks.

Istiaque Ahmed Over a year ago

where is clone documented in Laravel doc ?

Zac Grierson Over a year ago

clone is a native php command Object Clone

Jarek Tkaczyk · Accepted Answer · 2015-02-10 12:52:06Z

It's all about those wheres not get or count - basically your last attempt has where m.gender = male AND m.gender = female, so it logically shouldn't work and it doesn't.

Also mind that storing $query in 2 variables does nothing, since objects are passed by reference, so despite operating on $varOne and $varTwo you still use the same object:

$query = Address::query();
// add your clauses
$male = $query;
$female = $query;

$male === $female; // true

// but
$male = $query;
$female = clone $query;
$male === $female; // false - 2 different objects

// now you can add last where
$males   = $male->where('m.gender', 'male')->get();
$females = $female->where('m.gender', 'female')->get();

Just clone the query before using where('m.gender..) and you will be fine.

Collectives™ on Stack Overflow

Laravel: Using Query Multiple Times with different Wheres

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related