php - How do I create a complex left outer join in Yii2? -

how perform query in yii2?

select `keyword`.`id`, `keyword`, `volume`, `cpc`, `competition`, `keyword_id`  `keyword`  left join `ad_group_keyword`    on keyword.id = ad_group_keyword.keyword_id    , ad_group_id = 1  ((`keyword_id` null)    , (not (`volume` null)))    , (not (`cpc` null))  order volume desc  limit 1; 

i tried following , many combinations can't 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 sql, missing second condition of on:

select keyword.id, keyword, volume, cpc, competition, keyword_id keyword left join ad_group_keyword on keyword.id = ad_group_keyword.keyword_id ((keyword_id null) , (not (volume null))) , (not (cpc null)) order volume desc limit 1

i tried

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

but generates

from keyword left join ad_group_keyword on (keyword.id='keyword_id') , (ad_group_id=1)

with 'keyword_id' in quotation marks, treats string!

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

(this why hate dealing orm apis. spent time becoming proficient @ sql. make me learn whole new way of doing same thing, , it's impossible.)

in relation remark of having deal orm api, can use createcommand approach. can use raw sql query method. difference don't activerecord[] result array[] (which fine complex queries).

$sql = "select `keyword`.`id`, `keyword`, `volume`, `cpc`, `competition`, `keyword_id`  `keyword`  left join `ad_group_keyword`    on keyword.id = ad_group_keyword.keyword_id    , ad_group_id = 1  ((`keyword_id` null)    , (not (`volume` null)))    , (not (`cpc` null))  order volume desc  limit 1;";  $params = [];  $kw = yii::$app->db->createcommand($sql, $params)->queryall();