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!

Chris Magnussen · Accepted Answer · 2015-09-15 16:35:15Z

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']);

answered Sep 15, 2015 at 16:35

Chris Magnussen

1,5379 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Leo Ribeiro Over a year ago

I was trying to avoid using DB class...I think it's lil dirty (personal opinion). I think I gonna use a foreach loop anyway. Something like "foreach($resultado as $it) { $it->cargo = $it->cargo->nome; }".

Chris Magnussen Over a year ago

I agree. Eloquent is awesome, but it can't do everything, so (not) often you'll find yourself in a situation where you have to use the DB facade.

Leo Ribeiro Over a year ago

@HPM Done. Sorry for late. ;-)

Leo Ribeiro · Accepted Answer · 2015-09-15 17:18:15Z

0

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);
}

edited Sep 15, 2015 at 17:18

answered Sep 15, 2015 at 17:13

Leo Ribeiro

1,2851 gold badge15 silver badges27 bronze badges

3 Comments

Chris Magnussen Over a year ago

This is not a good practice, Leo. You are running 2 new queries for every iteration. Consider lazy loading your relations: $data->load('cargo', 'setor'); or eager loading as you did earlier.

Leo Ribeiro Over a year ago

It's true. Actually, I've never used the "load()" method. I'll try it. Thanks again, Chris!

Leo Ribeiro Over a year ago

Hey Chris, I've tried it, but the response was a JSON for "cargo", not a string (what I really wanted). Instead of {"cargo" : "Something"}, I've got {"cargo" : {"id" : 99, "nome" : "Something", "created_at" : "2015-09-15..."}}. I just wanted the "nome" value of the "cargo".

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related