Returning related data records in yii2 using relations

Question

I would like to get data of a user and his roles but it returns an error of:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'auth_item.user_id' in 'on clause' The SQL being executed was: SELECT COUNT(*) FROM `tblusers` LEFT JOIN `auth_item` ON `tblusers`.`id` = `auth_item`.`user_id`

I have tried: Search model

$query = User::find()->joinWith('role');

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'sort'=> ['defaultOrder' => ['id'=>SORT_ASC]],
        'pagination' => ['pageSize' => $pageSize]
    ]);

    return $dataProvider;

Relationship on the user model

public function getRole()
{
    // User has_one Role via Role.user_id -> id
    return $this->hasOne(Role::className(), ['user_id' => 'id']);
}

Relationship on the role model

public function getUser()
{
     return $this->hasMany(User::className(), ['id' => 'user_id']);
}

This is the full search:

public function search($params, $pageSize = 10)
{
   $query = User::find()->joinWith('role');

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'sort'=> ['defaultOrder' => ['id'=>SORT_ASC]],
        'pagination' => ['pageSize' => $pageSize]
    ]);

    return $dataProvider;
    die();

    // if user is not 'theCreator' ( You ), do not show him users with this role
     // if user is not 'theCreator' ( You ), do not show him users with this role
    if (Yii::$app->user->can('theCreator')) {
        $query->where(['!=', 'item_name', 'theCreator']);
    }

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'sort'=> ['defaultOrder' => ['id'=>SORT_ASC]],
        'pagination' => ['pageSize' => $pageSize]
    ]);

    // make item_name (Role) sortable
    $dataProvider->sort->attributes['item_name'] = [
        'asc' => ['item_name' => SORT_ASC],
        'desc' => ['item_name' => SORT_DESC],
    ];

    if (!($this->load($params) && $this->validate())) {
        return $dataProvider;
    }

    $query->andFilterWhere([
        'id' => $this->id,
        'status' => $this->status,
        'created_at' => $this->created_at,
        'updated_at' => $this->updated_at,
    ]);

    $query->andFilterWhere(['like', 'username', $this->username])
          ->andFilterWhere(['like', 'email', $this->email])
          ->andFilterWhere(['like', 'item_name', $this->item_name]);

    return $dataProvider;
}

This is the authitem model

<?php 
  namespace app\rbac;

  use yii\db\ActiveRecord;
  use Yii;

    class AuthItem extends ActiveRecord
    {  public static function tableName()
   { 
    return '{{%auth_item}}';
   }
   public static function getRoles()
   {
    if (Yii::$app->user->can('theCreator')) 
    {
        return static::find()->select('name')->where(['type' => 1])->all();  
    }
    else
    {
        return static::find()->select('name')
                             ->where(['type' => 1])
                             ->andWhere(['!=', 'name', 'theCreator'])
                             ->all();
    }
  }        
  }

THIS IS THE CODE UPDATE: On this line

$query = User::find()->joinWith('role'); 

it actually relates to auth assignment as below:

<?php
namespace app\rbac;

  use app\models\User;
     use yii\db\ActiveRecord;
    use Yii;
     class Role extends ActiveRecord
   {
  public static function tableName()
   {
    return '{{%auth_assignment}}';
   }
   public function rules()
        {
    return [
        [['item_name'], 'required'],
        [['item_name'], 'string', 'max' => 64],
    ];
}

 public function attributeLabels()
{
    return [
        'item_name' => Yii::t('app', 'Role'),
    ];
}


public function getUser()
{
    // Role has_many User via User.id -> user_id
    return $this->hasMany(User::className(), ['id' => 'user_id']);
}


}

Why does it return that error?

Answer 1

If you are using the default rbac models/module from yii2 could be you are relating the wrong model/table .. because the auth_itme model don't contain user_id column

/**
* This is the model class for table "auth_item".
*
* @property string $name
* @property integer $type
* @property string $description
* @property string $rule_name
* @property string $data
* @property integer $created_at
* @property integer $updated_at

Could be instead that you want relate the auth_assignment table which contain relation between auth_item and user

/**
 * This is the model class for table "auth_assignment".
 *
 * @property string $item_name
 * @property string $user_id
 * @property integer $created_at
 *