Consulta en laravel con groupBy
Publicado por Noe Castillo Zamorano (2 intervenciones) el 08/01/2018 22:12:44
Buenas tardes amigos del foro, tengo la siguiente consulta que lo que hace es traerme pivotada las semanas que tiene pagadas un alumno, funciona bien pero le estoy poniendo el año de consulta directamente como se muestra:
Pero yo quiero hacerlo de la siguiente manera....
al hacer esta consulta me manda el siguiente error:
(2/2) QueryException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cobros.sem_mes' in 'field list' (SQL: select `alu`.`nombre_alumno` as `nombre`, `alu`.`apellido_paterno` as `appat`, `alu`.`apellido_materno` as `apmat`,
sum(if(cobros.sem_mes=1,importe,0)) as importe1,
sum(if(cobros.sem_mes=2,importe,0)) as importe2,
etcetera.....
Desde una vista le mando el año que quiero consultar y lo capturo con la variable $query, cualquier apoyo les estaré agradecido.
saludos y gracias de antemano
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
public function index(Request $request)
{
$cobros=DB::select('select alumnos.nombre_alumno as nombre,alumnos.apellido_paterno as appat,alumnos.apellido_materno as apmat,
sum(if(cobros.sem_mes=1,importe,0)) as importe1,
sum(if(cobros.sem_mes=2,importe,0)) as importe2,
sum(if(cobros.sem_mes=3,importe,0)) as importe3,
sum(if(cobros.sem_mes=4,importe,0)) as importe4,
sum(if(cobros.sem_mes=5,importe,0)) as importe5,
sum(if(cobros.sem_mes=6,importe,0)) as importe6,
sum(if(cobros.sem_mes=7,importe,0)) as importe7,
sum(if(cobros.sem_mes=8,importe,0)) as importe8,
sum(if(cobros.sem_mes=9,importe,0)) as importe9,
sum(if(cobros.sem_mes=10,importe,0)) as importe10,
sum(if(cobros.sem_mes=11,importe,0)) as importe11,
sum(if(cobros.sem_mes=12,importe,0)) as importe12,
sum(if(cobros.sem_mes=13,importe,0)) as importe13,
sum(if(cobros.sem_mes=14,importe,0)) as importe14,
sum(if(cobros.sem_mes=15,importe,0)) as importe15,
sum(if(cobros.sem_mes=16,importe,0)) as importe16,
sum(if(cobros.sem_mes=17,importe,0)) as importe17,
sum(if(cobros.sem_mes=18,importe,0)) as importe18,
sum(if(cobros.sem_mes=19,importe,0)) as importe19,
sum(if(cobros.sem_mes=20,importe,0)) as importe20,
sum(if(cobros.sem_mes=21,importe,0)) as importe21,
sum(if(cobros.sem_mes=22,importe,0)) as importe22,
sum(if(cobros.sem_mes=23,importe,0)) as importe23,
sum(if(cobros.sem_mes=24,importe,0)) as importe24,
sum(if(cobros.sem_mes=25,importe,0)) as importe25,
sum(if(cobros.sem_mes=26,importe,0)) as importe26,
sum(if(cobros.sem_mes=27,importe,0)) as importe27,
sum(if(cobros.sem_mes=28,importe,0)) as importe28,
sum(if(cobros.sem_mes=29,importe,0)) as importe29,
sum(if(cobros.sem_mes=30,importe,0)) as importe30,
sum(if(cobros.sem_mes=31,importe,0)) as importe31,
sum(if(cobros.sem_mes=32,importe,0)) as importe32,
sum(if(cobros.sem_mes=33,importe,0)) as importe33,
sum(if(cobros.sem_mes=34,importe,0)) as importe34,
sum(if(cobros.sem_mes=35,importe,0)) as importe35,
sum(if(cobros.sem_mes=36,importe,0)) as importe36,
sum(if(cobros.sem_mes=37,importe,0)) as importe37,
sum(if(cobros.sem_mes=38,importe,0)) as importe38,
sum(if(cobros.sem_mes=39,importe,0)) as importe39,
sum(if(cobros.sem_mes=40,importe,0)) as importe40,
sum(if(cobros.sem_mes=41,importe,0)) as importe41,
sum(if(cobros.sem_mes=42,importe,0)) as importe42,
sum(if(cobros.sem_mes=43,importe,0)) as importe43,
sum(if(cobros.sem_mes=44,importe,0)) as importe44,
sum(if(cobros.sem_mes=45,importe,0)) as importe45,
sum(if(cobros.sem_mes=46,importe,0)) as importe46,
sum(if(cobros.sem_mes=47,importe,0)) as importe47,
sum(if(cobros.sem_mes=48,importe,0)) as importe48,
sum(if(cobros.sem_mes=49,importe,0)) as importe49,
sum(if(cobros.sem_mes=50,importe,0)) as importe50,
sum(if(cobros.sem_mes=51,importe,0)) as importe51,
sum(if(cobros.sem_mes=52,importe,0)) as importe52
from cobros, alumnos
where cobros.idalumno=alumnos.idalumno and cobros.anio=2017 group by alumnos.nombre_alumno,alumnos.apellido_paterno, alumnos.apellido_materno');
return view('cobranzas.historial.index',["cobros"=>$cobros]);
}
Pero yo quiero hacerlo de la siguiente manera....
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
public function index(Request $request)
{
$query=$request->get('searchAnio');
$cobros=DB::table('alumnos as alu')
->join('cobros as cob','alu.idalumno','=','cob.idalumno')
->select('alu.nombre_alumno as nombre','alu.apellido_paterno as appat','alu.apellido_materno as apmat',DB::raw('
sum(if(cobros.sem_mes=1,importe,0)) as importe1,
sum(if(cobros.sem_mes=2,importe,0)) as importe2,
sum(if(cobros.sem_mes=3,importe,0)) as importe3,
sum(if(cobros.sem_mes=4,importe,0)) as importe4,
sum(if(cobros.sem_mes=5,importe,0)) as importe5,
sum(if(cobros.sem_mes=6,importe,0)) as importe6,
sum(if(cobros.sem_mes=7,importe,0)) as importe7,
sum(if(cobros.sem_mes=8,importe,0)) as importe8,
sum(if(cobros.sem_mes=9,importe,0)) as importe9,
sum(if(cobros.sem_mes=10,importe,0)) as importe10,
sum(if(cobros.sem_mes=11,importe,0)) as importe11,
sum(if(cobros.sem_mes=12,importe,0)) as importe12,
sum(if(cobros.sem_mes=13,importe,0)) as importe13,
sum(if(cobros.sem_mes=14,importe,0)) as importe14,
sum(if(cobros.sem_mes=15,importe,0)) as importe15,
sum(if(cobros.sem_mes=16,importe,0)) as importe16,
sum(if(cobros.sem_mes=17,importe,0)) as importe17,
sum(if(cobros.sem_mes=18,importe,0)) as importe18,
sum(if(cobros.sem_mes=19,importe,0)) as importe19,
sum(if(cobros.sem_mes=20,importe,0)) as importe20,
sum(if(cobros.sem_mes=21,importe,0)) as importe21,
sum(if(cobros.sem_mes=22,importe,0)) as importe22,
sum(if(cobros.sem_mes=23,importe,0)) as importe23,
sum(if(cobros.sem_mes=24,importe,0)) as importe24,
sum(if(cobros.sem_mes=25,importe,0)) as importe25,
sum(if(cobros.sem_mes=26,importe,0)) as importe26,
sum(if(cobros.sem_mes=27,importe,0)) as importe27,
sum(if(cobros.sem_mes=28,importe,0)) as importe28,
sum(if(cobros.sem_mes=29,importe,0)) as importe29,
sum(if(cobros.sem_mes=30,importe,0)) as importe30,
sum(if(cobros.sem_mes=31,importe,0)) as importe31,
sum(if(cobros.sem_mes=32,importe,0)) as importe32,
sum(if(cobros.sem_mes=33,importe,0)) as importe33,
sum(if(cobros.sem_mes=34,importe,0)) as importe34,
sum(if(cobros.sem_mes=35,importe,0)) as importe35,
sum(if(cobros.sem_mes=36,importe,0)) as importe36,
sum(if(cobros.sem_mes=37,importe,0)) as importe37,
sum(if(cobros.sem_mes=38,importe,0)) as importe38,
sum(if(cobros.sem_mes=39,importe,0)) as importe39,
sum(if(cobros.sem_mes=40,importe,0)) as importe40,
sum(if(cobros.sem_mes=41,importe,0)) as importe41,
sum(if(cobros.sem_mes=42,importe,0)) as importe42,
sum(if(cobros.sem_mes=43,importe,0)) as importe43,
sum(if(cobros.sem_mes=44,importe,0)) as importe44,
sum(if(cobros.sem_mes=45,importe,0)) as importe45,
sum(if(cobros.sem_mes=46,importe,0)) as importe46,
sum(if(cobros.sem_mes=47,importe,0)) as importe47,
sum(if(cobros.sem_mes=48,importe,0)) as importe48,
sum(if(cobros.sem_mes=49,importe,0)) as importe49,
sum(if(cobros.sem_mes=50,importe,0)) as importe50,
sum(if(cobros.sem_mes=51,importe,0)) as importe51,
sum(if(cobros.sem_mes=52,importe,0)) as importe52
'))
->groupBy('alumnos.nombre_alumno')
->groupBy('alumnos.apellido_paterno')
->groupBy('alumnos.apellido_materno')
->having('cobros.anio','=',$query)
->get();
return view('cobranzas.historial.index',["cobros"=>$cobros]);
}
al hacer esta consulta me manda el siguiente error:
(2/2) QueryException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cobros.sem_mes' in 'field list' (SQL: select `alu`.`nombre_alumno` as `nombre`, `alu`.`apellido_paterno` as `appat`, `alu`.`apellido_materno` as `apmat`,
sum(if(cobros.sem_mes=1,importe,0)) as importe1,
sum(if(cobros.sem_mes=2,importe,0)) as importe2,
etcetera.....
Desde una vista le mando el año que quiero consultar y lo capturo con la variable $query, cualquier apoyo les estaré agradecido.
saludos y gracias de antemano
Valora esta pregunta
0