Sorting Related Tables order by does not apply

Yii 2.0 General Discussions
1

$model1 = \app\models\Product::find()

->joinWith(‘categories’)

->where(’ product.productId = :productId’, [’:productId’=>6037])

->with(‘categories’)

->orderBy(’ category.category_path ')

->all();

When I iterate over category, it is not sorted at all. I can adjust this in the model at getCategories, but I would like adjust this when neccessary and not modify my model to do so. How can I instruct ActiveRecord when it loads categories to add the orderby?

My query ends up like this:

SELECT Product.* FROM Product LEFT JOIN prod_category ON Product.productId = prod_category.productId LEFT JOIN category ON prod_category.categoryId = category.category_id WHERE product.productId = ‘6037’ ORDER BY category.category_path, category_path

When I start iterating over category:

SELECT * FROM category WHERE category_id IN (‘14’, ‘38’, ‘42’, ‘51’)

2

  1. Remove the joinWith() call. It only adds overhead to the main query because you don’t want to filter or order products by category data.

  2. Move the orderBy() call from the main query to the category query.




Product::find()

  ->where(['productId' => 6037]) // will be parameterized automatically

  ->with(['categories' => function ($query) { $query->orderBy('category_path'); }])

  ->all();

It will result in something like:

SELECT Product.* FROM Product WHERE productId = ‘6037’ ;

SELECT * FROM category WHERE category_id IN (‘14’, ‘38’, ‘42’, ‘51’) ORDER BY category_path;

3

HOT DOG It works!! You deserve the master title next to your name.