如何在Yii2中创建复杂的左外连接？

Question

How do I perform this query in Yii2?

SELECT `keyword`.`id`, `keyword`, `volume`, `cpc`, `competition`, `keyword_id` 
FROM `keyword` 
LEFT JOIN `ad_group_keyword` 
  ON keyword.id = ad_group_keyword.keyword_id 
  and ad_group_id = 1 
WHERE ((`keyword_id` IS NULL) 
  AND (NOT (`volume` IS NULL))) 
  AND (NOT (`cpc` IS NULL)) 
ORDER BY volume desc 
LIMIT 1;

I tried the following and many combinations but I can't get the ON part right.

$kw = Keyword::find()->select(['keyword.id', 'keyword', 'volume', 'cpc', 'competition', 'keyword_id'])->
  leftJoin('ad_group_keyword', 'keyword.id = ad_group_keyword.keyword_id', ['ad_group_id'=>1])->
  andWhere(['keyword_id'=>null])->
  andWhere(['not', ['volume' => null]])->andWhere(['not', ['cpc' => null]])->
  orderBy('volume desc')->asArray()->limit(1)->all();

The above generates this SQL, which is missing the second condition of the ON:

SELECT keyword.id, keyword, volume, cpc, competition, keyword_id FROM keyword LEFT JOIN ad_group_keyword ON keyword.id = ad_group_keyword.keyword_id WHERE ((keyword_id IS NULL) AND (NOT (volume IS NULL))) AND (NOT (cpc IS NULL)) ORDER BY volume desc LIMIT 1

I also tried

leftJoin('ad_group_keyword', ['keyword.id'=>'keyword_id', 'ad_group_id'=>1])->

But it generates

FROM keyword LEFT JOIN ad_group_keyword ON (keyword.id='keyword_id') AND (ad_group_id=1)

With 'keyword_id' in quotation marks, so it treats it as a string!

Docs: http://www.yiiframework.com/doc-2.0/yii-db-query.html#leftJoin()-detail

(This is why I hate dealing with ORM APIs. I spent all that time becoming proficient at SQL. Now you make me learn a whole new way of doing the same thing, and sometimes it's impossible.)

Answer 1

In relation to your remark of having to deal with ORM API, you can use the createCommand approach. You can just use your raw sql query with this method. The difference is that you don't get ActiveRecord[] as a result but just array[] (which is usually fine for complex queries).

$sql = "SELECT `keyword`.`id`, `keyword`, `volume`, `cpc`, `competition`, `keyword_id` 
FROM `keyword` 
LEFT JOIN `ad_group_keyword` 
  ON keyword.id = ad_group_keyword.keyword_id 
  and ad_group_id = 1 
WHERE ((`keyword_id` IS NULL) 
  AND (NOT (`volume` IS NULL))) 
  AND (NOT (`cpc` IS NULL)) 
ORDER BY volume desc 
LIMIT 1;";

$params = [];

$kw = Yii::$app->db->createCommand($sql, $params)->queryAll();