belongsToMany why is returning just the first?

Published 3 months ago by hj_junior

I'm trying to create a table who will group a list of permissions to each user, so I have created this following tables:

Table: N_perfilacesso - Group of permissions (pages)

  • _id (primary key)
  • nome (text)

Table: N_paginas - Just list the pages of system

  • _id (primary key)
  • nome (text)

Table: N_perfilacesso_paginas - List of pages of each group of permissions

  • _id (primary key)
  • _perfilacesso (References to N_perfilacesso)
  • _pagina (References to N_paginas)

So now, the models:

PerfilAcesso

    protected $table = 'N_perfilacesso';
    protected $primaryKey = '_id';
    protected $hidden = ['deleted_at','created_at','updated_at','pivot'];
    public function paginas () {
        return $this->belongsToMany('App\Models\PaginasSistema', 'N_perfilacesso_paginas', '_pagina', '_id');
    }

PerfilAcessoPaginas

    protected $table = 'N_perfilacesso_paginas';
    protected $primaryKey = '_id';
    protected $hidden = ['deleted_at','created_at','updated_at','pivot'];

PaginasSistema

    protected $table = 'N_paginas';
    protected $primaryKey = '_id';
    protected $hidden = ['deleted_at','created_at','updated_at','pivot'];
    public function perfilacesso () {
        return $this->belongsToMany('App\Models\PerfilAcesso', 'N_perfilacesso_paginas', '_pagina', '_id');
    }

So in the controller I find the id of profile of access, and after I want to list the pages

    $perfilacesso = PerfilAcesso::findOrFail($id);
    return response([
        'status' => 'ok',
        'pages' => $perfilacesso->paginas
    ]);

But I'm getting result with just the first of result in array

{
    "status": "ok",
    "paginas": [
        {
            "_id": 1,
            "nome": "evadidos.grafico.instituicao"
        }
    ]
}

Update 2

Now I'm trying to debbug better, I found something that I think its stranger, look eloquent query:

        {
            "query": "select [N_paginas].*, [N_perfilacesso_paginas].[_pagina] as [pivot__pagina], [N_perfilacesso_paginas].[_id] as [pivot__id] from [N_paginas] inner join [N_perfilacesso_paginas] on [N_paginas].[_id] = [N_perfilacesso_paginas].[_id] where [N_perfilacesso_paginas].[_pagina] = ? and [N_paginas].[deleted_at] is null",
            "bindings": [
                1
            ],
            "time": 0
        }

As you can see [N_perfilacesso_paginas].[_pagina] = ? its because of this, eloquent its getting just one, but I don't know how to solve it

Best Answer (As Selected By hj_junior)
hj_junior

I got it do, but I have a lot of changes, I tried to keep everything in the standart of laravel, so know I'm not using prefix and nothing else, so the breaking changes:

Migrations

    public function basetable() {
        $this->integer('id')->autoIncrement()->comment("Identificação");
        $this->timestamp('created_at')->useCurrent()->comment("timestamp de criação");
        $this->timestamp('updated_at')->comment("timestamp de atualização")->nullable();
        $this->timestamp('deleted_at')->nullable()->comment("timestamp de exclusão")->nullble();
    }
    $this->schema->create('perfilacessos', function (CustomBlueprint $table) {
        $table->basetable();
        $table->text('nome');
    });

    $this->schema->create('paginas', function (CustomBlueprint $table) {
        $table->basetable();
        $table->text('nome');
        });

    $this->schema->create('pagina_perfilacessos', function (CustomBlueprint $table) {
        $table->basetable();
        $table->integer('perfilacessos_id');
        $table->integer('pagina_id');
        $table->foreign('perfilacessos_id')->references('id')->on('perfilacessos');
        $table->foreign('pagina_id')->references('id')->on('paginas');
    });

Models

PerfilAcesso -> Perfilacessos (Renamed)

    public function paginas () {
        return $this->belongsToMany(Pagina::class);
    }

PaginasSistema -> Pagina (Renamed)

    public function perfilacesso () {
        return $this->belongsToMany(Perfilacessos::class);
    }
Snapey
Snapey
3 months ago (998,475 XP)

have you told eloquent that you are not using the default primary key name?

https://laravel.com/docs/5.6/eloquent#eloquent-model-conventions

hj_junior

@Snapey Yes, sorry, I've added this information in the blocks of code too and some stranger think that I have see it in the query.

Snapey
Snapey
3 months ago (998,475 XP)

shouldn't your relationships be hasMany and not belongsToMany

hj_junior

@Snapey I think shoud be belongsToMany because of this, look this ER diagram

(https://i.imgur.com/X9q3dhg.png)

PerfilAcesso its a group of 0 or many PaginasSistema and in the middle we have PerfilAcessoPaginasto connect it

As you can see in the query [N_perfilacesso_paginas].[_pagina] = ?, this should be different, how can I make eloquent generate instead [N_perfilacesso_paginas].[_perfilacesso] = ?

Snapey
Snapey
3 months ago (998,475 XP)

I'm not so sure about things when you break with convention. In addition, some parts of eloquent rely on knowing that the relationship name is plural

Both relationships refer to _paginas but one should be _perfilacesso ?

The docs:

In addition to customizing the name of the joining table, you may also customize the column names of the keys on the table by passing additional arguments to the belongsToMany method. The third argument is the foreign key name of the model on which you are defining the relationship, while the fourth argument is the foreign key name of the model that you are joining to:

PerfilAcesso

    public function paginas () {
        return $this->belongsToMany('App\Models\PaginasSistema', 'N_perfilacesso_paginas', '_pagina', '_perfilacesso');
    }

PaginasSistema

    public function perfilacesso () {
        return $this->belongsToMany('App\Models\PerfilAcesso', 'N_perfilacesso_paginas', '_perfilacesso', '_pagina');
    }
hj_junior

I got it do, but I have a lot of changes, I tried to keep everything in the standart of laravel, so know I'm not using prefix and nothing else, so the breaking changes:

Migrations

    public function basetable() {
        $this->integer('id')->autoIncrement()->comment("Identificação");
        $this->timestamp('created_at')->useCurrent()->comment("timestamp de criação");
        $this->timestamp('updated_at')->comment("timestamp de atualização")->nullable();
        $this->timestamp('deleted_at')->nullable()->comment("timestamp de exclusão")->nullble();
    }
    $this->schema->create('perfilacessos', function (CustomBlueprint $table) {
        $table->basetable();
        $table->text('nome');
    });

    $this->schema->create('paginas', function (CustomBlueprint $table) {
        $table->basetable();
        $table->text('nome');
        });

    $this->schema->create('pagina_perfilacessos', function (CustomBlueprint $table) {
        $table->basetable();
        $table->integer('perfilacessos_id');
        $table->integer('pagina_id');
        $table->foreign('perfilacessos_id')->references('id')->on('perfilacessos');
        $table->foreign('pagina_id')->references('id')->on('paginas');
    });

Models

PerfilAcesso -> Perfilacessos (Renamed)

    public function paginas () {
        return $this->belongsToMany(Pagina::class);
    }

PaginasSistema -> Pagina (Renamed)

    public function perfilacesso () {
        return $this->belongsToMany(Perfilacessos::class);
    }

Please sign in or create an account to participate in this conversation.