How can I execute a query with nested select?

How can I execute the following query in Laravel using Eloquen? If not with Eloquent, how would it be with the Query Builder?

SELECT
grupos.id,
grupos.grupo_nombre,
grupos.grupo_alumnospermitidos,
(SELECT COUNT(grupos_alumnos.id) FROM grupos_alumnos WHERE grupos_alumnos.grupo_id = grupos.id) AS numAlumnos
FROM grupos
ORDER BY
grupos.clasificacion_id ASC,
grupos.grupo_nombre ASC

Thanks for your help

Snapey

7 years ago

Level 122

Create model for Groupo

create model for Alumno

create relationships between the two

uicabpatweyler

7 years ago

Level 4

Thank you @Snapey , do it this way

$grupos = DB::table('grupos')
            ->select('grupos.id','grupos.grupo_nombre','grupos.grupo_alumnospermitidos')
            ->addSelect(DB::raw('(SELECT COUNT(grupos_alumnos.id) FROM grupos_alumnos WHERE grupos_alumnos.grupo_id = grupos.id) AS numAlumnos'))
            ->orderBy('grupos.clasificacion_id','asc')
            ->orderBy('grupos.grupo_nombre','asc')
            ->get();

Works correctly.

1 like

Snapey

7 years ago

Level 122

I thought you wanted to do it with Eloquent?

Please or to participate in this conversation.