Why does Yii2 generate additional requests?

Question

PostgreSQL

I'm trying to avoid an additional query that occurs when selecting from two tables.
Model 1 available

class Melody extends ActiveRecord
{

    public static function tableName()
    {
        return 'cms.melody';
    }

    public function getArtist()
    {
        return $this->hasOne(Artist::className(), ['artist_id' => 'artist_id']);
    }
    //....
}

Model2

class Artist extends ActiveRecord
{
    public static function tableName()
    {
        return 'cms.artist';
    }
    //.....
}

I make a selection:

songs = Melody::find()
                ->alias('m')
                ->select([
                    'melody_id' => 'm.melody_id',
                    '"cms"."artist".*'
                ])
                ->joinWith('artist')
                ->all();

I receive 2 requests
1:

SELECT "m"."melody_id" AS "melody_id", "cms"."artist".* 
       FROM "cms"."melody" "m" inner join "cms"."artist" ON "m"."artist_id" = "cms"."artist"."artist_id"

2:

SELECT * FROM "cms"."artist" WHERE "artist_id" IN (1200, 2, 500, 4142, 364, 434, 1918, 1906, 13290, 9119, 1916, 5073, 72, 8351, 1316, 1694, 9926, 1822, 1894, 2040, 1074, 872, 8475, 10221, 10215, 552, 17792, 2000, 2080, 180, 1806, 1306, 6281, 16371, 1858, 8596, 892, 1534, 11766, 14104, 13172, 4917, 11058, 7040, 5789, 5400, 2108, 4864, 312, 276, 5918, 17497, 3903, 182, 564, 1670, 6602, 5074, 6922, 9613, 13430, 6773, 6232, 2036, 7014, 4289, 6529, 858, 1488, 1498, 4439, 17814, 82, 13250, 84, 1448, 164, 15551, 18288, 2102, 2100, 1444, 7509, 4947, 9018, 4338, 6691, 13438, 1418, 146, 814, 8715, 5349, 2182, 2044, 12109, 5913, 812, 1736, 98, 1018, 14500, 11312, 5055, 5058, 216, 102, 15808, 5061, 648)

Can't figure out how to avoid the second request and is it even possible? I don’t really want to use pure sql because of the use of AR capabilities

Answer 1

Because you are abstracted from query management.

Answer 2

Try using leftJoin(Artist::className(), '`cms`.`id` = `m`.`artist_id') instead of joinWith()

Answer 3

public function getArtist()
    {
        return $this->hasOne(Artist::className(), ['artist_id' => 'artist_id']);
    }

remove it and it should be gone.
But what's stopping you from the second request? Yii selected all the artists related to the request in two requests instead of N requests
. Also, you should have had requests for information about tables, do they interfere with you?) If you
want to control requests, do not use AR in any form. Only native php requests, only hardcore!)

Answer 4

Read the documentation
carefully If in Russian, now relays work differently than in Yii1. Now, instead of one query with a bunch of JOINs, several queries are executed using IN (...) . This has already been mentioned many times. It's all in the link