There is a sql query:

SELECT * FROM article a INNER JOIN category_article ca ON a.id=ca.article_id WHERE ca.category_id = 2 AND EXISTS( SELECT 1 FROM category_article WHERE article_id = a.id AND category_id = 9 )

For Active Record rewrote it this way:

 $query = Article::find(); $query->innerJoinWith(['categories'], false)->andWhere(['category.id' => 2]); $query->andWhere(['exists', (new \yii\db\Query()) ->from('category_article') ->where(['article_id' => 'article.id']) // Указать поле нужно тут ->andWhere(['category_id' => 9]) ]); $query->all()

The problem with ActiveRecord is that in ->where(['article_id' => 'article.id']) value of article.id not perceived as the id field of the article table, but as a text value. How can I specify the field of the article table in the condition?

    2 answers 2

    Probably so:

     ->where(['article_id' => yii\db\Expression('article.id')])

      According to the canons, you do not need this condition if the connections are specified in these entities.

      In Category_Article

       public function getArticles() { return $this->hasMany(Article::className(), ['id' => 'article_id']); }

      In Article

       public function getCategory() { return $this->hasOne(CategoryArticles::className(), ['article_id' => 'id']); }

      And here is the request itself

       $query = Article::find(); $query->innerJoinWith(['category'], false)->andWhere(['category.id' => 2]); $query->andWhere(['exists', (new \yii\db\ActiveQuery()) ->from('category_article') ->with(['article']) // Указать поле нужно тут ->andWhere(['category_id' => 9]) ]); $query->all()
      • Common sense tells me that you are right, but I can not figure out how to change the request using these links - Jeque
      • @EvgeniiZaets supplemented the answer - Peresada
      • So this is a query builder. \ yii \ db \ Query () has no with () method - Jeque
      • @EvgeniiZaets Do you use it in principle? Replace it with \ yii \ db \ ActiveQuery () - Peresada