atze de groot atze de groot - 5 months ago 29
SQL Question

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'

Answer

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],
    ]);
Comments