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!

Answer 1

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();

Answer 2

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.