CODEWITHSUNDEEP
laravel
scottsuhy
scottsuhy

Reputation: 405

Need guidance on how to build a Laravel database query

In Laravel 6.18 I'm trying to figure out how to recreate the following Postgres query. 

with data as (
    select date_trunc('month', purchase_date) as x_month,  date_trunc('year', purchase_date) AS x_year,
    sum (retail_value) AS "retail_value_sum" 
    from coins 
    where user_email = '[email protected]' and sold = 0
    group by x_month, x_year
    order by x_month asc, x_year asc
)
select x_month, x_year, sum (retail_value_sum) over (order by x_month asc, x_year asc rows between unbounded preceding and current row)
from data

I know how to build the main part of the query

$value_of_all_purchases_not_sold = DB::table('coins')
    ->select(DB::raw('date_trunc(\'month\', purchase_date) AS x_month, date_trunc(\'year\', purchase_date) AS x_year, sum(retail_value) as purchase_price_sum'))
    ->where('user_email', '=', auth()->user()->email)
    ->where('sold', '=', 0)
    ->groupBy('x_month', 'x_year')
    ->orderBy('x_month', 'asc')
    ->orderBy('x_year', 'asc')
    ->get();

but how do you build out the with data as ( and the second select?

I need the data to be cumulative and I'd rather do the calculation in the DB than in PHP.

Upvotes: 0

Views: 36

Answers (1)

Ersoy
Ersoy

Reputation: 9594

Laravel doesn't have built-in method(s) for common table expression. You may use a third party package such as this - it has a very good documentation. If you don't want to use an external library, then you need use query builder's select method with bindings such as 

$results = DB::select('your-query', ['your', 'bindings']);

return Coin::hydrate($results); // if you want them as collection of Coin instance.

Upvotes: 1

Related Questions