How to create a relation query with hasOne and onCondition?

Question

relation query

sql

Because I've got an index (user_id,lesson_id) I want to query with that index.

In the case of the image it does not use the index, right ?

NOTE: USER_ID it is a INTEGER VALUE and not a FIELD.

//the following SQL it is my expecting:
//not generate by php code. just show a example for what to do.
LEFT JOIN `user_lesson_order` ON (
    //user_id is current logined 
    `user_lesson_order` . `user_id` = 29
) 
AND ( 
    `lesson_favorite` . `lesson_id` = `user_lesson_order` . `lesson_id`
)
AND ...
AND ...

//the wrong php code. it will generate a wrong sql;
public function getLessonOrder() {
    return $this->hasOne(UserLessonOrder::class, [
        UserLessonOrder::tableName() . '.user_id' => $this->user_id,
        'lesson_id' => 'lesson_id'
    ])->onCondition([
        UserLessonOrder::tableName() . '.payment_status' =>SystemCode::COMMON_PAYMENT_STATUS_YES,
        UserLessonOrder::tableName() . '.status' => SystemCode::COMMON_STATUS_ENABLE
    ]);
}
//the wrong sql:
LEFT JOIN `user_lesson_order` ON (
   //user_lesson_order.user_id = 41, this is my expect sql. 
   `lesson_favorite`.`41` = `user_lesson_order`.`user_id`

    AND `lesson_favorite`.`lesson_id` = `user_lesson_order`.`lesson_id`
) AND (
(
    `user_lesson_order`.`payment_status` = 'COMMON_PAYMENT_STATUS_YES'
) AND (
    `user_lesson_order`.`status` = 'COMMON_STATUS_ENABLE'
)
)
Unknown column 'lesson_favorite.41' in 'on clause'

fllow php code will generate a correct sql. but it cant use unique_key(user_id,lesson_id);

//follow code is working. but genarate a sql cant not use unique_key(user_id,lesson_id);
public function getLessonOrder() {
return $this->hasOne(UserLessonOrder::class, [
    'lesson_id' => 'lesson_id'
])->onCondition([
    UserLessonOrder::tableName() . '.user_id' => $this->user_id,
    UserLessonOrder::tableName() . '.payment_status' =>SystemCode::COMMON_PAYMENT_STATUS_YES,
    UserLessonOrder::tableName() . '.status' => SystemCode::COMMON_STATUS_ENABLE
    ]);
}


//sql generate by php code,it is working. but cant use unique_index(user_id,lesson_id);
LEFT JOIN `user_lesson_order` ON (
    `lesson_favorite` . `lesson_id` = `user_lesson_order` . `lesson_id`
) AND ( 
    //user_id is current logined 
   `user_lesson_order` . `user_id` = 29
)
AND ...
AND ...

Please tell me:

1, Which case will be using index of database unique_key (user_id,lesson_id);

2, How can I create a query like this? user_id on the first, and then lesson_id.

//user_id is current logined
left join user_lesson_order on user_lesson_order.user_id = 29 
and lesson_favorite.lesson_id = user_lesson_order.lesson_id

Don't be lazy. Don't just link images. Nobody likes that on SO. Please ask proper questions. — robsch, Nov 29 '17 at 15:34
thanks for your suggestion. im just conside send text will be bad format. — hhniao, Nov 30 '17 at 04:57
You can format the code. Give it a try. Consider that answering users have to write the code by themself, instead of just copying your provided code into the answer and edit it. You won't get much help and reputation if you don't ask proper questions. Make it as easy as possible to write an answer. Also it is not of much help to others if your question is hard to read. — robsch, Nov 30 '17 at 08:56

robsch · Accepted Answer · 2017-11-30T10:04:16.563

1

public function getLessenOrder() {
    return $this->hasOne(UserLessonOrder::class, [
     // 'user_id'   => 'user_id', // is it part of the relation?
        'lesson_id' => 'lesson_id', 
    ])->onCondition([
        UserLessonOrder::tableName() . '.payment_status' => SystemCode::COMMON_PAYMENT_STATUS_YES,
        UserLessonOrder::tableName() . '.status'         => SystemCode::COMMON_PAYMENT_ENABLE, 
        UserLessonOrder::tableName() . '.user_id'        => $this->user_id,
    ])
}

This should do it. The created JOIN should use the index. Doesn't it? You can have a look into the runtime/logs/app.log to see which query was generated.

If that works the only difference is using the correct field name in the $link parameter of hasOne(): session_id instead of session. With this parameter you just say which fields (columns) should be related - it's not possible to use a value here. This can be done in onCondition() what you have already done.

edited Nov 30 '17 at 10:04

answered Nov 30 '17 at 08:52

robsch

9,358
9
63
104

thanks for your answer. this query should use the index it is right. but, please note that ,"the user_id it is a value, not a field", user_id from User Component. so, might your code have a bit mistake. please update the answer. thanks! – hhniao Nov 30 '17 at 09:04
@hhniao Updated answer. Did you mean this? Otherwise I'm not sure what you mean with constant. – robsch Nov 30 '17 at 09:09
i have update the question. i have add more detail information. please answer it again. thanks! – hhniao Nov 30 '17 at 09:40
@hhniao Updated again. So your FK relation is just about the lesson id, not containing the user id? If it's still not working: which SQL query gets generated with this code? Put into a comment... – robsch Nov 30 '17 at 09:54
@hhniao Updated once more. – robsch Nov 30 '17 at 09:58
yes, FK just lesson_id (in fact, i have not any FK, just relation with lesson_id ). you last one update it is working. but it cant not using unique index (user_id,lesson_id); – hhniao Nov 30 '17 at 10:37
so, how can i build a sql like this? left join left join user_lesson_order on user_lesson_order.user_id = 29 and lesson_favorite.lesson_id = user_lesson_order.lesson_id. – hhniao Nov 30 '17 at 10:38
i wanna user_id condistion on the first to use unique index – hhniao Nov 30 '17 at 10:39
@hhniao You have user_id in both classes UserLessonOrder and LessonFavourite, right? If you uncomment `'user_id' => 'user_id'` wouldn't that cause the usage of the index? The first condition that gets build should contain both columns, so the index should be used, I think. Following other conditions should not affect that. But I'm not sure with this. – robsch Nov 30 '17 at 10:57
yes, both classes contain user_id. it cant be link with `'user_id' => 'user_id'`. – hhniao Nov 30 '17 at 11:30
but it just a coincidence. conside this condition, UserLessonOrder change to UserGoodsOrder and LessonFavorite change to Goods. as you know, Goods will not contain user_id – hhniao Nov 30 '17 at 11:35
@hhniao Okay, I'm afraid I can't help you with this. Might be not a real relation? Consider implementing the function with `return LessonFavourite::find()->where(...)->one();`. – robsch Nov 30 '17 at 12:21

csminb · Answer 2 · 2017-11-29T21:13:36.000

0

you can use multiple fields to define relation links

$this->hasOne(
        UserLessonOrder::class, 
        ['lesson_id' => 'lesson', 'user_id' => 'user_id']
    )
    ->onCondition([
        'payment_status' => ...
        'status'         => ...  
    ])

usage of any model attribute ($this->user_id) in relation definition will cause issues in most situations

edited Nov 29 '17 at 21:13

answered Nov 29 '17 at 20:42

csminb

2,382
1
16
27

thanks for your answer. but please note that, user_id it is a constants(integer), not a field. so, it cant use ['lesson_id'=>'lesson','user_id'=>'user_id']. – hhniao Nov 30 '17 at 04:59
@hhniao sorry i completely missed the point of your question the first time around. in short you can't make a yii relation generate the sql in the format you want (relation links are always the first condition in the join statement. unless you start to override QueryBuilder) your issue however seems entirely mysql related, the optimiser should use the best (same?) index in both situation. consider perhaps posting a question with relevant schema and explain statements as to why this is happening – csminb Nov 30 '17 at 19:16
any way, thanks you. but your world is suprise to me. yii cant generate a sql !!! i cant belive that. – hhniao Dec 01 '17 at 05:37

How to create a relation query with hasOne and onCondition?

2 Answers2