How to translate mysql query to Active Record Yii2?

Question

MySQL

There is a query that worked fine for a long time on my handwritten site until the time when I wanted to cut all my sites under Yii2 at the same time and deal with this basis.

SELECT i.id, i.src_small as src FROM ideas i JOIN ideas_filters f USING (id) WHERE f.filter IN ($array) GROUP BY i.id HAVING COUNT(DISTINCT f.filter) = '.count($array),' ORDER BY i.id DESC

In Yii2 I'm using ActiveRecord , query without my perversions:

$query = Ideas::find()->select('id, src_small')->where(['theme' => $theme->id])->orderBy('id DESC');

It will be easy for a Yii2 specialist to translate this query into the required format. Helpme.
In general, any variants of this request are of interest, because maybe someone has a better option than mine.
There is a table with ideas , there is a table with filters , each idea can have many filters, there is a linking table filters_ideas . The task is to display all the ideas for the selected filters.

Answer 1

Ideas::find()
    ->select(['i.id', 'i.src_small as src']) // Возможно просто одной сторокой select('i.id, i.src_small as src'). Проверить под рукой правильность нет.
    ->from(['i' => Ideas::tableName()])
    ->join('JOIN', ['f' => IdeasFilters::tableName()], 'f.id=i.id') // USING в yii2 нету поэтому так
    ->where(['f.filter' => $array]) // $array чистый массив в 1 колонку с числовыми индексами 0,1,2 etc. В общем обычный массив вида [23,43,52]
    ->groupBy('i.id')
    ->having(['COUNT(DISTINCT f.filter)' => count($array)])
    ->orderBy(['i.id' => SORT_DESC])
    ->all(); // или one() смотря что нужно.

Classes are named as standard when creating models through gii. If you have other models, then change the name. In general, it should work.
PS
I forgot. The Ideas model will need to add the
Because. it is not there by default, but for an AR object it will be used (there is a query in the request i.src_small as src). However, Yii will not allow you to create this property "on the fly", and therefore you need to register it.

Answer 2

Ну вы как минимум все еще можете использовать голый sql в ActiveRecord