Crud: show pivot table as a crud / return name instead of id

[kpapag]

Not quite sure if its a bug

What I did

I have 2 models, Users and Apps, with fields (id, name, etc.) and because of the n-n relation, a third one (pivot) using belongsToMany relation on both models. I need to be able to list the pivot via CRUD and update some extra fields i have set in each record in it, so i made another model called UserApps.

What I expected to happen

The relations on Users and Apps models and select n-n relationship (with pivot table) for column value works fine, even in their CRUD's. That means that when i list Users or Apps, with select_multiple column i can successfully retrieve the 'name' field instead of the id.

What happened

UserApps CRUD list keeps retrieving the id's stored in the DB, while i want to show the 'name' instead. Meaning that i keep getting

id | User | App | whateverField

1 | 2 | 1 | blablah
2 | 3 | 4 | blablah

when i should be getting

id | User | App | whateverField

1 | George | Photoshop | blablah
2 | John | MS Office | blablah

What I've already tried to fix it

I tried to use the relations on User and Apps model so i can return the name instead of the id, as i did with UserCrudController and AppsCrudController, however it wont do the trick. I should mention that i already set foreign_key on both User and App table in my migration, when creating the user_apps table.

Do i need to make new functions in my UserApps model in order to use them for the crudController? I am kind of getting lost here...

Any help would be much appreciated. thank you in advance

Backpack, Laravel, PHP, DB version

Backpack: 3.6, Laravel 5.8, PHP: 7.3.5 MySQL: 5.7

[welcome[bot]]

Hello there! Thanks for opening your first issue on this repo!

Just a heads-up: Here at Backpack we use Github Issues only for tracking bugs. Talk about new features is also acceptable. This helps _a lot_ in keeping our focus on improving Backpack. If you issue is not a bug/feature, please help us out by closing the issue yourself and posting in the appropriate medium (see below). If you're not sure where it fits, it's ok, a community member will probably reply to help you with that.

Backpack communication mediums:

• Bug Reports, Feature Requests - Github Issues (here);
• Quick help (_How do I do X_) - Gitter Chatroom;
• Long questions (_I have done X and Y and it won't do Z wtf_) - Stackoverflow, using the backpack-for-laravel tag;

Please keep in mind Backpack offers no official / paid support. Whatever help you receive here, on Gitter, Slack or Stackoverflow is thanks to our awesome _awesome_ community members, who give up some of their time to help their peers. If you want to join our community, just start pitching in. We take pride in being a welcoming bunch.

Thank you!

--
Justin Case
The Backpack Robot

[jsvini]

@kpapag if the UserApp model has the User and App relations you can use them directly in the column name in dot notation, like this:

$this->crud->addColumns( [
    [
        'label' => 'App',
        'name' => 'app.name', // relation.column_name
    ],
    [
        'label' => 'User',
        'name' => 'user.name',
    ],
] );

Also add a $this->crud->with(['app', 'user']) in setup method to eager load the relation and avoid multiple queries.

[kpapag]

@kpapag if the UserApp model has the User and App relations you can use them directly in the column name in dot notation, like this:

$this->crud->addColumns( [
    [
        'label' => 'App',
        'name' => 'app.name', // relation.column_name
    ],
    [
        'label' => 'User',
        'name' => 'user.name',
    ],
] );

Also add a $this->crud->with(['app', 'user']) in setup method to eager load the relation and avoid multiple queries.

@jsvini thank you for your reply. Unfortunately, none of the above worked. I will post here my code in case it helps you to better understand where my error (or bug?) might be.

[kpapag]

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use App\Models\BackpackUser;
use Backpack\Base\app\Models\Traits\InheritsRelationsFromParentModel;
use Backpack\CRUD\CrudTrait;
use Backpack\CRUD\ModelTraits\SpatieTranslatable\HasTranslations;

class MyUser extends BackpackUser
{
    use InheritsRelationsFromParentModel;
    use HasTranslations;
    use CrudTrait;

protected $table = 'users';

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', //some more vars
    ];

    public function apps()
    {
        return $this->belongsToMany('App\Models\MyApp', 'users_apps', 'user_id', 'app_id')->withTimestamps();
    }

}

```php
namespace App\Models;

use Backpack\CRUD\CrudTrait;
use Backpack\CRUD\ModelTraits\SpatieTranslatable\HasTranslations;
use Illuminate\Database\Eloquent\Model;

class MyApp extends Model
{
use HasTranslations;
use CrudTrait;

protected $table = 'apps';

protected $fillable = ['name','description'];

public function users()
{
    return $this->belongsToMany('App\Models\MyUser')->using('App\Models\MyUserApps')->withPivot('app_name')->withTimestamps();
}

}

```php
namespace App\Models;

use Backpack\CRUD\CrudTrait;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\Pivot;

class MyUserApps extends Pivot
{
    use CrudTrait;
    /**
     * Indicates if the IDs are auto-incrementing.
     *
     * @var bool
     */
    public $incrementing = true;

    protected $table = 'users_apps';

    protected $fillable = [
        //some vars i want to crud via backpack
    ];
}

the migration function for creating this db

public function up()
{
        Schema::enableForeignKeyConstraints();        
        Schema::create('users_apps', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('user_id');
            $table->unsignedBigInteger('app_id');
  $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade')->onUpdate('cascade');
  $table->foreign('app_id')->references('id')->on('apps')->onDelete('cascade')->onUpdate('cascade');
            $table->timestamps();
    //some vars i want to crud via backpack, same as MyUserApps model          
        });
    }

and now, part of the crud controller for UserApps

namespace App\Http\Controllers\Admin;

use Backpack\CRUD\app\Http\Controllers\CrudController;
use App\Http\Requests\MyUserAppsRequest as StoreRequest;
use App\Http\Requests\MyUserAppsRequest as UpdateRequest;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Hash;

class MyUserAppsCrudController extends CrudController
{
    public function setup()
    {

        /*
        |--------------------------------------------------------------------------
        | BASIC CRUD INFORMATION
        |--------------------------------------------------------------------------
        */


        $this->crud->setModel('App\Models\MyUserApps');
        $this->crud->setRoute(backpack_url('myuserapps'));
        $this->crud->setEntityNameStrings('myuserapp', 'myuserapps');


        // Columns.

        $this->crud->setColumns([
            [
                'name'  => 'user_id',
                'label' => 'User',
                'type'  => 'text',
            ],
            /*[
             // n-n relationship (with pivot table)
             'name' => 'user_id', // the column that contains the ID of that connected entity;
             'label' => "User", // Table column heading
             'type' => "select",
             'entity' => 'users', // the method that defines the relationship in your Model
             'attribute' => "user.name", // foreign key attribute that is shown to user
             'model' => "App\Models\MyUser", // foreign key model
             ],*/
            [
                'name'  => 'app_id',
                'label' => 'Application',
                'type'  => 'text',
            ],
            /*[
            // n-n relationship (with pivot table)
                'name' => 'apps', // the method that defines the relationship in your Model
                'label' => "Apps", // Table column heading
                'type' => "select",
                'entity' => 'apps', // the method that defines the relationship in your Model
                'attribute' => "name", // foreign key attribute that is shown to user
                'model' => "App\Models\MyApp", // foreign key model
             ],*/

            //other vars
        ]);

as of now, it displays the id's correctly but if i try the commented code in the crud to list the names instead of the id's, i get the "error loading page, please refresh the page"

[jsvini]

@kpapag you need to add the relations to the model used by the CrudController, in this case MyUserApps:

namespace App\Models;

use Backpack\CRUD\CrudTrait;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\Pivot;

class MyUserApps extends Pivot
{
    use CrudTrait;
    /**
     * Indicates if the IDs are auto-incrementing.
     *
     * @var bool
     */
    public $incrementing = true;

    protected $table = 'users_apps';

    protected $fillable = [
        //some vars i want to crud via backpack
    ];

    public function app(){
        return $this->hasOne('App\Models\MyApp', 'id', 'app_id');
    }

    public function user(){
        return $this->hasOne('App\Models\MyUser', 'id', 'user_id');
    }
}

Then you will be able to use:

$this->crud->addColumns( [
    [
        'label' => 'App',
        'name' => 'app.name', // relation.column_name
    ],
    [
        'label' => 'User',
        'name' => 'user.name',
    ],
] );

[kpapag]

Indeed it worked now, needed hasOne relation. thanks a ton!

[guillorrr]

Hi! Can I ask how to addField in CrudController to assign a value to 'withpivot' column? Thanks

[kpapag]

Hi! Can I ask how to addField in CrudController to assign a value to 'withpivot' column? Thanks

Hello,

For the example above, it would be
[ // Select2Multiple = n-n relationship (with pivot table) 'label' => trans('backpack::myuser.apps'), 'type' => 'select2_multiple', 'name' => 'apps', // the method that defines the relationship in your Model 'entity' => 'apps', // the method that defines the relationship in your Model 'attribute' => 'name', // foreign key attribute that is shown to user 'model' => "App\Models\MyApp", // foreign key model 'pivot' => true, // on create&update, do you need to add/delete pivot table entries? 'select_all' => true, // show Select All and Clear buttons? ]

[guillorrr]

thank you for your anwser! I didn't understand yet in your example how to you pass value to column 'app_name' in with_pivot on MyApp model:

public function users() { return $this->belongsToMany('App\Models\MyUser')->using('App\Models\MyUserApps')->withPivot('app_name')->withTimestamps(); }