title: Yii 2: how to build search query using ActiveRecord between 2 tables?
body: <p>I used Yii 2.</p>
  
  <p>To be clear:</p>
  
  <ul>
  <li>The two tables, content and task, is 1:1 relation, task.content_id =
  content.id;</li>
  <li>and I referenced
  <a href="http://www.yiiframework.com/doc-2.0/guide-output-data-widgets.html#filtering-data" rel="nofollow">doc</a>.</li>
  <li>in view file, I used Gridview to show data.</li>
  <li>and I wanna the content is also searchable even it is in another
  table.</li>
  </ul>
  
  <p>Maybe the following sql can explain what I want:</p>
  
  <pre><code>SELECT 
      c.content, 
      t.publish_status 
  FROM 
      content c, task t 
  WHERE 
      c.content LIKE '%keywordInContent%' AND
      t.publish_status = 1 AND
      c.id = t.content_id 
  ORDER BY 
      updated_at 
  LIMIT 20;
  </code></pre>
  
  <p>Here is my controller code：</p>
  
  <pre><code>public function actionIndex()
  {
      $searchModel = new TaskSearch;
      $dataProvider = $searchModel-&gt;search(Yii::$app-&gt;getRequest()-&gt;get());
  
      return $this-&gt;render('index', [
          'dataProvider' =&gt; $dataProvider,
          'searchModel' =&gt; $searchModel,
      ]);
  }
  </code></pre>
  
  <p>and search model code:</p>
  
  <pre><code>public function search($params)
  {
      $query = Task::find()-&gt;trashed(Task::TRASHED_NO);
  
      $dataProvider = new ActiveDataProvider([
          'query' =&gt; $query,
      ]);
  
      if (!($this-&gt;load($params) &amp;&amp; $this-&gt;validate())) {
          return $dataProvider;
      }
  
      $query-&gt;andFilterWhere(['publish_status' =&gt; $this-&gt;publish_status]);
  
      return $dataProvider;
  }
  </code></pre>
  
  <p>I have settled it by searching like clause before and adding the resulted content_id to searchModel query, code like follow:</p>
  
  <pre><code>if (!empty($this-&gt;keyword)) {
       $contentIdArr = Content::find()-&gt;select('id')-&gt;where(['like', 'content', $this-&gt;keyword])-&gt;column();
       if (empty($contentIdArr)) {
          $contentIdArr = -1;
       }
       $query-&gt;andFilterWhere(['content_id' =&gt; $contentIdArr]);
  }
  </code></pre>
  
  <p>I wonder is there a way to construct the sql I wrote at begin in Yii 2 ?</p>
  
  <p>thanks for help.</p>