'Subquery in yii2

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 ?

yii2subquery


Solution 1:[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

Solution 2:[2]

I think this is more readable. Also there is no raw query.

$rows = (new \yii\db\Query())
    ->select([
        'p.*',
        'a.account_name'
    ])
    ->from('projects p')
    ->innerJoin('accounts a', 'a.id = p.account_id')
    ->all();

Solution 3:[3]

I have tried this

$rows = (new \yii\db\Query())
                    ->select(['*','(SELECT account_name FROM accounts WHERE id = projects.account_id) as account_name'])
                    ->from('projects')
                    ->where(['id' => $id])
                    ->all();

This is working for me.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 João Marques
Solution 3 Akshay Sharma

Related Questions