How to rewrite this SQL query in Query Builder?

Question

Yii

There is a SQL query:

SELECT
    iu.*, u.banned, u.foto,
    (SELECT SUM(rating)/COUNT(*)
        FROM `olit_insurance_reviews`
        WHERE company_id = u.user_id and rating > 0 AND parent_id IS NULL) as rating,
    (SELECT COUNT(*)
        FROM `olit_insurance_reviews`
        WHERE company_id = u.user_id AND parent_id IS NULL) as reviews_count
    FROM `olit_insurance_users` as iu
    INNER JOIN `olit_users` as u
    ON u.user_id = iu.user_id
    WHERE u.user_group = '6' ORDER BY u.banned DESC, iu.company_name ASC

It needs to be converted to the following format:

->select('name')->from('table')->where(['like', 'yes']);

But I don’t know how to do this - I saw only simple requests, but there are no complex examples. How, for example, nested selects are made, how are algebraic operations done?
Can someone show examples of complex queries on Query Builder?

Answer 1

Look at the doc . Most of your questions are answered there. Here, for example, about nested selects:

$subQuery = (new Query())->select('COUNT(*)')->from('user');

// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post`
$query = (new Query())->select(['id', 'count' => $subQuery])->from('post');

If you don't need models, you can just use DAO

$sql = "SELECT
    iu.*, u.banned, u.foto,
    (SELECT SUM(rating)/COUNT(*)
        FROM `olit_insurance_reviews`
        WHERE company_id = u.user_id and rating > :rating AND parent_id IS NULL) as rating,
    (SELECT COUNT(*)
        FROM `olit_insurance_reviews`
        WHERE company_id = u.user_id AND parent_id IS NULL) as reviews_count
    FROM `olit_insurance_users` as iu
    INNER JOIN `olit_users` as u
    ON u.user_id = iu.user_id
    WHERE u.user_group = :userGroup ORDER BY u.banned DESC, iu.company_name ASC";
$cmd = Yii::$app->db->createCommand($sql, [':userGroup' => 6, ':rating' => 0]);
$items = $cmd->queryAll();