How to make SQL select from two related tables in Yii2?

Question

MySQL

Actually there are 2 tables:

• car_brand - table with car brands (fields: id, title)
  
• car - table with cars (fields: id, brand_id, title)
  

You need to display the name of the brands and their id, which contain at least one car.
Now the request looks like this:

$car_brand = Car::find()
        ->select('title, id, brand_id')
        ->with('car_brand')
        ->groupBy('brand_id')
        ->all();

But one of the requests is visible in the debugger:

SELECT * FROM `car_brand` WHERE `id` IN ( все айдишников моделей )

Instead of what you want:

SELECT a.title as title, brand_id 
FROM car_brand as a, car as b 
WHERE b.brand_id = a.id 
GROUP BY b.brand_id

What is the best way to display such a query/result?

Answer 1

With this request:

$car_brand = Car::find()
        ->select('title, id, brand_id')
        ->with('car_brand')
        ->groupBy('brand_id')
        ->all();

$car_brand is a Car
$car_brand->car_brand is the data from the car_brand link
Here are the docs: www.yiiframework.com/doc-2.0/guide-db-active-recor...