Complicated Laravel Query leftjoin a made query

Question

Right now I want to have the sum of Sueldo in a column grouped by idEmpleado of table recibo. Im not sure if I can put the DB::table inside the left join. if not how could I call that table in a query to make a join with the information of the empleado.

Here is what I have so far

$pagos = DB::table('empleado')
            ->leftJoin('empresa', 'empresa.idEmpresa', '=', 'empleado.emp_idEmpresa_FK')
            ->leftJoin('departamento', 'departamento.idDepartamento', '=', 'empleado.emp_idDeparameto_FK')
            ->leftJoin('tipoperiodo', 'tipoperiodo.idTipoPeriodo', '=', 'empleado.emp_idTipoPeriodo_FK')
            ->leftJoin(
                DB::table('recibos')
                ->leftJoin('empleado', 'empleado.idEmpleado', '=', 'recibos.rec_idEmpleado_FK')
                ->select(DB::raw('idRecibos, rec_idEmpleado_FK, SUM(SueldoBase) as Restante'))
                ->groupBy('rec_idEmpleado_FK')
                ->get()
                ,'recibos', 'recibos.rec_idEmpleado_FK', '=', 'empleado.idEmpleado')
            ->select('idEmpleado', 'Nombre', 'Nombre_Depto', 'Nombre_Empresa', 'Puesto', 'SueldoBase', 'Restante')
            ->get();

I'm getting an error ErrorException with message :

Array to string conversion

I'm suppose its because I'm trying to make a leftjoin of a query im making there. Dunno Im knid of new to this. Any suggestions ?

With the help of the answer in the commnets I got to this Query,

DB::table('empleado')
            ->leftJoin('empresa', 'empresa.idEmpresa', '=', 'empleado.emp_idEmpresa_FK')
            ->leftJoin('departamento', 'departamento.idDepartamento', '=', 'empleado.emp_idDeparameto_FK')
            ->leftJoin('tipoperiodo', 'tipoperiodo.idTipoPeriodo', '=', 'empleado.emp_idTipoPeriodo_FK')
            ->leftjoin(
                    DB::raw('SELECT rec_idEmpleado_FK, SUM( SueldoBase ) AS Restante
                    FROM `recibos`
                    INNER JOIN empleado ON rec_idEmpleado_FK = idEmpleado
                    GROUP BY rec_idEmpleado_FK'), 
                    function($join)
                    {
                        $join->on('idEmpleado', '=', 'rec_idEmpleado_FK');
                    })
            ->select('idEmpleado', 'Nombre', 'Nombre_Depto', 'Nombre_Empresa', 'Puesto', 'SueldoBase', 'Restante')
            ->get();

But still get the error:

check the manual that corresponds to your MySQL server version for the right syntax to use near

SELECT rec_idEmpleado_FK, SUM( SueldoBase ) AS Restante
FROM `recibos`

Any ideas why?

You're trying to join a subquery right? I don't think Eloquent supports that, short of using DB::raw -- See http://stackoverflow.com/questions/18079281/laravel-fluent-query-builder-join-with-subquery — Chelsea Urquhart, May 29 '14 at 05:42

score 0 · Accepted Answer · answered May 29 '14 at 17:14

Ok I finally got the answer. I forgot to put the name to the inner query here is my answer. I hope it helps. I based my answer in the link that Chelsea gave me. Thanks

$pagos = DB::table('empleado')
            ->leftJoin('empresa', 'empresa.idEmpresa', '=', 'empleado.emp_idEmpresa_FK')
            ->leftJoin('departamento', 'departamento.idDepartamento', '=', 'empleado.emp_idDeparameto_FK')
            ->leftJoin('tipoperiodo', 'tipoperiodo.idTipoPeriodo', '=', 'empleado.emp_idTipoPeriodo_FK')
            ->leftjoin(
                    DB::raw('(SELECT rec_idEmpleado_FK, SUM( SueldoBase ) AS Restante
                    FROM `recibos`
                    INNER JOIN empleado ON rec_idEmpleado_FK = idEmpleado
                    GROUP BY rec_idEmpleado_FK) AS recibos1'), 
                    function($join)
                    {
                        $join->on('idEmpleado', '=', 'rec_idEmpleado_FK');
                    })
            ->select('idEmpleado', 'Nombre', 'Nombre_Depto', 'Nombre_Empresa', 'Puesto', 'SueldoBase', 'Restante')
            ->get();

Complicated Laravel Query leftjoin a made query

1 Answers1