0

I save the part of a query into a variable $base_query like this:

$base_query = DB::connection('mysql_live')->table('users_transactions_all')
->whereDate('users_transactions_all.date_added', '>=', $start_date)->whereDate('users_transactions_all.date_added', '<=', $end_date)
->join('users', 'users.id', '=', 'users_transactions_all.user_id')
->when($data->regcountry != "all", function ($query) use ($data) {
    return $query->whereIn('users.regcountry', $data->regcountry);
});

In the next step I use it like this and this works:

$payments = $base_query->select('users_transactions_all.date_elem AS date',
        DB::raw("SUM(users_transactions_all.eur_amount) as eurAmount"),
        DB::raw("SUM(users_transactions_all.partner_payout_eur) as eurPayoutPartner"),
        DB::raw("COUNT(users_transactions_all.id) as count"))
        ->groupBy('date')->get();

After that I have issues. If I want to reuse the variable I get an error:

$packages = $base_query->select(DB::raw("COUNT(users_transactions_all.package_name) as count"),
DB::raw("SUM(users_transactions_all.eur_amount) as eurAmount"), 'users_transactions_all.package_name')
->groupBy('users_transactions_all.package_name')->get();

The error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'date' in 'group statement'

In the second call if I just replace the $base_query var with its content, then everything works. It looks like the var is somehow consumed the first time I use it and cannot be reused, whats going on here? How can I reuse it?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Roman
  • 3,563
  • 5
  • 48
  • 104
  • 2
    Does this answer your question? [Laravel query builder - re-use query with amended where statement](https://stackoverflow.com/questions/30235551/laravel-query-builder-re-use-query-with-amended-where-statement) You just need to clone the object before proceeding with the `groupBy()->get()` – N69S Nov 29 '22 at 09:47

1 Answers1

0

In the first function you actually change the $base_query variable when you add ->select to it. You can clone the variable and use it like that.

$baseQueryClone = clone $base_query;

Write this above the first function, and use your clone in that one and the base_query in your second one.

Roman
  • 3,563
  • 5
  • 48
  • 104
dz0nika
  • 882
  • 1
  • 5
  • 16