How to translate this SQL query?

You need to build a query to search models:
SELECT id, name FROM `products`
WHERE (`category_id`='КН3')
AND EXISTS(SELECT * FROM product_book WHERE product_book.product_id = products.id 
 AND product_book.book_name = 'book_language' 
 AND (SELECT name FROM book_language WHERE id = product_book.book_id) IN('Russian'))
 AND EXISTS(SELECT * FROM product_book WHERE product_book.product_id = products.id 
 AND product_book.book_name = 'book_binding' 
 AND (SELECT name FROM book_binding WHERE id = product_book.book_id) IN('Solid'))

How to organize and nested select?
March 19th 20 at 08:52
2 answers
March 19th 20 at 08:54
Solution
Problem solved, who cares - here's the code:
if(!empty($this->author)){
 $sql = new Expression("(SELECT name FROM book_authors WHERE id = product_book.book_id)");
 $row = (new Query())
->from('product_book')
->where('product_book.product_id=products.id')
 ->andWhere(['product_book.book_name' => 'book_authors'])
 ->andFilterWhere(['in', $sql, $this->author]);

 $query->andFilterWhere(['exists', $row]);
 }
March 19th 20 at 08:56
How to create queries with subqueries - RTFM https://www.yiiframework.com/doc/guide/2.0/en/db-q... (search the page for "sub-query").

PS Your SQL basically does not work. A subquery can only be used instead of the value, not the field name.
This bad code will be terribly slow.
Language, type of cover, category_id need to do a numeric-indexed fields. And look at the ID, not the text.

Find more questions by tags Yii