Subquery in yii2

Question

I have 2 tables

1. projects (id,name)
   

2. accounts (id,project_id,account_name)
   

I want to find all projects with their account_name like this

SELECT Project.*,(SELECT account_name FROM accounts WHERE projectid = Project.id) FROM projects as Project

Please let me know how this possible in yii2.
I can easily done it in cakephp where we can use virtualFields.is there any functionality in yii2 like cakephp ?

Answer 1

Actually, you should be able to just write this into the select() call as an additional column (column names with parentheses are treated as DB expressions automatically)

Project::find()
    ->select([
        'Project.*', 
        '(SELECT account_name FROM accounts WHERE projectid = Project.id) AS account_name'
    ])
    ->asArray()->all();

If you like to have the column present in the ActiveRecord class instead of an array, declare an additional public property which receives the field:

class Project extends ActiveRecord {
    // ...
    public $account_name;
    // ...
}

See http://www.yiiframework.com/doc-2.0/yii-db-query.html#select()-detail