Hello. I need to create many-to-many relation. Junction table contains not only ID’s of related records, but also some additional data. Among other data it contains a column to be sorted by.
Say, I have tA and tB tables. Junction table contains: tA_id, tB_id, rank. Without framework I use the following SQL query:
SELECT tA.*, junction.rank FROM tA, tB, junction WHERE tA.id=junction.tA_id && tB.id=junction.tB_id && tB.someColumn="blabla" ORDER BY junction.rank
Here I select needed records from table tA together with additional column from junction table and with sorting by this additional column.
Is there any way to achieve the same with ActiveRecord?
This code may help u, or not, anyway i tried. Ask me more if didn`t works, i rly wanna help
First of all i assume that there’s relation between tables. In my example that i tested there’s inner join.
So code is:
Junction::find()->innerJoin('ta')->innerJoin('tb')->where(
[
'and',
['=', 'junction.ta_id', 'ta.id'],
['=', 'junction.tb_id', 'tb.id'],
]
)->select('ta.*, junction.rank')->orderBy('junction.rank')->all();
// this example gonna process next mysql:
//SELECT `ta`.*, `junction`.`rank` FROM `junction` INNER JOIN `ta` INNER JOIN `tb` WHERE
//(`junction`.`ta_id` = 'ta.id') AND (`junction`.`tb_id` = 'tb.id') ORDER BY `junction`.`rank`
To make innerJoin func work u should have getTb and getTa functions in your junction class.
