Find() Join With Subselect In Join?


I have the following line that I am trying to craft into a query using active query find()

LEFT JOIN (select * from `my_join_table` where id = 15) as my_join_table on =')

$query = Questions::find();

$query->joinWith(<img src='' class='bbc_emoticon' alt='???' />?<img src='' class='bbc_emoticon' alt='???' />???)

Can anyone help out please.


    $query = Questions::find()->select('my_other_table.*, my_join_table.*')->innerJoin('my_join_table', ' =')->where(' = 15')->all();

I am using this in a ModelSearch as an ActiveDataProvider.

I require a sub select on the joining table because it needs to select specific rows before presenting as a table to join.

Your example does as it says but does not work for me because it takes all rows in the table into consideration.

I have the sub select working as desired in a standard sql query but dont know how to make it work as an activedataprovider situation.



using query i found a solution for sub select

$query->leftJoin(['u' => $subQuery], '');


I need to have this in AR for relation purposes.

Is there a way to include this query into the relation of the Model?

Sure, this is the point. AR make much easier the relation job.

You first have to define foreign key in your table, then, Gii will generate the model with the relations.

With PhpMyAdmin :

Once you created the foreign key in your table, and generated the model with Gii, then, you can do something like :

$item = my_other_table::model->with("my_join_table")->findByPk($id);

Wich is more elegant.