How to translate this SQL query?

Question

Yii

You need to build such a query to the search model:

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('Русский'))
  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('Твердый'))

What is the best way to organize nested and selects?

Answer 1

I solved the problem, for those who are interested - here is 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]);
        }

Answer 2

How to create queries with subqueries - RTFM https://www.yiiframework.com/doc/guide/2.0/en/db-q... (search on the page for "sub-query").
PS Your SQL basically does not work. A subquery can only be used in place of a value, not a field name.
This govnokod will slow down terribly.
Language, cover type, category_id should be numeric indexed fields. And search by ID, not text.