How can i convert this query(postgresql) to laravel eloquent-CodePudding

I want to convert below postgresql code to laravel eloquent

select s2.itemdesc,
       to_char(date_trunc('month', saledate), 'YYYY') AS year,
       to_char(date_trunc('month', saledate), 'Month') AS month,
       to_char(date_trunc('month', saledate), 'MM') AS month_number,
       sum(qty) AS qty1
from sale s left join saled s2 on s.saleno = s2.saleno 
GROUP BY s2.itemdesc, date_trunc('month', saledate)
order by s2.itemdesc desc and month desc

I have tried query in Postgresql Manager and its work. but I failed when convert to Laravel eloquent

CodePudding user response：

DB::query() returns an instance of the query builder.

From that instance, you can list the columns you want to select as comma separated parameters with select(). For computed or aggregated columns, passing DB::raw(expression) instead of the raw expression works. Another way is to pass everything as a single parameter to selectRaw is also an option.

There are two ways to select the main table: The Query Builder's from(table, alias) method. The alias parameter is optional. The table parameter can be either a string or a Closure for subquery tables. DB::table(table, alias) also works. It's functionally the same as doing DB::query()->from(table, alias).

groupBy()/groupByRaw() is used to group by columns.

orderBy()/orderByRaw() is used to order by columns.

DB::query()
    ->select(
        's2.itemdesc',
        DB::raw("to_char(date_trunc('month', saledate), 'YYYY') AS year"),
        DB::raw("to_char(date_trunc('month', saledate), 'Month') AS month"),
        DB::raw("to_char(date_trunc('month', saledate), 'MM') AS month_number"),
        DB::raw("sum(qty) AS qty1")
    )
    ->from('sale', 's')
    ->leftJoin('saled s2', 's.saleno', 's2.saleno')
    ->groupByRaw("s2.itemdesc, date_trunc('month', saledate)")
    ->orderByRaw8"s2.itemdesc desc, month desc")
    ->get();

An Eloquent Model works the same way.

Sale::query()->...