Wherein problem using array

Question

I have a system where I need to list some employees. This listing will depend on the supervisor’s department code.

Ex: If the supervisor is from department 206, he can access all data from the departments' servers: (205,206,207,209);

Therefore, I am trying to query using Wherein and passing an array as parameter. However, I am not succeeding. I don’t know how to fix this situation.

So I’ll explain below how this part of the system works so you can help me if possible.

The function lotacaoSaude serves to return an array of departments that the supervisor can access.

$dep = Session::get('dep');

$deps = lotacaoSaude($dep);

Below is the way I mount my query:

$servidores = DB::table('sch_sismapa.tb_servidor')
        ->join('sch_decom.tb_servidor', 'sch_sismapa.tb_servidor.nr_matricula', '=', 'sch_decom.tb_servidor.nr_matricula')
        ->select('sch_decom.tb_servidor.nm_servidor', 'sch_sismapa.tb_servidor.*')
        ->where('sch_sismapa.tb_servidor.cd_secretaria','10')
        ->whereIN('sch_sismapa.tb_servidor.cd_departamento',[$deps])
        ->where('sch_sismapa.tb_servidor.id_referencia', '8')
        ->orderBy('sch_decom.tb_servidor.nm_servidor', 'asc')
        ->orderby('sch_decom.tb_servidor.cd_departamento', 'asc')
        ->get();

The way I did it presents the following error:

SQLSTATE[HY093]: Invalid Parameter number: Parameter was not defined

Remember that the cd_department column in the database is of the type integer

Answer 1

According to Laravel’s documentation Where clauses the method whereIn checks whether a given column is contained in a given array.

The example given in the documentation is:

   $users = DB::table('users')
                ->whereIn('id', [1, 2, 3])
                ->get();

Making it clear that in this case the method whereIn will compare the column value id with 1, 2 or 3 and return the record only if 'id' equals one of these values.

In your case $deps is already an array so do [$deps] equals to create an array whose only element is an array.

[$deps] == [ [205, 206, 207, 209] ];

Then usewhereInwith the parameter [$deps]:

->whereIn('sch_sismapa.tb_servidor.cd_departamento', [$deps])

Amounts to :

->whereIn('sch_sismapa.tb_servidor.cd_departamento', [ [205, 206, 207, 209] ])

In other words, only the record whose department is the array would be returned [205, 206, 207, 209] what should not exist in your database.

Assuming your query is correct and only the spelling of whereIn, orderBy and the query array is compromised, the code should look like this:

$servidores = DB::table('sch_sismapa.tb_servidor')
        ->join('sch_decom.tb_servidor', 'sch_sismapa.tb_servidor.nr_matricula', '=', 'sch_decom.tb_servidor.nr_matricula')
        ->select('sch_decom.tb_servidor.nm_servidor', 'sch_sismapa.tb_servidor.*')
        ->where('sch_sismapa.tb_servidor.cd_secretaria','10')
        ->whereIn('sch_sismapa.tb_servidor.cd_departamento', $deps)
        ->where('sch_sismapa.tb_servidor.id_referencia', '8')
        ->orderBy('sch_decom.tb_servidor.nm_servidor', 'asc')
        ->orderBy('sch_decom.tb_servidor.cd_departamento', 'asc')
        ->get();