how to orderBy on viaTable in hasMany model relationship

I have three tables,




A ‘work’ can have many ‘clients’ and they are joined by the work_client table. When I generate the model yii defines these relationships as you would expect:

    public function getClients()


		return $this->hasMany(Client::className(), ['id' => 'client_id'])->viaTable('work_client', ['work_id' => 'id']);


This works, except I would like to order the related clients by a property ‘display_order’ on the work_client table. I can’t figure out how to do this since from what I can tell Yii does not convert the above code into a join but instead does a SELECT FROM client WHERE IN () so there is no way to sort on that field since it is never joined.

Then I tried replacing the hasMany call with this:

return Client::findBySql("SELECT * FROM " . WorkClient::tableName() . " LEFT JOIN " . Client::tableName() . " c ON = client_id WHERE work_id = $this->id ORDER BY display_order");

However that throws an error when I try to make relation via my WorkSearch model (it looks like Yii tries to perform a join without the ‘ON’ part of the SQL when combined with my other filters) Also I can’t imagine referring to ‘$this->id’ in there is good practice.

I found this Stack Overflow question where the poster is asking the same thing but the accepted solution does not work for me as I cannot merely move the display_order property to the client table. Is there a way to force the hasMany relationship to use a join instead of a subselect? And if not is there another solution I can use?

This may help you.

I’m not asking how to write a SQL query through Yii. As I pointed out in my initial response, I had a somewhat working solution by using model::findBySql, but that throws an error because from what I can tell model relations should return an ActiveQuery instance, not an array of results.

I guess you didn’t read the link. It has the exact example you are looking for.

You asked if there was a way to force yii to join the related table. Again, click the link. I could just as well copy and paste from the linked article but I’m on an ipad and well…