How to group and sort data in AR?

Question

MySQL

There are two tables. Product (id, title, etc.) and SalonProduct (id, product_id, salon_id, etc.)
There is a relationship in the Product model there is a SalonProduct relationship.
You must first select all the Products for which the SalonProduct connection does not return null, and then add
it. Tried like this:

$query = Product::find()->joinWith('salonProduct')->andWhere(['<>','salon_product',null]);
        $query_null = Product::find()->joinWith('salonProduct')->andWhere(['salon_product'=>null]);
        $query->union($query_null);

Mistake.
Throw an idea.
Actually I should be fine with this:

$query = Product::find()->joinWith('salonProduct');
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => [
                'pageSize' => 24,
            ],
            'sort' => [
                'defaultOrder' => [
                    'salon_product.product_id' => SORT_ASC
                ]
            ],
        ]);

But again a mistake. Invalid argument supplied for foreach() in yiisoft\yii2\data\Sort.php

Answer 1

SELECT * FROM table ORDER BY CASE WHEN salon_id = NULL THEN 1 ELSE 0 END;

How it works can be checked here

Зайти сюда www.w3schools.com/sql/trysql.asp?filename=trysql_s...
Вписать это -

SELECT * FROM Customers
ORDER BY CASE WHEN City = "London" THEN 0
    ELSE 1
END;

И результат вроде тот, что вам нужен.

Answer 2

You don't have to create an additional field in the database. You can use the If construct inside Select and display the result of this function as an additional column.
In pure SQL it would look like this:

SELECT *, IF(`salon_product`.`salon_id` IS NULL, 0, 1) AS `mark` 
FROM `salon_product` 
ORDER BY `mark` DESC

In your case, the following code should work:

$c = new CDbCriteria();
$c->select = '*, , IF(`sp`.`salon_id` IS NULL, 0, 1) AS `mark` ';
$c->join = 'LEFT JOIN `SalonProduct` `sp` ON `t`.`id` = `sp`.`product_id`';
$c->order = '`mark` DESC';
$products = Product::model()->findAll($c);