query eloquent and sql

Question

I´m trying to create query that return "Number of companies that belong to the census" with laravel Eloquent or DB::raw()

i think that my query it´s select count(censosEmpresas.empresa_cif) from censosEmpresas groupBy codigoCenso similar it´s in mysql. My problem it´s that i´m using resolve with GraphQL and laravel and i don´t know if it´s correct because to extract this data i´m in activity table that it has a relationship with censos with column censo_id and censo has a relationship with censosEmpresas with column codigoCenso. i think that to build this query i need leftJoin and i´m building my query so:

public function actividadesPorAplicacionConEmpresasCount($root, array $args, $context, $info)
{
    $actividadId = $args["aplicacion_id"];
    //$countEmpresas = CensosEmpresas::groupBy('empresa_cif')->select('codigoCenso', \DB::raw('count(*) as totalEmpresas'))->where('codigoCenso', $aplicacionId)->get();
    $countEmpresas = Actividad::select(count('censosEmpresas.empresa_cif'))
                                ->leftJoin('censos', 'id', 'actividades.censo_id')
                                ->leftJoin('censosEmpresas', 'id', 'censosEmpresas.codigoCenso')
                                ->where('censosEmpresas.codigoCenso', $actividadId)
                                ->groupBy('codigoCenso');

    return $countEmpresas;
}

but this, return en mi console:

debugMessage: 
"count(): Argument #1 ($value) must be of type Countable|array, string given"

I don´t know if i´m building my query correctly. My database structure it´s:

Thanks for readme and sorry for my bad english

Answer 1

You need to pass the SUM, COUNT, AVG and other SQL functions as text to the Eloquent translator. This is called "raw SQL". It's important to know that you're responsible for the syntax correctness of SQL commands passed in this form!

In your original code, you told PHP to pass the character count of a string ('censosEmpresas.empresa_cif') to the "select" function, using the PHP count() function.

However, what you need to pass to the function is a text string indicating the number of rows in the censosEmpresas.empresa_cif column. The select() function is not suitable for this, because if you use select("count(censosEmpresas.empresa_cif)"), you would immediately get an error message saying that the column named count(censosEmpresas.empresa_cif) cannot be found.

Instead, you need to pass the calculation request in RAW SQL format: selectRaw("count(censosEmpresas.empresa_cif)"). This way, the Eloquent will literally insert count(censosEmpresas.empresa_cif) after the select keyword, and when the SQL is executed, you'll get the result back.

More information: https://laravel.com/docs/10.x/queries#selectraw

Example:

Model::selectRaw("count(censosEmpresas.empresa_cif)");

Rewriting your original code:

public function actividadesPorAplicacionConEmpresasCount($root, array $args, $context, $info)
{
    $actividadId = $args["aplicacion_id"];
    //$countEmpresas = CensosEmpresas::groupBy('empresa_cif')->select('codigoCenso', \DB::raw('count(*) as totalEmpresas'))->where('codigoCenso', $aplicacionId)->get();
    $countEmpresas = Actividad::selectRaw("count(censosEmpresas.empresa_cif)")
                                ->leftJoin('censos', 'id', 'actividades.censo_id')
                                ->leftJoin('censosEmpresas', 'id', 'censosEmpresas.codigoCenso')
                                ->where('censosEmpresas.codigoCenso', $actividadId)
                                ->groupBy('codigoCenso');

    return $countEmpresas;
}