PHP Laravel 5.1 - Eloquent select using "with()" not working

Question

I'm trying to make a select with specific tables in the database.

Here the "Funcionario" model:

class Funcionario extends Model
{
    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'funcionarios';


    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = ['nome', 'matricula', 'pis_pasep', 'data_admissao', 'data_demissao', 'data_nascimento', 'apelido', 'sexo_id', 'setor_id', 'cargo_id', 'turno_id'];

     /**
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function cargo()
    {
        return $this->belongsTo('App\Cargo');
    }

}

Here is the "Cargo" Model:

class Cargo extends Model
{
    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'cargos';


    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = ['nome'];

    /**
     * Belongs to 'funcionarios' relationship
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function funcionario()
    {
        return $this->hasMany('App\Funcionario');
    }
}

This is what I'm trying now:

public function showMyEmployees(){

        $data = Funcionario::where('supervisor_id', $user->id)
            ->with([
                'cargo' => function($query){
                    $query->select('nome'); // $query->pluck('nome'); // $query->get(['nome']);
                }
            ])
            ->orderBy('nome')
            ->get();

        return response()->json($data, 200);
    }

What I'm getting:

{
    "id": 1648,
    "nome": "ADOLFO ARAUJO DOS SANTOS JUNIOR",
    "matricula": 14311,
    "pis_pasep": 0,
    "data_admissao": "1970-01-01",
    "data_demissao": "1970-01-01",
    "data_nascimento": null,
    "apelido": null,
    "supervisor_id": 1105,
    "coordenador_id": null,
    "gerente_id": null,
    "diretor_id": null,
    "sexo_id": null,
    "setor_id": 36,
    "cargo_id": 56,
    "turno_id": null,
    "created_at": "2015-09-15 14:49:32",
    "updated_at": "2015-09-15 15:58:36",
    "cargo": null
  }

And "cargo" should has a value not null.

If I don't use the closure in the select eloquent, it returns with the "nome" in "cargo".

Like this:

{
    "id": 1648,
    "nome": "ADOLFO ARAUJO DOS SANTOS JUNIOR",
    "matricula": 14311,
    "pis_pasep": 0,
    "data_admissao": "1970-01-01",
    "data_demissao": "1970-01-01",
    "data_nascimento": null,
    "apelido": null,
    "supervisor_id": 1105,
    "coordenador_id": null,
    "gerente_id": null,
    "diretor_id": null,
    "sexo_id": null,
    "setor_id": 36,
    "cargo_id": 56,
    "turno_id": null,
    "created_at": "2015-09-15 14:49:32",
    "updated_at": "2015-09-15 15:58:36",
    "cargo": {
      "id": 56,
      "nome": "AUXILIAR DE PRODUCAO",
      "created_at": "2015-09-15 14:47:18",
      "updated_at": "2015-09-15 14:47:18"
    }

..And this is what I want:

{
    "id": 1648,
    "nome": "ADOLFO ARAUJO DOS SANTOS JUNIOR",
    "matricula": 14311,
    "pis_pasep": 0,
    "data_admissao": "1970-01-01",
    "data_demissao": "1970-01-01",
    "data_nascimento": null,
    "apelido": null,
    "supervisor_id": 1105,
    "coordenador_id": null,
    "gerente_id": null,
    "diretor_id": null,
    "sexo_id": null,
    "setor_id": 36,
    "cargo_id": 56,
    "turno_id": null,
    "created_at": "2015-09-15 14:49:32",
    "updated_at": "2015-09-15 15:58:36",
    "cargo": "AUXILIAR DE PRODUCAO"
}

Thanks!

Answer 1

As I wanted to avoid DB class facade, I decided to use a foreach loop to put a new value in "cargo".

It will be like:

public function showMyEmployees(){

    $data = Funcionario::where('supervisor_id', $user->id)
        ->orderBy('nome')
        ->get();

    $funcionarios = array();
    foreach($data as $funcionario){
        $funcionario->cargo = $funcionario->cargo()->pluck('nome');
        $funcionario->setor = $funcionario->setor()->pluck('nome');

        array_push($funcionarios, $funcionario);
    }

    return response()->json($funcionarios, 200);
}

Answer 2

You need to add "id" to your select(), as eloquent need it for comparison. I think you are better off not using eloquent in this case, since you just want to return a string for the cargo key.

DB::table('funcionarios')
    ->join('cargos', 'cargos.id', '=', 'funcionarios.cargo_id')
    ->get(['funcionarios.*', 'cargos.nome AS cargo']);