Yii2: how to use subquery with ActiveRecord?

Question

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.
        ',
    ]);

Does this answer your question? [Yii2 subquery in Active Record](https://stackoverflow.com/questions/30164491/yii2-subquery-in-active-record) — Sfili_81, Oct 09 '20 at 06:32

score 2 · Answer 1 · answered Oct 09 '20 at 10:34

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

score 1 · Answer 2 · answered Oct 09 '20 at 11:02

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.

score 0 · Answer 3 · answered Oct 30 '20 at 06:36

0

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

answered Oct 30 '20 at 06:36

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

1
1

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Mark Rotteveel Oct 30 '20 at 07:57

Yii2: how to use subquery with ActiveRecord?

3 Answers3