I need to do a sort. Means yii2, according to the documentation is done as follows.

function actionIndex() { $sort = new Sort([ 'attributes' => [ 'age', 'name' => [ 'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC], 'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC], 'default' => SORT_DESC, 'label' => 'Name', ], ], ]); $models = Article::find() ->where(['status' => 1]) ->orderBy($sort->orders) ->all(); return $this->render('index', [ 'models' => $models, 'sort' => $sort, ]); }

Works fine. But, I need a little harder query to do. Here is a request

 SELECT * , (object_info.price / ( SELECT price_type.course FROM price_type WHERE price_type.id = object_info.price_type_id)) AS real_price FROM object_info ORDER BY real_price

So how do you set it up with a query string ??

Tried like this

 $query = ObjectInfo::find(); $sort = new Sort([ 'attributes' => [ 'created_at', 'price' => [ $query->select(['*', 'real_price' => 'SELECT * , (object_info.price / ( SELECT price_type.course FROM price_type WHERE price_type.id = object_info.price_type_id)) AS real_price FROM object_info ORDER BY real_price']), 'asc' => ['real_price' => SORT_ASC], ], ], ]);

Does not work, writes that real_price will not find in the table. All right, this definition is simply the name given

 (object_info.price / ( SELECT price_type.course FROM price_type WHERE price_type.id = object_info)) AS real_price

How then?

  • Where exactly to register ?? - Vlad Shkuta

1 answer 1

Specify select in the query constructor; the yii\data\Sort class does not support any query constructs.

 $sort = new Sort([ 'attributes' => [ 'created_at', 'price' => [ 'asc' => ['real_price' => SORT_ASC], ], ], ]); $query = ObjectInfo::find() ->select(['*', 'real_price' => 'object_info.price / ( SELECT price_type.course FROM price_type WHERE price_type.id = object_info.price_type_id)') ->orderBy($sort->orders); $models = $query->all();
  • Thanks, I did the same before, but the error is syntax. The bottom line is that in -> select (['*', 'real_price' => .. So you can do it? Because real_price is not the name of the field, but just the name of the request part, after AS - Vlad Shkuta
  • And if you remove in 'real_price' => 'SELECT *, (object_info.price ... First SELECT, then there are no errors, but it sorts simply by the price column, not by real_price ( - Vlad Shkuta
  • Yes, you can specify aliases as keys. Removed excess in the designer. Have you checked your sql code at all? - Bookin
  • Yes, in mysql everything worked correctly. Thank you, you have correctly decided, I just didn’t specify $ models in foreach during the output) And then I redid it to work through dataProvider - Vlad Shkuta