不能在Yii2中排序HasMany关系

I know there are many questions regarding the Sorting Properties in Yii2. I'm a beginner in using this framework and I've exhausted all the reference that I can be found on the internet.

So for the first Part.

I try using the orderBy method on the Controller but can't seem to work it either.

My controller part uses this code:

$saleModel = Sales::find()->with([
    'customer',
    'salesItems',
    'salesItems.item'
])->where(['id' => $id])->one();

Now I have a Model called Sales on this model I have a hasMany relations with SalesItems model.

public function getSalesItems()
{
    return $this->hasMany(SalesItems::className(), ['sale_id' => 'id'])
}

I have tried sorting this SalesItems model using the 'item_id' that can be found in this Model and it is working.

public function getSalesItems()
{
    return $this->hasMany(SalesItems::className(), ['sale_id' => 'id'])
        ->orderBy(['item_id' => SORT_DESC]);
}

But what I want is to sort my SalesItems thru an another connected table/model that is connected via the item_id column in the sales items model.

On this case, I have another table called items which have an ID column this part is where they join ['id' =>'item_id'].

Overall, I want to sort my SalesItems based on the items.name.

My SQL Statement should be like this:

SELECT sales.id , sales.customer_id, sales_items.item_id, 
sales_items.sale_id, items.name FROM sales
INNER JOiN sales_items ON sales_items.sale_id = sales.id
INNER JOIN items ON sales_items.item_id = items.id WHERE sales.id =6374
ORDER BY items.name ASC;

I run this SQL Statemend on DB and successfully retrieve the results the way I wanted it to.

Here is also the table Schema:

Hope someone can understand what I've been trying to do.

Thank you

You need to use the following query with joinWith to achieve what you want, and you need one more column in the list to be selected i.id otherwise it would only show one item.

$saleModel = Sales::find()->alias('s')
    ->select('s.id, s.customer_id, si.item_id, si.sale_id, i.name, i.id')
    ->joinWith(
        [
            'customer c',
            'salesItems si',
            'salesItems.item i'
        ], false, 'INNER JOIN'
    )
    ->where(['s.id' => 1])->orderBy('i.name ASC')->asArray()->all();

You can try viaTable function of ActiveQuery:

public function getSalesItems()
{
    return $this->hasMany(SalesItems::className(), ['sale_id' => 'id'])
         ->viaTable('items', ['id' => 'item_id'])
         ->orderBy(['items.name' => SORT_ASC]);
}

Try these codes(they've worked on my localhost):

In a controller:

$sales = Sales::find()->with([
            'customer',
            'salesItems',
            'salesItems.item'
        ])->where(['id' => 2])->asArray()->one();

or

$db = Yii::$app->db;

        $query = $db->createCommand("SELECT sales.id , sales.customer_id, sales_items.item_id, 
                    sales_items.sale_id, items.name FROM sales
                    INNER JOiN sales_items ON sales_items.sale_id = sales.id
                    INNER JOIN items ON sales_items.item_id = items.id WHERE sales.id =2
                    ORDER BY items.name ASC;")->queryAll();

In the SalesItems model:

<?php
//...
class SalesItems extends \yii\db\ActiveRecord
{
//...
public function getItem()
    {
        return $this->hasOne(Items::className(), ['id' => 'item_id']);
    }
//...
}

In the Sales model:

<?php
//...
class Sales extends \yii\db\ActiveRecord
//...
public function getSalesItems()
    {
        return $this->hasMany(SalesItems::className(), ['sale_id' => 'id'])->orderBy(['item_id' => SORT_DESC]);
    }

    public function getCustomer()
    {
        return $this->hasOne(Customers::className(), ['id' => 'customer_id']);
    }
// ...
}

Using Active record:

     $query = Sales::find()->alias('sl')
        ->select('sl.id, sl.customer_id, si.item_id, si.sale_id, itm.name')
        ->joinWith('salesItem AS si', true)
        ->joinwith('salesItem.item AS itm')
        ->where(['si.id' => $id])
        ->orderBy(['itm.name' => SORT_ASC]);   

Then you can use any of the provided methods to retrieve the query results. or if you would like to test your query use:

$sql = $query->createCommand()->sql; 

To see if it matches your raw query.

I rarely use the relationships feature in most frameworks save for laravel which is restraining in this feature.

Understanding your question and the ERDs so presented, I will suggest a simplified answer for this specific issue

I note that si.sale_id joins to s.id and hence are numerically equal for the relationship to hold

Here, therefore, is my preferred solution

/**
 * 
 * @param integer $sales_id sales id
 * @return \frontend\models\SalesItems[] models
 */
public function getSalesItems($sales_id) {

    $s = \frontend\models\Sales::tableName();

    $si = \frontend\models\SalesItems::tableName();

    $i = \frontend\models\Items::tableName();

    return $this
                ->select('s.id, s.customer_id, si.item_id, si.sale_id, i.name')->from("$si si")
                ->leftJoin("$s s", "'$sales_id' = s.id")
                ->leftJoin("$i i", "si.item_id = i.id")
                ->where("si.sale_id = '$sales_id'")
                ->orderBy('i.name asc')
                ->all();
}

Run this in the SalesItems model class

Please introduce the following two attributes in the SalesItems model class only (not database table table) as follows

class SalesItems extends \yii\db\ActiveRecord {

    // copy line below into SalesItems class
    public $name, $customer_id;

}

I believe it is highly optimized this way

Hope it helps

Thanks