Yii 2: how to build search query using ActiveRecord between 2 tables?

Question

I used Yii 2.

To be clear:

• The two tables, content and task, is 1:1 relation, task.content_id = content.id;
• and I referenced doc.
• in view file, I used Gridview to show data.
• and I wanna the content is also searchable even it is in another table.

Maybe the following sql can explain what I want:

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;

Here is my controller code：

public function actionIndex()
{
    $searchModel = new TaskSearch;
    $dataProvider = $searchModel->search(Yii::$app->getRequest()->get());

    return $this->render('index', [
        'dataProvider' => $dataProvider,
        'searchModel' => $searchModel,
    ]);
}

and search model code:

public function search($params)
{
    $query = Task::find()->trashed(Task::TRASHED_NO);

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

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

    $query->andFilterWhere(['publish_status' => $this->publish_status]);

    return $dataProvider;
}

I have settled it by searching like clause before and adding the resulted content_id to searchModel query, code like follow:

if (!empty($this->keyword)) {
     $contentIdArr = Content::find()->select('id')->where(['like', 'content', $this->keyword])->column();
     if (empty($contentIdArr)) {
        $contentIdArr = -1;
     }
     $query->andFilterWhere(['content_id' => $contentIdArr]);
}

I wonder is there a way to construct the sql I wrote at begin in Yii 2 ?

thanks for help.

Answer 1

Could you try this:

public function search($params)
{
    $query = Task::find()->trashed(Task::TRASHED_NO);

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

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

    if(!empty($this->keyword))
        $query->leftJoin('content', 'content.id=task.content_id')
            ->andWhere(['publish_status' => 1])
            ->andWhere(['LIKE', 'content', $this->keyword])
            ->orderBy('updated_at');

    return $dataProvider;
}

But the updated_at shouldn't be part of the search, I guess. This is more about sorting. Look here for an example.

Answer 2

I think you can do it by first getting Content data and have a relation with Task model.

You can achieve relation by this link. Relation in Content model:

public function getTask()
{
    return $this->hasOne(Task::className(), ['content_id' => 'id']);
}

and search query

$searchResult = Content::find()
            ->select('content, publish_status')
            ->with('task')
            ->where(['LIKE', 'content', 'keywordInContent'])
            ->andWhere(['publish_status' => 1])
            ->orderBy('updated_at')
            ->limit(20)
            ->all();

I think this might help you.