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?

When you equal the variables, you are using the reference to the object, you have to clone it to prevent modifying the original query, and please, use relationships not "raw" sql. This issue is basic PHP — matiaslauriti, Jul 09 '23 at 14:15
Hi @matiaslauriti thanks for the comment. You mean the PHP clone way? Is there any laravel way to do the clone or split the query? — Md. Amin Hossain, Jul 10 '23 at 07:31

IGP · Accepted Answer · 2023-07-10T20:19:41.747

2

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;
}

edited Jul 10 '23 at 20:19

answered Jul 10 '23 at 08:15

IGP

14,160
4
26
43

Hi @IGP thanks buddy. It's working as I expected. :) – Md. Amin Hossain Jul 10 '23 at 13:00
You're welcome, and to answer your other question, this is the same as using `clone $base_query` as you can see in [the source code](https://github.com/laravel/framework/blob/10.x/src/Illuminate/Database/Query/Builder.php#L3865) – IGP Jul 10 '23 at 20:07

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

1 Answers1