How to get sorted array if there is distinct?

Question

Yii

Good afternoon, experts! =)
Initially, there were two problems, I solved one.
Read more:
There is a "categories" table and a "products" table
Using a "one-to-many" relationship, we do a eager loading of "categories"

Модель 'Categories':
  public function getProducts () {
    return $this->hasMany(Products::className(), ['category_id' => 'id']);
  }

and output everything in the controller:

Контроллер:
  public function actionView () {
    $query = Categories::find()->select('categories.id');
    $query->innerJoinWith(
      [
        'Products' => function ($query) {
            $query->select(['products.id', 'products.price']);
            $query->orderBy(['products.price' => SORT_ASC]);
        },
      ]
    );
                debug($query->asArray()->all());
  }

We get:

Array
(
  [0] => Array
    (
      [id] => 1
      [products] => Array
        (
          [0] => Array
            (
              [id] => 3
              [price] => 100
            )
          [1] => Array
            (
              [id] => 4
              [price] => 50
            )
        )
    )
  [1] => Array
    (
      [id] => 2
      [products] => Array
        (
          [0] => Array
            (
              [id] => 5
              [price] => 300
            )
        )
    )
)

So, the first problem, if I call $query->count(), then it will show me not "2", but "3", i.e. the sum of all elements of the "products" subarrays. I solved this problem by adding distinct: $query->distinct()->count()
And then another problem appeared. where there is more than one element in the "products" subarray, I need to sort them by price $query->orderBy(['products.price' => SORT_ASC]), but this entry starts to conflict with distinct

SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'mybd.products.price' which is not in SELECT list; this is incompatible with DISTINCT
The SQL being executed was: SELECT COUNT(*) FROM (SELECT DISTINCT `categories`.`id` FROM `categories` LEFT JOIN `products` ON `categories`.`id` = `products`.`product_id` ORDER BY `products`.`price`) `c`

How can I make the sorting work and show the correct number of elements in the array??
I must say right away that count($query) will not work, in the future pagination is created for this variable.

Answer 1

What do you not like about:
and connection:

public function getProductsByPrice () {
    return $this->hasMany(Products::className(), ['category_id' => 'id'])->orderBy('price');
  }

Or if you need goods, then why not choose goods?
or if you need products in a certain category, then
What do you want to get as a result?

Answer 2

I would use a query like this:

$rows = Products::find()
->select(['category_id' => 'CAT.id', 'product_id' => 'PROD.id', 'product_price' => 'PROD.price'])
->from(['PROD' => Products::tableName()])
->innerJoin(['CAT' => Categories::tableName()], 'CAT.id = PROD.category_id')
->orderBy(['PROD.price' => SORT_ASC])
->asArray()->all();
$rows = ArrayHelper::index($rows, 'product_id', 'category_id');

The output will be something like this array:

[
  1 => [
    177 => ['category_id' => 1, 'product_id' => 177, 'price' => 1000],
    178 => ['category_id' => 1, 'product_id' => 177, 'price' => 2000],
  ],
  2 => [
    179 => ['category_id' => 2, 'product_id' => 179, 'price' => 3000],
    192 => ['category_id' => 2, 'product_id' => 192, 'price' => 4000],
  ],
]

PS: and on ORDER BY you swear because you are not joining, but loading through with. Try doing ORDER BY inside innerJoinWith.