How to create a relation query with hasOne and onCondition?

Question

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

Answer 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.