CODEWITHSUNDEEP
sqlpostgresqlactiverecordyii2subquery
Roby Sottini
Roby Sottini

Reputation: 2265

Yii2: how to use subquery with ActiveRecord?

I want to create a find function to use then in a GridView widget My I don't know how to use a subquery.

This is the simple version of the PostgreSQL query that I have:

SELECT color
FROM cars
LEFT JOIN (
    SELECT name
    FROM companies
)

Here is what I a trying and doesn't work:

$query = Cars::find()
    ->select([
        'color' => '
            SELECT name     // Problem here.
            FROM companies  // Problem here.
        ',
    ]);

Upvotes: 0

Views: 545

Answers (3)

Олег Сергеевич
Олег Сергеевич

Reputation: 1

$query = Cars::find()
    ->select(['cars.color','companies.name'])
    ->leftJoin('companies', 'cars.company_id = companies.id');

Upvotes: 0

Michal Hynčica
Michal Hynčica

Reputation: 6144

You can use instance of ActiveQuery in leftJoin() method like this:

$subQuery = Companies::find()
    ->select('name', 'id'); //I've added ID to show how to write on condition
$query = Cars::find()
    ->select('color')
    ->leftJoin(
        ['alias' => $subQuery],
        'alias.id = cars.company_id'
    );

See documentation for more details about leftJoin() and join() methods.

Upvotes: 1

Jiri Semmler
Jiri Semmler

Reputation: 421

Your expected query is not what you are trying to do. You can add a custom subquery as

$query = Cars::find()
    ->select([
       '(SELECT name FROM companies) AS xxx'
    ]);

But if you want to add a field from joined table to the grid, then you should have a relation defined and then you can easily add such a column to your grid. See https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#relational-data

Upvotes: 2

Related Questions