Untitled

mail@pastecode.io avatar
unknown
sql
2 years ago
33 kB
2
Indexable
Never

SELECT 
  COUNT(*) AS `count` 
FROM 
  `english`.`block_lists` AS `BlockList` 
WHERE 
  `user_id` = 61833 
  AND `teacher_id` = 584 
  AND `status` = '1' 
  AND `block_type` = '1'


______________________________________________________________________________________________________________________________________

// start
SELECT 
  `LessonSchedule`.`id`, 
  `LessonSchedule`.`user_id`, 
  `LessonSchedule`.`teacher_id`, 
  `LessonSchedule`.`class_id`, 
  `LessonSchedule`.`chapter_id`, 
  `LessonSchedule`.`lesson_time`, 
  `LessonSchedule`.`live_lesson_flg`, 
  `LessonSchedule`.`coin`, 
  `LessonSchedule`.`refund_flg`, 
  `LessonSchedule`.`chat_start_time`, 
  `LessonSchedule`.`chat_end_time`, 
  `LessonSchedule`.`status`, 
  `LessonSchedule`.`status_manual`, 
  `LessonSchedule`.`open_lesson_flg`, 
  `LessonSchedule`.`connect_id`, 
  `LessonSchedule`.`payment_plan_id`, 
  `LessonSchedule`.`old_connect_id`, 
  `LessonSchedule`.`textbook_category_type`, 
  `LessonSchedule`.`lesson_text_id`, 
  `LessonSchedule`.`textbook_category_id`, 
  `LessonSchedule`.`apology_show`, 
  `LessonSchedule`.`teacher_check_flg`, 
  `LessonSchedule`.`callan_halfprice_flg`, 
  `LessonSchedule`.`rank_coin_id`, 
  `LessonSchedule`.`rank_basic_amount_log_id`, 
  `LessonSchedule`.`coin_event`, 
  `LessonSchedule`.`substitute_teacher_flg`, 
  `LessonSchedule`.`substitute_teacher`, 
  `LessonSchedule`.`reserve_coin_data_log_id`, 
  `LessonSchedule`.`native_option_flg`, 
  `LessonSchedule`.`created`, 
  `LessonSchedule`.`modified`, 
  `LessonSchedule`.`created_ip`, 
  `LessonSchedule`.`modified_ip`, 
  `LessonSchedule`.`textbook_type`, 
  `LessonSchedule`.`partner_modification_status`, 
  `LessonSchedule`.`old_data_flg`, 
  `LessonSchedule`.`google_calendar_event_id` 
FROM 
  `english`.`lesson_schedules` AS `LessonSchedule` 
WHERE 
  `LessonSchedule`.`user_id` = 61833 
  AND `LessonSchedule`.`lesson_time` <= '2022-11-29 13:04:51' 
  AND `LessonSchedule`.`lesson_time` >= '2022-11-29 13:29:51' 
  AND `LessonSchedule`.`status` = 1 
ORDER BY 
  `LessonSchedule`.`lesson_time` desc 
LIMIT 
  1
// end

______________________________________________________________________________________________________________________________________


// start
SELECT 
  `TextbookConnect`.`id`, 
  `LessonSchedule`.`teacher_id`, 
  `LessonSchedule`.`lesson_time`, 
  `Teacher`.`id`, 
  `Teacher`.`name`, 
  `Teacher`.`jp_name`, 
  `Teacher`.`avatar_id`, 
  `Teacher`.`avatar_flg`, 
  `Teacher`.`image_url`, 
  `Teacher`.`counseling_flg`, 
  `TextbookCategory`.`name`, 
  `TextbookCategory`.`type_id`, 
  `TextbookSubcategory`.`name`, 
  `Textbook`.`name` 
FROM 
  `english`.`lesson_schedules` AS `LessonSchedule` 
  LEFT JOIN `english`.`teachers` AS `Teacher` ON (
    `LessonSchedule`.`teacher_id` = `Teacher`.`id`
  ) 
  LEFT JOIN `english`.`textbook_connects` AS `TextbookConnect` ON (
    `LessonSchedule`.`connect_id` = `TextbookConnect`.`id`
  ) 
  LEFT JOIN `english`.`textbook_categories` AS `TextbookCategory` ON (
    `TextbookConnect`.`category_id` = `TextbookCategory`.`id` 
    AND `TextbookCategory`.`status` = 1
  ) 
  LEFT JOIN `english`.`textbook_subcategories` AS `TextbookSubcategory` ON (
    `TextbookConnect`.`subcategory_id` = `TextbookSubcategory`.`id` 
    AND `TextbookSubcategory`.`status` = 1
  ) 
  LEFT JOIN `english`.`textbooks` AS `Textbook` ON (
    `TextbookConnect`.`textbook_id` = `Textbook`.`id`
  ) 
WHERE 
  `LessonSchedule`.`user_id` = 61833 
  AND `LessonSchedule`.`lesson_time` BETWEEN '2022-11-29 12:34:51' 
  AND '2022-11-29 23:35:00' 
  AND `LessonSchedule`.`status` = 1 
ORDER BY 
  `LessonSchedule`.`lesson_time` asc 
LIMIT 
  2
// end


______________________________________________________________________________________________________________________________________

// start
SELECT 
  COUNT(*) AS `count` 
FROM 
  `english`.`lesson_schedules` AS `LessonSchedule` 
  LEFT JOIN `english`.`users` AS `User` ON (
    `LessonSchedule`.`user_id` = `User`.`id`
  ) 
  LEFT JOIN `english`.`teachers` AS `Teacher` ON (
    `LessonSchedule`.`teacher_id` = `Teacher`.`id`
  ) 
WHERE 
  `LessonSchedule`.`user_id` = 61833 
  AND `LessonSchedule`.`lesson_time` BETWEEN '2022-11-29 12:34:51' 
  AND '2022-11-29 23:35:00' 
  AND `LessonSchedule`.`status` = 1
// end

______________________________________________________________________________________________________________________________________


SELECT 
  COUNT(*) AS `count` 
FROM 
  `english`.`payments` AS `Payment` 
WHERE 
  `Payment`.`user_id` = 61833 
  AND `Payment`.`form_type` IN (2, 4, 6)


______________________________________________________________________________________________________________________________________


SELECT 
  `UserCampaignModalSettings`.`campaign_setting_id` 
FROM 
  `english`.`user_campaign_modal_settings` AS `UserCampaignModalSettings` 
WHERE 
  `UserCampaignModalSettings`.`user_id` = 61833 
  AND `UserCampaignModalSettings`.`show` = 0


______________________________________________________________________________________________________________________________________

// start
SELECT 
  `FilteredAnnounceV`.`id`, 
  `FilteredAnnounceV`.`content`, 
  `FilteredAnnounceV`.`title`, 
  `FilteredAnnounceV`.`disp_date` 
FROM 
  `english`.`filtered_announces_v` AS `FilteredAnnounceV` 
  left JOIN `english`.`notifications_read` AS `NotificationsRead` ON (
    `NotificationsRead`.`notif_id` = `FilteredAnnounceV`.`id` 
    AND `NotificationsRead`.`notif_type` = 1 
    AND `NotificationsRead`.`user_id` = '61833'
  ) 
WHERE 
  `FilteredAnnounceV`.`kbn` = 1 
  AND `FilteredAnnounceV`.`device_display` LIKE '%1%' 
  AND `FilteredAnnounceV`.`language` = 'ja' 
  AND `FilteredAnnounceV`.`supported_language` = 'ja' 
  AND `FilteredAnnounceV`.`supported_currency` = 'TWD' 
  AND `FilteredAnnounceV`.`membership_type` = 1 
  AND (
    (
      (
        (
          `NotificationsRead`.`display_flag` = 1
        ) 
        AND (
          `NotificationsRead`.`read_flg` = '0'
        ) 
        AND (
          `NotificationsRead`.`user_id` = '61833'
        )
      )
    ) 
    OR (`NotificationsRead`.`id` is null)
  ) 
GROUP BY 
  `FilteredAnnounceV`.`id` 
ORDER BY 
  `disp_date` DESC, 
  `id` DESC
// end


______________________________________________________________________________________________________________________________________
  

// start
SELECT 
  `TeacherOccupationIndustry`.`name` AS `industry_name`, 
  `TeacherOccupationIndustryPosition`.`name` AS `position_name`, 
  `TeacherOccupationIndustryPosition`.`id` AS `position_primary_id` 
FROM 
  `english`.`teacher_occupation_industries` AS `TeacherOccupationIndustry` 
  LEFT JOIN `english`.`teacher_occupation_industry_positions` AS `TeacherOccupationIndustryPosition` ON (
    `TeacherOccupationIndustry`.`id` = `TeacherOccupationIndustryPosition`.`teacher_occupation_industry_id`
  ) 
WHERE 
  `TeacherOccupationIndustry`.`is_active` = 1 
GROUP BY 
  `TeacherOccupationIndustry`.`id`, 
  `TeacherOccupationIndustryPosition`.`id` 
ORDER BY 
  `TeacherOccupationIndustry`.`id` ASC
// end


______________________________________________________________________________________________________________________________________


SELECT 
  COUNT(*) AS `count` 
FROM 
  `english`.`users_deactivation_enquates` AS `UsersDeactivationEnquates` 
WHERE 
  `UsersDeactivationEnquates`.`user_id` = 61833


______________________________________________________________________________________________________________________________________


SELECT 
  COUNT(*) AS `count` 
FROM 
  `english`.`lesson_onairs_logs` AS `LessonOnairsLog` 
WHERE 
  `LessonOnairsLog`.`user_id` = 61833 
  AND `LessonOnairsLog`.`start_time` IS NOT NULL 
  AND `LessonOnairsLog`.`end_time` IS NOT NULL

______________________________________________________________________________________________________________________________________
  

SELECT 
  COUNT(*) AS `count` 
FROM 
  `english`.`phone_verify_check_logs` AS `PhoneVerifyCheckLog` 
WHERE 
  `user_id` = 61833 
  AND `status` = 0


______________________________________________________________________________________________________________________________________


SELECT 
  `Teacher`.`id` 
FROM 
  `english`.`teachers` AS `Teacher` 
WHERE 
  `Teacher`.`id` IN (630, 6148, 6347, 6398) 
  AND `Teacher`.`status` = '1' 
  AND `Teacher`.`avatar_parent_flg` = 1


______________________________________________________________________________________________________________________________________


SELECT 
  `TextbookCategory`.`textbook_category_type` 
FROM 
  `english`.`textbook_categories` AS `TextbookCategory` 
WHERE 
  `TextbookCategory`.`id` = 0 
LIMIT 
  1

______________________________________________________________________________________________________________________________________


SELECT 
  `ReservationCoin`.`id`, 
  `ReservationCoin`.`coin` 
FROM 
  `english`.`reservation_coins` AS `ReservationCoin` 
WHERE 
  `ReservationCoin`.`status` = 1 
GROUP BY 
  `ReservationCoin`.`coin` 
ORDER BY 
  `ReservationCoin`.`coin` ASC

______________________________________________________________________________________________________________________________________

// start
SELECT 
  COUNT(*) AS `count` 
FROM 
  `english`.`teachers` AS `Teacher` 
  LEFT JOIN `english`.`lesson_onairs` AS `LessonOnair` ON (
    `LessonOnair`.`teacher_id` = `Teacher`.`id`
  ) 
  LEFT JOIN `english`.`teacher_features` AS `TeacherFeature` ON (
    `TeacherFeature`.`teacher_id` = `Teacher`.`id`
  ) 
  LEFT JOIN `english`.`teacher_weekly_ratings` AS `TeacherWeeklyRating` ON (
    `TeacherWeeklyRating`.`teacher_id` = `Teacher`.`id`
  ) 
  LEFT JOIN `english`.`teacher_status` AS `TeacherStatus` ON (
    `TeacherStatus`.`teacher_id` = `Teacher`.`id`
  ) 
  LEFT JOIN `english`.`country_codes` AS `CountryCode` ON (
    `Teacher`.`homeland2` = `CountryCode`.`id`
  ) 
  LEFT JOIN `english`.`timezones` AS `Timezone` ON (
    `Timezone`.`id` = `Teacher`.`timezone_id`
  ) 
  LEFT JOIN `english`.`teacher_details` AS `TeacherDetail` ON (
    `TeacherDetail`.`teacher_id` = `Teacher`.`id`
  ) 
  LEFT JOIN `english`.`teacher_ratings_lessons` AS `TeacherRatingsLesson` ON (
    `TeacherRatingsLesson`.`teacher_id` = `Teacher`.`id`
  ) 
  left join (
    select 
      sw.lesson_time, 
      sw.teacher_id, 
      ls.status, 
      ls.lesson_time as sched_time, 
      ls.id 
    from 
      (
        select 
          lesson_time, 
          teacher_id 
        from 
          shift_workons 
        where 
          hide_flg = 0 
          and status = 1 
          and lesson_time = "2022-11-29 13:00:00"
      ) as sw 
      left join `lesson_schedules` as ls on (
        ls.lesson_time = sw.lesson_time 
        and sw.teacher_id = ls.teacher_id 
        and ls.status = 1
      )
  ) as nl on (nl.teacher_id = Teacher.id) 
  left join (
    select 
      mbs.lesson_time, 
      mbs.teacher_id 
    from 
      (
        select 
          lesson_time, 
          teacher_id 
        from 
          shift_work_meal_breaks 
        where 
          lesson_time = "2022-11-29 13:00:00"
      ) as mbs
  ) as mb on (mb.teacher_id = Teacher.id) 
  LEFT JOIN `english`.`home_based_rank_basic_amount_logs` AS `HomeBasedRankBasicAmountLog` ON (
    `HomeBasedRankBasicAmountLog`.`rank_id` = `Teacher`.`current_rank_id` 
    AND `HomeBasedRankBasicAmountLog`.`active` = 1
  ) 
  LEFT JOIN `english`.`home_based_reserve_coin_data_logs` AS `HomeBasedReserveCoinDataLog` ON (
    `HomeBasedReserveCoinDataLog`.`reserve_coin_data_id` = `Teacher`.`reserve_coin_data_id` 
    AND `HomeBasedReserveCoinDataLog`.`status` = 1
  ) 
  LEFT JOIN `english`.`teacher_rank_coins` AS `TeacherRankCoin` ON (
    `TeacherRankCoin`.`id` = `Teacher`.`rank_coin_id`
  ) 
WHERE 
  (
    (
      `Teacher`.`id` NOT IN (630, 6148, 6347, 6398)
    ) 
    AND (`Teacher`.`status` = '1') 
    AND (`Teacher`.`admin_flg` = '0') 
    AND (`Teacher`.`counseling_flg` = '0') 
    AND (
      (
        (
          `Teacher`.`avatar_parent_flg` = 0
        ) 
        AND (`Teacher`.`avatar_flg` = 0)
      )
    ) 
    AND (`Teacher`.`stealth_flg` <> '1') 
    AND (
      (
        (
          (
            (
              `LessonOnair`.`status` IN (1, 2, 3)
            ) 
            AND (
              `LessonOnair`.`connect_flg` = (1)
            )
          )
        ) 
        OR (
          (
            (
              `TeacherStatus`.`status` IN ('4', '5')
            ) 
            AND (
              (
                select 
                  count(*) 
                from 
                  lesson_onairs a 
                where 
                  `a`.`teacher_id` = `Teacher`.`id` 
                  and `a`.`connect_flg` = 0
              ) = 0
            )
          )
        )
      )
    ) 
    AND (
      `Teacher`.`native_speaker_flg` = 1
    ) 
    AND (
      `Teacher`.`last_login_time` >= '2022-10-02 00:00:00'
    ) 
    AND (
      (
        CASE WHEN (
          (
            `TeacherRankCoin`.`reserve_coin_settings_flg` = 1 
            AND `Teacher`.`reserve_coin_data_id` IS NOT NULL 
            AND `Teacher`.`avatar_flg` = 0 
            AND `Teacher`.`avatar_parent_flg` = 0 
            AND (
              `Teacher`.`native_speaker_flg` = 0 
              OR (
                `Teacher`.`native_speaker_flg` = 1 
                AND (
                  (
                    (0) = 0 
                    AND `Teacher`.`home_flg` = 1
                  ) 
                  OR (
                    (0) = 1 
                    AND `Teacher`.`home_flg` = 0
                  ) 
                  OR `HomeBasedReserveCoinDataLog`.`reserve_coin` < `HomeBasedRankBasicAmountLog`.`reserve_coin_with_op` 
                  OR `HomeBasedRankBasicAmountLog`.`reserve_coin_with_op` IS NULL
                )
              )
            )
          )
        ) THEN `HomeBasedReserveCoinDataLog`.`reserve_coin` BETWEEN 0 
        AND 1000 ELSE `HomeBasedRankBasicAmountLog`.`reserve_coin` BETWEEN 0 
        AND 1000 END
      )
    )
  ) 
GROUP BY 
  `Teacher`.`id`
// end


______________________________________________________________________________________________________________________________________


// start
SELECT 
  Teacher.id, 
  Teacher.name, 
  Teacher.jp_name, 
  Teacher.good_point, 
  Teacher.native_speaker_flg, 
  Teacher.callan_halfprice_flg, 
  Teacher.beginner_teacher_flg, 
  Teacher.translate_message, 
  Teacher.translate_self_introduction_third_pp, 
  Teacher.message_short, 
  Teacher.self_introduction_third_pp, 
  Teacher.image_url, 
  Teacher.reservation_hide_flg, 
  Teacher.rank_coin_id, 
  Teacher.staff_message, 
  Teacher.created, 
  Teacher.counseling_flg, 
  Teacher.birthday, 
  Teacher.homeland2, 
  Teacher.hobby, 
  Teacher.youtube_tag, 
  Teacher.lesson_count, 
  Teacher.sudden_count, 
  Teacher.reserve_count, 
  Teacher.timezone_dst_flg, 
  Teacher.instructor_history, 
  Teacher.noindex_flg, 
  Teacher.avatar_id, 
  Teacher.avatar_parent_flg, 
  Teacher.avatar_flg, 
  Teacher.avatar_sort, 
  Teacher.status, 
  Teacher.current_rank_id, 
  Teacher.last_login_time, 
  Teacher.stusap_lesson_count, 
  Teacher.home_flg, 
  Teacher.reserve_coin_data_id, 
  LessonOnair.status, 
  LessonOnair.connect_flg, 
  LessonOnair.chat_hash, 
  LessonOnair.lesson_type, 
  LessonOnair.user_id, 
  LessonOnair.live_lesson_flg, 
  LessonOnair.connect_id, 
  TeacherFeature.new, 
  TeacherFeature.best_free_talk, 
  TeacherFeature.good_in_teaching_textbook, 
  TeacherFeature.suitable_for_intermediate_or_advance_students, 
  TeacherFeature.have_many_beginner_students, 
  TeacherFeature.suitable_for_children, 
  TeacherFeature.suitable_for_senior, 
  TeacherFeature.good_grammar_and_vocabulary, 
  TeacherFeature.pronunciation, 
  TeacherStatus.status AS teacher_status, 
  TeacherStatus.remarks1, 
  TeacherStatus.remarks2, 
  TeacherStatus.created, 
  (
    CASE when TeacherStatus.status = 2 then (
      TIME_TO_SEC(
        TIMEDIFF(NOW(), TeacherStatus.created)
      )
    ) else 0 end
  ) as teacher_status_standby_duration, 
  CountryCode.id, 
  CountryCode.country_name, 
  Timezone.jp_time_diff, 
  Timezone.continent_id, 
  Timezone.city_eng, 
  TeacherWeeklyRating.ratings, 
  TeacherWeeklyRating.rate1, 
  TeacherWeeklyRating.rate2, 
  TeacherWeeklyRating.rate3, 
  TeacherWeeklyRating.rate4, 
  TeacherWeeklyRating.rate5, 
  TeacherDetail.country_id, 
  TeacherDetail.country, 
  TeacherRatingsLesson.ratings 
FROM 
  english.teachers AS Teacher 
  LEFT JOIN english.lesson_onairs AS LessonOnair ON (
    LessonOnair.teacher_id = Teacher.id
  ) 
  LEFT JOIN english.teacher_features AS TeacherFeature ON (
    TeacherFeature.teacher_id = Teacher.id
  ) 
  LEFT JOIN english.teacher_weekly_ratings AS TeacherWeeklyRating ON (
    TeacherWeeklyRating.teacher_id = Teacher.id
  ) 
  LEFT JOIN english.teacher_status AS TeacherStatus ON (
    TeacherStatus.teacher_id = Teacher.id
  ) 
  LEFT JOIN english.country_codes AS CountryCode ON (
    Teacher.homeland2 = CountryCode.id
  ) 
  LEFT JOIN english.timezones AS Timezone ON (
    Timezone.id = Teacher.timezone_id
  ) 
  LEFT JOIN english.teacher_details AS TeacherDetail ON (
    TeacherDetail.teacher_id = Teacher.id
  ) 
  LEFT JOIN english.teacher_ratings_lessons AS TeacherRatingsLesson ON (
    TeacherRatingsLesson.teacher_id = Teacher.id
  ) 
  left join (
    select 
      sw.lesson_time, 
      sw.teacher_id, 
      ls.status, 
      ls.lesson_time as sched_time, 
      ls.id 
    from 
      (
        select 
          lesson_time, 
          teacher_id 
        from 
          shift_workons 
        where 
          hide_flg = 0 
          and status = 1 
          and lesson_time = "2022-11-29 13:00:00"
      ) as sw 
      left join `lesson_schedules` as ls on (
        ls.lesson_time = sw.lesson_time 
        and sw.teacher_id = ls.teacher_id 
        and ls.status = 1
      )
  ) as nl on (nl.teacher_id = Teacher.id) 
  left join (
    select 
      mbs.lesson_time, 
      mbs.teacher_id 
    from 
      (
        select 
          lesson_time, 
          teacher_id 
        from 
          shift_work_meal_breaks 
        where 
          lesson_time = "2022-11-29 13:00:00"
      ) as mbs
  ) as mb on (mb.teacher_id = Teacher.id) 
  LEFT JOIN english.home_based_rank_basic_amount_logs AS HomeBasedRankBasicAmountLog ON (
    HomeBasedRankBasicAmountLog.rank_id = Teacher.current_rank_id 
    AND HomeBasedRankBasicAmountLog.active = 1
  ) 
  LEFT JOIN english.home_based_reserve_coin_data_logs AS HomeBasedReserveCoinDataLog ON (
    HomeBasedReserveCoinDataLog.reserve_coin_data_id = Teacher.reserve_coin_data_id 
    AND HomeBasedReserveCoinDataLog.status = 1
  ) 
  LEFT JOIN english.teacher_rank_coins AS TeacherRankCoin ON (
    TeacherRankCoin.id = Teacher.rank_coin_id
  ) 
WHERE 
  (
    (
      Teacher.id NOT IN (630, 6148, 6347, 6398)
    ) 
    AND (Teacher.status = '1') 
    AND (Teacher.admin_flg = '0') 
    AND (Teacher.counseling_flg = '0') 
    AND (
      (
        (Teacher.avatar_parent_flg = 0) 
        AND (Teacher.avatar_flg = 0)
      )
    ) 
    AND (Teacher.stealth_flg <> '1') 
    AND (
      (
        (
          (
            (
              LessonOnair.status IN (1, 2, 3)
            ) 
            AND (
              LessonOnair.connect_flg = (1)
            )
          )
        ) 
        OR (
          (
            (
              TeacherStatus.status IN ('4', '5')
            ) 
            AND (
              (
                select 
                  count(*) 
                from 
                  lesson_onairs a 
                where 
                  a.teacher_id = Teacher.id 
                  and a.connect_flg = 0
              ) = 0
            )
          )
        )
      )
    ) 
    AND (Teacher.native_speaker_flg = 1) 
    AND (
      Teacher.last_login_time >= '2022-10-02 00:00:00'
    ) 
    AND (
      (
        CASE WHEN (
          (
            `TeacherRankCoin`.`reserve_coin_settings_flg` = 1 
            AND `Teacher`.`reserve_coin_data_id` IS NOT NULL 
            AND `Teacher`.`avatar_flg` = 0 
            AND `Teacher`.`avatar_parent_flg` = 0 
            AND (
              `Teacher`.`native_speaker_flg` = 0 
              OR (
                `Teacher`.`native_speaker_flg` = 1 
                AND (
                  (
                    (0) = 0 
                    AND `Teacher`.`home_flg` = 1
                  ) 
                  OR (
                    (0) = 1 
                    AND `Teacher`.`home_flg` = 0
                  ) 
                  OR `HomeBasedReserveCoinDataLog`.`reserve_coin` < `HomeBasedRankBasicAmountLog`.`reserve_coin_with_op` 
                  OR `HomeBasedRankBasicAmountLog`.`reserve_coin_with_op` IS NULL
                )
              )
            )
          )
        ) THEN HomeBasedReserveCoinDataLog.reserve_coin BETWEEN 0 
        AND 1000 ELSE HomeBasedRankBasicAmountLog.reserve_coin BETWEEN 0 
        AND 1000 END
      )
    )
  ) 
GROUP BY 
  Teacher.id 
ORDER BY 
  (
    select 
      if(
        count(*) > 0, 
        1, 
        0
      ) 
    FROM 
      lesson_onairs 
    WHERE 
      teacher_id = Teacher.id 
      AND status = 1 
      AND connect_flg = 1 
    LIMIT 
      1
  ) desc, 
  (
    select 
      if(
        count(*) > 0, 
        2, 
        0
      ) 
    from 
      teacher_status 
    where 
      teacher_id = Teacher.id 
      and (
        status = 4 
        and (
          remarks1 = 2 
          OR remarks2 = "after_lesson_other"
        )
      ) 
    LIMIT 
      1
  ) desc, 
  (
    if (
      (
        select 
          if (
            count(*) > 0, 
            1, 
            0
          ) 
        from 
          teacher_status 
        where 
          teacher_id = Teacher.id 
          and status = 4 
          and remarks1 <> 2 
          and (
            remarks2 IS NULL 
            or remarks2 <> "after_lesson_other"
          ) 
        LIMIT 
          1
      ) OR (
        select 
          if (
            count(*) > 0, 
            1, 
            0
          ) 
        from 
          lesson_onairs 
        where 
          teacher_id = Teacher.id 
          and status <> 1 
          and connect_flg = 1 
        LIMIT 
          1
      ), 3, 
      0
    )
  ) desc, 
  TeacherWeeklyRating.ratings desc, 
  Teacher.lesson_count desc, 
  Teacher.name asc 
LIMIT 
  5
// end

______________________________________________________________________________________________________________________________________


SELECT 
  `SettingOption`.`value` 
FROM 
  `english`.`setting_options` AS `SettingOption` 
WHERE 
  `SettingOption`.`name` = 'recommended_teacher_title' 
LIMIT 
  1

______________________________________________________________________________________________________________________________________


SELECT 
  `Timezone`.`id`, 
  `Timezone`.`continent_id`, 
  `Timezone`.`city_eng`, 
  `Timezone`.`utc_offset`, 
  `Timezone`.`jp_time_diff`, 
  `Timezone`.`country_code_id` 
FROM 
  `english`.`timezones` AS `Timezone`

______________________________________________________________________________________________________________________________________ 


// start
SELECT 
  `UsersTextbookInfo`.`last_viewed_connect_id`, 
  `UsersTextbookInfo`.`last_viewed_category_type`, 
  `UsersTextbookInfo`.`last_viewed_category_id`, 
  `UsersTextbookInfo`.`last_viewed_textbook_type`, 
  `UsersTextbookInfo`.`last_viewed_date`, 
  `UsersTextbookInfo`.`preset_connect_id`, 
  `UsersTextbookInfo`.`preset_last_viewed_date`, 
  `UsersTextbookInfo`.`preset_category_id`, 
  `UsersTextbookInfo`.`preset_category_type`, 
  `UsersTextbookInfo`.`preset_textbook_type` 
FROM 
  `english`.`users_textbook_infos` AS `UsersTextbookInfo` 
WHERE 
  `UsersTextbookInfo`.`user_id` = 61833 
  AND `UsersTextbookInfo`.`user_type` = 0 
  AND `UsersTextbookInfo`.`native_language` = 'ja' 
LIMIT 
  1 
SELECT 
  `User`.`id`, 
  `User`.`studysapuri_id`, 
  `User`.`payment_plan_id` 
FROM 
  `english`.`users` AS `User` 
WHERE 
  `User`.`id` = 61833 
LIMIT 
  1
// end

______________________________________________________________________________________________________________________________________


// start
SELECT 
  `Textbook`.`id`, 
  `Textbook`.`name`, 
  `Textbook`.`chapter_id`, 
  `Textbook`.`sort`, 
  `TextbookConnect`.`id`, 
  `TextbookConnect`.`category_id`, 
  `TextbookConnect`.`textbook_id`, 
  `TextbookConnect`.`subcategory_id`, 
  `TextbookConnect`.`sort`, 
  `TextbookCategory`.`id`, 
  `TextbookCategory`.`name`, 
  `TextbookCategory`.`english_name`, 
  `TextbookCategory`.`type_id`, 
  `TextbookCategory`.`image_small_url`, 
  `TextbookCategory`.`reservation_flg`, 
  `TextbookCategory`.`textbook_category_type`, 
  `TextbookCategory`.`sort`, 
  `TextbookSubcategory`.`id`, 
  `TextbookSubcategory`.`name`, 
  `TextbookSubcategory`.`english_name`, 
  `TextbookSubcategory`.`sort` 
FROM 
  `english`.`textbook_connects` AS `TextbookConnect` 
  LEFT JOIN `english`.`textbooks` AS `Textbook` ON (
    `TextbookConnect`.`textbook_id` = `Textbook`.`id`
  ) 
  LEFT JOIN `english`.`textbook_categories` AS `TextbookCategory` ON (
    `TextbookConnect`.`category_id` = `TextbookCategory`.`id`
  ) 
  LEFT JOIN `english`.`textbook_subcategories` AS `TextbookSubcategory` ON (
    `TextbookConnect`.`subcategory_id` = `TextbookSubcategory`.`id`
  ) 
WHERE 
  (
    (
      `TextbookConnect`.`category_id` != 0
    ) 
    AND (
      `TextbookConnect`.`textbook_id` != 0
    ) 
    AND (
      `TextbookSubcategory`.`status` = 1
    ) 
    AND (`TextbookCategory`.`status` = 1) 
    AND (`Textbook`.`status` = 1) 
    AND (
      `Textbook`.`callan_level_check` = 0
    )
  ) 
  AND `TextbookConnect`.`id` = 13567 
  AND `TextbookCategory`.`textbook_category_type` NOT IN (8, 9) 
  AND `TextbookCategory`.`ja_display_flg` = 1 
ORDER BY 
  `TextbookCategory`.`sort` ASC, 
  `TextbookSubcategory`.`sort` ASC, 
  `TextbookConnect`.`sort` ASC, 
  `Textbook`.`sort` ASC 
LIMIT 
  1 
SELECT 
  `BlockList`.`id`, 
  `BlockList`.`teacher_id` 
FROM 
  `english`.`block_lists` AS `BlockList` 
WHERE 
  `BlockList`.`user_id` = 61833 
  AND `BlockList`.`status` = '1'
// end

______________________________________________________________________________________________________________________________________


SELECT 
  `TextbookCategory`.`id`, 
  `TextbookCategory`.`name`, 
  `TextbookCategory`.`english_name` 
FROM 
  `english`.`textbook_connects` AS `TextbookConnect` 
  INNER JOIN `english`.`textbook_categories` AS `TextbookCategory` ON (
    `TextbookCategory`.`id` = `TextbookConnect`.`category_id`
  ) 
WHERE 
  `TextbookCategory`.`status` = '1' 
  AND `TextbookCategory`.`type_id` = '2' 
GROUP BY 
  `TextbookConnect`.`category_id` 
ORDER BY 
  `TextbookCategory`.`sort` ASC

______________________________________________________________________________________________________________________________________


// start
SELECT 
  `CountryCode`.`region`, 
  `CountryCode`.`country_name`, 
  `CountryCode`.`nationality`, 
  `CountryCode`.`id`, 
  `CountryCode`.`code` 
FROM 
  `english`.`teachers` AS `Teacher` 
  LEFT JOIN `english`.`country_codes` AS `CountryCode` ON (
    `Teacher`.`homeland2` = `CountryCode`.`id`
  ) 
WHERE 
  `Teacher`.`status` = 1 
  AND `Teacher`.`stealth_flg` = 0 
  AND `Teacher`.`admin_flg` = 0 
  AND `Teacher`.`avatar_flg` = 0 
  AND `Teacher`.`avatar_parent_flg` = 0 
  AND `Teacher`.`counseling_flg` = 0 
  AND `Teacher`.`homeland2` IS NOT NULL 
  AND `Teacher`.`last_login_time` >= "2022-10-02 00:00:00" 
GROUP BY 
  `CountryCode`.`region`, 
  `CountryCode`.`id` 
ORDER BY 
  `CountryCode`.`region` ASC, 
  `CountryCode`.`nationality` ASC
// end

______________________________________________________________________________________________________________________________________


SELECT 
  `LessonOnairsLog`.`user_agent` 
FROM 
  `english`.`lesson_onairs_logs` AS `LessonOnairsLog` 
WHERE 
  `LessonOnairsLog`.`user_id` = 61833

______________________________________________________________________________________________________________________________________


SELECT 
  COUNT(*) AS `count` 
FROM 
  `english`.`user_search_conditions` AS `UserSearchCondition` 
WHERE 
  `UserSearchCondition`.`user_id` = 61833

______________________________________________________________________________________________________________________________________


// start
SELECT 
  `User`.`api_token`, 
  `User`.`id`, 
  `User`.`parent_id`, 
  `User`.`password`, 
  `User`.`admin_flg`, 
  `User`.`charge_flg`, 
  `User`.`fail_flg`, 
  `User`.`created`, 
  `User`.`email`, 
  `User`.`gender`, 
  `User`.`birthday`, 
  `User`.`status`, 
  `User`.`nickname`, 
  `User`.`image_url`, 
  `User`.`profile_image`, 
  `User`.`hash16`, 
  `User`.`campaign_id`, 
  `User`.`next_charge_date`, 
  `User`.`sms_through_flg`, 
  `User`.`sns_topic_arn`, 
  `User`.`double_check_flg`, 
  `User`.`birthday_show_flg`, 
  `User`.`card_company`, 
  `User`.`magazine_flg`, 
  `User`.`timezone_id`, 
  `User`.`timezone_dst_flg`, 
  `User`.`reservation_notif_count`, 
  `User`.`reservation_mail_flg`, 
  `User`.`reservation_cancel_mail_flg`, 
  `User`.`heavy_user_flg`, 
  `User`.`callan_level_check`, 
  `User`.`counseling_attended_flg`, 
  `User`.`country_code`, 
  `User`.`device`, 
  `User`.`corporate_id`, 
  `User`.`corporate_type`, 
  `User`.`corporate_individual_payment_flg`, 
  `User`.`native_language2`, 
  `User`.`settlement_currency_id`, 
  `User`.`monthly_payment`, 
  `User`.`gender_show_flg`, 
  `User`.`residence_show_flg`, 
  `User`.`nationality_show_flg`, 
  `User`.`residence_id`, 
  `User`.`nationality_id`, 
  `User`.`currency_code`, 
  `User`.`complimentary_code`, 
  `User`.`first_charge_date`, 
  `User`.`invalid_email_flg`, 
  `User`.`monthly_speaking_attended_flg`, 
  `User`.`monthly_speaking_business_attended_flg`, 
  `User`.`payment_plan_id`, 
  `User`.`nickname_change_date`, 
  `User`.`memo`, 
  `User`.`idfa`, 
  `User`.`adid`, 
  `User`.`new_coupon_flg`, 
  `User`.`studysapuri_id`, 
  `User`.`studysapuri_coin_flg`, 
  `User`.`card_brand`, 
  `User`.`card_token`, 
  `User`.`wp_transaction_identifier`, 
  `User`.`paypal_billing_agreement_id`, 
  `User`.`allow_appreciation_flg`, 
  `User`.`show_appreciation_flg`, 
  `User`.`allow_appreciation_message_flg`, 
  `User`.`display_appreciation_message_flg`, 
  `User`.`paypal_payer_id`, 
  `User`.`native_option`, 
  `User`.`price_id`, 
  `User`.`slot_notif_guide_flg`, 
  `User`.`apple_id`, 
  `User`.`apple_access_token`, 
  `User`.`camera_toggle_flg`, 
  `User`.`comment_own_topic_flg`, 
  `User`.`topic_user_comment_flg` 
FROM 
  `english`.`users` AS `User` 
WHERE 
  `User`.`api_token` = 'lester_api_token' 
LIMIT 
  1
// end

______________________________________________________________________________________________________________________________________

// start 
SELECT 
  `User`.`id`, 
  `User`.`nickname`, 
  `FamilyPlanList`.`family_name`, 
  `User`.`email`, 
  `User`.`status`, 
  `User`.`charge_flg`, 
  `User`.`fail_flg`, 
  `User`.`double_check_flg`, 
  `User`.`next_charge_date`, 
  `User`.`parent_id`, 
  `User`.`hash16`, 
  `User`.`image_url`, 
  `User`.`profile_image`, 
  `User`.`api_token`, 
  `User`.`payment_plan_id`, 
  `FamilyPlanList`.`id`, 
  `FamilyPlanList`.`family_id`, 
  `FamilyPlanList`.`approve_flg`, 
  `FamilyPlanList`.`relation` 
FROM 
  `english`.`users` AS `User` 
  LEFT JOIN `english`.`family_plan_lists` AS `FamilyPlanList` ON (
    `FamilyPlanList`.`family_id` = `User`.`id`
  ) 
WHERE 
  `FamilyPlanList`.`parent_id` = 61833 
  AND `FamilyPlanList`.`approve_flg` = 1 
  AND `User`.`id` <> 61833
// end

_____________________________________________________________________________________________________________________________________


// start
SELECT 
  `User`.*, 
  (
    SELECT 
      `fpl`.`parent_name` 
    FROM 
      family_plan_lists AS fpl 
    WHERE 
      `fpl`.`parent_id` = `User`.`id` 
      AND `fpl`.`approve_flg` = 1 
      AND `fpl`.`parent_name` IS NOT NULL 
    ORDER BY 
      `fpl`.`id` DESC 
    LIMIT 
      1
  ) AS `User__parent_name` 
FROM 
  `english`.`users` AS `User` 
WHERE 
  `User`.`id` = 61833 
LIMIT 
  1
// end
_____________________________________________________________________________________________________________________________________
// start
SELECT 
  `UsersFamilyAccount`.`id`, 
  `UsersFamilyAccount`.`switch_accounts` 
FROM 
  `english`.`user_family_accounts` AS `UsersFamilyAccount` 
WHERE 
  `UsersFamilyAccount`.`user_id` = 61833 
  AND `UsersFamilyAccount`.`family_id` = 61833 
  AND `UsersFamilyAccount`.`status` = 1 
LIMIT 
  1
// end

_____________________________________________________________________________________________________________________________________

// start
SELECT 
  `UsersFamilyAccount`.`switch_accounts` 
FROM 
  `english`.`user_family_accounts` AS `UsersFamilyAccount` 
WHERE 
  `UsersFamilyAccount`.`user_id` = 61833 
  AND `UsersFamilyAccount`.`family_id` = 61833 
  AND `UsersFamilyAccount`.`status` = 1 
LIMIT 
  1
// end