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?