how to orderBy on viaTable in hasMany model relationship

Yii 2.0 General Discussions
1

I have three tables,

work

client

work_client

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 client.id 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 c.id = 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?

2

This may help you.

http://www.bsourcecode.com/yiiframework2/select-query-joins/

3

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.

4

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…