How to make this query in laravel?

Question

I'm trying to make a query in Laravel, but it's not working. I USE POSTGRESQL

select extract(year from fecha_creacion) as anio, extract(month from fecha_creacion) as mes,
     sum(case when tipo = 'entrada' then 1 else 0 end ) 
  from documento 
  group by extract(year from fecha_creacion), extract(month from fecha_creacion)
  order by anio, mes;

I also tried the following.

$data = DB::table('documento')
    ->selectRaw('year from fecha_creacion AS anio, month from fecha_creacion AS mes')
    ->sum(case when tipo = 'entrada' then 1 else 0 end )
    ->orderBy(anio, mes, DESC)
    ->get();

Is it allowed to order by an alias? In addition, your order by DESC has an extra coma before DESC — James, Jun 16 '19 at 19:27
not works because laravel throw this error: syntax error, unexpected 'case' (T_CASE) — Gousikan, Jun 16 '19 at 19:50
@Gousikan You've forgotten quotation marks in your `sum()` line, then. — ceejayoz, Jun 17 '19 at 12:49
Possible duplicate of [PHP parse/syntax errors; and how to solve them?](https://stackoverflow.com/questions/18050071/php-parse-syntax-errors-and-how-to-solve-them) — ceejayoz, Jun 17 '19 at 12:49

score 0 · Answer 1 · answered Jun 16 '19 at 20:09

0

You can use laravel DB::raw() expresssion query.

You have to pass the query in raw function.

reference: https://laravel.com/docs/5.8/queries#raw-expressions

answered Jun 16 '19 at 20:09

hasan05

904
5
22

How to make this query in laravel?

1 Answers1