title: Returning related data records in yii2 using relations
body: <p>I would like to get data of a user and his roles but it returns an error of:</p>
  
  <blockquote>
    <p>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`</p>
  </blockquote>
  
  <p>I have tried:
  Search model</p>
  
  <pre><code>$query = User::find()-&gt;joinWith('role');
  
      $dataProvider = new ActiveDataProvider([
          'query' =&gt; $query,
          'sort'=&gt; ['defaultOrder' =&gt; ['id'=&gt;SORT_ASC]],
          'pagination' =&gt; ['pageSize' =&gt; $pageSize]
      ]);
  
      return $dataProvider;
  </code></pre>
  
  <p>Relationship on the user model</p>
  
  <pre><code>public function getRole()
  {
      // User has_one Role via Role.user_id -&gt; id
      return $this-&gt;hasOne(Role::className(), ['user_id' =&gt; 'id']);
  }
  </code></pre>
  
  <p>Relationship on the role model</p>
  
  <pre><code>public function getUser()
  {
       return $this-&gt;hasMany(User::className(), ['id' =&gt; 'user_id']);
  }
  </code></pre>
  
  <p>This is the full search:</p>
  
  <pre><code>public function search($params, $pageSize = 10)
  {
     $query = User::find()-&gt;joinWith('role');
  
      $dataProvider = new ActiveDataProvider([
          'query' =&gt; $query,
          'sort'=&gt; ['defaultOrder' =&gt; ['id'=&gt;SORT_ASC]],
          'pagination' =&gt; ['pageSize' =&gt; $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-&gt;user-&gt;can('theCreator')) {
          $query-&gt;where(['!=', 'item_name', 'theCreator']);
      }
  
      $dataProvider = new ActiveDataProvider([
          'query' =&gt; $query,
          'sort'=&gt; ['defaultOrder' =&gt; ['id'=&gt;SORT_ASC]],
          'pagination' =&gt; ['pageSize' =&gt; $pageSize]
      ]);
  
      // make item_name (Role) sortable
      $dataProvider-&gt;sort-&gt;attributes['item_name'] = [
          'asc' =&gt; ['item_name' =&gt; SORT_ASC],
          'desc' =&gt; ['item_name' =&gt; SORT_DESC],
      ];
  
      if (!($this-&gt;load($params) &amp;&amp; $this-&gt;validate())) {
          return $dataProvider;
      }
  
      $query-&gt;andFilterWhere([
          'id' =&gt; $this-&gt;id,
          'status' =&gt; $this-&gt;status,
          'created_at' =&gt; $this-&gt;created_at,
          'updated_at' =&gt; $this-&gt;updated_at,
      ]);
  
      $query-&gt;andFilterWhere(['like', 'username', $this-&gt;username])
            -&gt;andFilterWhere(['like', 'email', $this-&gt;email])
            -&gt;andFilterWhere(['like', 'item_name', $this-&gt;item_name]);
  
      return $dataProvider;
  }
  </code></pre>
  
  <p>This is the authitem model</p>
  
  <pre><code>&lt;?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-&gt;user-&gt;can('theCreator')) 
      {
          return static::find()-&gt;select('name')-&gt;where(['type' =&gt; 1])-&gt;all();  
      }
      else
      {
          return static::find()-&gt;select('name')
                               -&gt;where(['type' =&gt; 1])
                               -&gt;andWhere(['!=', 'name', 'theCreator'])
                               -&gt;all();
      }
    }        
    }
  </code></pre>
  
  <p>THIS IS THE CODE UPDATE:
  On this line  </p>
  
  <pre><code>$query = User::find()-&gt;joinWith('role'); 
  </code></pre>
  
  <p>it actually relates to auth assignment as below:</p>
  
  <pre><code>&lt;?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' =&gt; 64],
      ];
  }
  
   public function attributeLabels()
  {
      return [
          'item_name' =&gt; Yii::t('app', 'Role'),
      ];
  }
  
  
  public function getUser()
  {
      // Role has_many User via User.id -&gt; user_id
      return $this-&gt;hasMany(User::className(), ['id' =&gt; 'user_id']);
  }
  
  
  }
  </code></pre>
  
  <p>Why does it return that error?</p>