How to split Laravel query result at the end after doing some common query at beginning?

Question

I want to split a Laravel query result at the end of the query after doing some common query in the beginning. Scenario, code smple and my try is given below.

Code sample

$bankSlipIds = [1,2,3];
$cashSlipIds = [4,5];
$sourceIds = [1, 2];

//common query start
$slipBalance = SlipEntry::groupBy('source_id')
    ->join('slips as s', 's.id', '=', 'slip_entries.slip_id')
    ->whereIn('s.slip_type', ['x_slip', 'y_slip']);

if ($source_id) {
    $slipBalance = $slipBalance->whereIn('slip_entries.source_id', $sourceIds);
}

if ($type == "current") {
    $slipBalance = $slipBalance->whereBetween('s.slip_date', [date("Y-m-d", $from]), date("Y-m-d", $to])]);
} else if ($type == "year") {
    $slipBalance = $slipBalance->whereBetween('s.slip_date', [$from, $to]);
} else if ($type == "cumulative") {
    $slipBalance = $slipBalance->where('s.slip_date', "<=", date("Y-m-d", $to]));
}
//common query end

//split result start
$bankSlip = $slipBalance;
$bankSlip = $bankSlip->whereIn('s.bank_slip_id', $bankSlipIds)->select('source_id', DB::raw('SUM(case when slip_type = "y_slip" and type = "credit" then -amount when type = "debit" then amount else 0 end) as amount'))->get();

$cashSlip = $slipBalance;
$cashSlip = $cashSlip->whereIn('s.cash_slip_id', $cashSlipIds)->select( 'source_id', DB::raw('SUM(case when slip_type = "y_slip" and type = "credit" then -amount when type = "debit" then amount else 0 end) as amount'))->get();
//split result end

I try to saw the sql for both query and it show as below,

//bank slip query

select
  `source_id`,
  SUM(case
    when slip_type = "y_slip" and type = "credit" then -amount
    when type = "debit" then amount
    else 0
  end) as amount
from
  `slip_entries`
inner join
  `slips` as `s` on `s`.`id` = `slip_entries`.`slip_id`
where
  `s`.`slip_type` in (?, ?)
  and `slip_entries`.`source_id` in (?)
  and `s`.`slip_date` between ? and ?
  and `s`.`bank_slip_id` in (?)
group by `source_id`

//cash slip query

select
  `source_id`,
  SUM(case
    when slip_type = "y_slip" and type = "credit" then -amount
    when type = "debit" then amount
    else 0
  end) as amount
from
  `slip_entries`
inner join
  `vouchers` as `s` on `s`.`id` = `slip_entries`.`slip_id`
where
  `s`.`slip_type` in (?, ?)
  and `slip_entries`.`source_id` in (?)
  and `s`.`slip_date` between ? and ?
  and `s`.`bank_slip_id` in (?)
  and `s`.`cash_slip_id` in (?)
group by `source_id`

But it looks like at the end split bank query condition is attaching with cash query. As a result not getting the expected result.

How to ignore the bank id condition s.bank_slip_id in (?) in cashSlip query?

Answer 1

You are modifying the same query builder object. The solution is to clone it.

$base_query = SlipEntry::query()->......

...

$query_1 = $base_query->clone()->where('column_1', ...);
$query_2 = $base_query->clone()->where('column_2', ...);

If you do not have another use for $base_query, then the last clone can be removed.

$base_query = SlipEntry::query()->......

...

$query_1 = $base_query->clone()->where('column_1', ...);
$query_2 = $base_query->where('column_2', ...);

This is the same as using (clone $base_query)->.... This is the clone() method's implementation as seen in the source

# vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php

/**
 * Clone the query.
 *
 * @return static
 */
public function clone()
{
    return clone $this;
}