CODEWITHSUNDEEP
phpmysqlyii2relation
atze de groot
atze de groot

Reputation: 53

Accessing data in Yii2

I'm new to the Yii2 framework and I'm now trying to acces data in Listview using relations. Can somebody please explain why my code isn't working. I want to find a tag which belongs to a document.

Here is a screenshot of my database: enter image description here

Here is my relation:

public function getTags() {
    return $this->hasMany(Tag::className(), ['id' => 'tag_id'])
                    ->viaTable('tbl_document_tag', ['document_id' => 'id']);
}

Here is my Controller:

    public function actionTag() {
     $model = new Search();
     $tag = Yii::$app->getRequest()->getQueryParam('tag');

    //Documents
    $documentModel = new Document;
    $documentSearch = $model->searchDocumentsByTag($documentModel, $tag);

    return $this->render('results', [
        'model' => $model,
        'documentSearch' => $documentSearch,
        'documentModel' => $documentModel
    ]);
}

Here is my view:

    public function searchDocumentsByTag($documentsModel, $keyword) {
    $query = Document::find()
            ->with('tags')
            ->andFilterWhere([
        'or',
        ['like', 'tags.state', 1],
        ['like', 'tags.slug', $keyword],
    ]);

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

I get the following error:

Database Exception – yii\db\Exception

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tags.state' in 'where clause' The SQL being executed was: SELECT COUNT(*) FROM tbl_document WHERE (tags.state LIKE '%1%') OR (tags.slug LIKE '%steekwoord%') Error Info: Array ( [0] => 42S22 1 => 1054 [2] => Unknown column 'tags.state' in 'where clause' ) ↵ Caused by: PDOException

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tags.state' in 'where clause'

Upvotes: 1

Views: 351

Answers (1)

soju
soju

Reputation: 25322

You should simply use joinWith() instead of with() :

This method allows you to reuse existing relation definitions to perform JOIN queries. [...] Note that because a JOIN query will be performed, you are responsible to disambiguate column names.

e.g. :

$query = Document::find()
    ->joinWith('tags tags')
    ->andFilterWhere([
        'or',
        ['like', 'tags.state', 1],
        ['like', 'tags.slug', $keyword],
    ]);

Upvotes: 1

Related Questions