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 my_join_table.id = my_other_table.id')
$query = Questions::find();
$query->joinWith(<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />?<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />???)
Can anyone help out please.
$query = Questions::find()->select('my_other_table.*, my_join_table.*')->innerJoin('my_join_table', 'my_join_table.id = my_other_table.id')->where('my_join_table.id = 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
This file has been truncated.
Built on top of [Database Access Objects](db-dao.md), query builder allows you to construct a SQL query
in a programmatic and DBMS-agnostic way. Compared to writing raw SQL statements, using query builder will help you write
more readable SQL-related code and generate more secure SQL statements.
Using query builder usually involves two steps:
1. Build a [[yii\db\Query]] object to represent different parts (e.g. `SELECT`, `FROM`) of a SELECT SQL statement.
2. Execute a query method (e.g. `all()`) of [[yii\db\Query]] to retrieve data from the database.
The following code shows a typical way of using query builder:
$rows = (new \yii\db\Query())
->where(['last_name' => 'Smith'])
$query->leftJoin(['u' => $subQuery], 'u.id=author_id');
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.