Why Yii2 generates additional queries?

Trying to avoid an additional query that occurs when the sample of the two tables.
A model 1
class Melody extends ActiveRecord
{

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

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


Модель2
class Artist extends ActiveRecord
{
 public static function tableName()
{
 return 'cms.artist';
}
//.....
}


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


Get 2 request

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 query and is it even possible? Don't really want to use pure sql due to the use of the AR capabilities
July 9th 19 at 11:01
4 answers
July 9th 19 at 11:03
Solution
Because you are abstracted from demand management.
Reveal more the essence of the answer, I will be extremely happy the information received - Marc_Beier18 commented on July 9th 19 at 11:06
: because made in favor of standardization and speed of development. For example, if you change the dB, provided that all uses ActiveRecord and ActiveQuery is very likely not to break anything because the other driver for the database to build the query itself, without your participation. - Haley0 commented on July 9th 19 at 11:09
July 9th 19 at 11:05
Try instead of joinWith() to use leftJoin(Artist::className(), "cms`.`id` = `m`.`artist_id')
To use joins with ties to the official. documentation - Marc_Beier18 commented on July 9th 19 at 11:08
: now did so, but the data are selected in the same model. Unfortunately, today there is no time to fully understand this method. - Haley0 commented on July 9th 19 at 11:11
July 9th 19 at 11:07
public function getArtist()
{
 return $this->hasOne(Artist::className(), ['artist_id' => 'artist_id']);
 }

clean and should disappear.
But what's stopping you second query? Yii you have chosen all the artists related to the query in two query instead of N queries

And yet you had to be requests to retrieve information about the tables that you do not interfere?)
Want to control queries - don't use AR in any form. Only native queries php, only hardcore!)
Requests for information about the tables? schemaCacheDuration more. I was expecting one query with the join. If AR gets the data from sql in one query, but does not put them in the model because they do not know how, so AR is not needed. - Marc_Beier18 commented on July 9th 19 at 11:10
Yii 1.x applied automatically aliases to the tables, and therefore was able to get all the data in one request. Yii 2 does not. Perhaps there is a way to get him to act by analogy with the 1 version, but I don't know how. - Haley0 commented on July 9th 19 at 11:13
And if you remove getArtist how the framework knows what with('artist')? - adell17 commented on July 9th 19 at 11:16
Well, not necessarily down requests to write, there is QueryBuilder. Unless you use something really quite database-specific... - Marc_Beier18 commented on July 9th 19 at 11:19
: Yes, I was referring to something else other than AR - Marc_Beier18 commented on July 9th 19 at 11:22
July 9th 19 at 11:09
Read carefully the documentation

If in Russian, now releasemy do not work as in Yii1. Now instead of one query with lots of JOINS s run multiple queries using IN (...). Not once has it been mentioned. The link all is written
Clear. Strange decision. Interestingly, the man who invented it ran into the length limit of the query? =) I have a bunch in SEPRS is performed for 8 seconds with a pair of joynow 0.2 - Marc_Beier18 commented on July 9th 19 at 11:12
: Create a ticket in the official repository, argue their position. You can describe even in Russian, maybe that will work - Haley0 commented on July 9th 19 at 11:15
: Now we are working on this issue. I'm not the first :) I Will wait - adell17 commented on July 9th 19 at 11:18

Find more questions by tags YiiActive RecordPostgreSQL