Symfony Profiler

WITH
  all_boxes AS (
    SELECT
      id AS box_id,
      unique_id AS box_unique_id,
      'car' AS vehicle_type
    FROM
      car_box
    WHERE
      visible = 1
      AND deleted_at IS NULL
      AND unique_id IS NOT NULL
      AND unique_id <> ''
    UNION ALL
    SELECT
      id AS box_id,
      unique_id AS box_unique_id,
      'bike' AS vehicle_type
    FROM
      bike_box
    WHERE
      visible = 1
      AND deleted_at IS NULL
      AND unique_id IS NOT NULL
      AND unique_id <> ''
  ),
  boxes AS (
    SELECT
      *
    FROM
      all_boxes
  ),
  total_usage AS (
    SELECT
      code_id,
      COUNT(*) AS usage_count
    FROM
      promotion_code_used
    WHERE
      is_used = 1
    GROUP BY
      code_id
  ),
  user_usage AS (
    SELECT
      code_id,
      COUNT(*) AS usage_count
    FROM
      promotion_code_used
    WHERE
      is_used = 1
      AND user_id = ?
    GROUP BY
      code_id
  ),
  eligible_promotions AS (
    SELECT
      pc.id,
      pc.code,
      pc.name,
      pc.name_vn,
      pc.discount_percent,
      pc.max_discount_amount,
      pc.start_at,
      pc.expired_at,
      pc.has_consumption_limit,
      pc.total_wattage_consumed_usage,
      pc.vehicle_type
    FROM
      promotion_code pc
      LEFT JOIN total_usage total_u ON total_u.code_id = pc.id
      LEFT JOIN user_usage user_u ON user_u.code_id = pc.id
    WHERE
      pc.enabled = 1
      AND pc.deleted_at IS NULL
      AND (
        pc.start_at IS NULL
        OR pc.start_at <= DATE_ADD(
          UTC_TIMESTAMP(),
          INTERVAL 7 HOUR
        )
      )
      AND (
        pc.expired_at IS NULL
        OR pc.expired_at >= DATE_ADD(
          UTC_TIMESTAMP(),
          INTERVAL 7 HOUR
        )
      )
      AND (
        COALESCE(pc.has_consumption_limit, 0) = 0
        OR COALESCE(
          pc.total_wattage_consumed_usage,
          0
        ) > 0
      )
      AND (
        COALESCE(pc.max_total_usage, 0) <= 0
        OR COALESCE(total_u.usage_count, 0) < pc.max_total_usage
      )
      AND (
        COALESCE(pc.max_usage_per_user, 0) <= 0
        OR COALESCE(user_u.usage_count, 0) < pc.max_usage_per_user
      )
      AND NOT EXISTS (
        SELECT
          1
        FROM
          promotion_code_user user_block
        WHERE
          user_block.promotion_code_id = pc.id
          AND user_block.user_id = ?
          AND user_block.is_blocked = 1
      )
      AND (
        NOT EXISTS (
          SELECT
            1
          FROM
            promotion_code_user user_rule
          WHERE
            user_rule.promotion_code_id = pc.id
        )
        OR EXISTS (
          SELECT
            1
          FROM
            promotion_code_user user_allow
          WHERE
            user_allow.promotion_code_id = pc.id
            AND user_allow.user_id = ?
            AND user_allow.is_blocked = 0
        )
      )
      AND pc.code NOT IN (?)
  ),
  ranked AS (
    SELECT
      boxes.box_id,
      boxes.box_unique_id,
      boxes.vehicle_type AS box_vehicle_type,
      promotion.id AS promotion_id,
      promotion.code,
      promotion.name,
      promotion.name_vn,
      promotion.discount_percent,
      promotion.max_discount_amount,
      promotion.start_at,
      promotion.expired_at,
      promotion.has_consumption_limit,
      promotion.total_wattage_consumed_usage,
      ROW_NUMBER() OVER (
        PARTITION BY
          boxes.vehicle_type,
          boxes.box_id
        ORDER BY
          COALESCE(promotion.discount_percent, -1) DESC,
          promotion.id DESC
      ) AS promotion_rank
    FROM
      boxes
      INNER JOIN eligible_promotions promotion ON (
        promotion.vehicle_type IS NULL
        OR promotion.vehicle_type = boxes.vehicle_type
      )
      AND NOT EXISTS (
        SELECT
          1
        FROM
          promotion_code_box box_block
        WHERE
          box_block.promotion_code_id = promotion.id
          AND box_block.box_unique_id = boxes.box_unique_id
          AND box_block.is_blocked = 1
          AND (
            box_block.vehicle_type IS NULL
            OR box_block.vehicle_type = boxes.vehicle_type
          )
      )
      AND (
        NOT EXISTS (
          SELECT
            1
          FROM
            promotion_code_box box_rule
          WHERE
            box_rule.promotion_code_id = promotion.id
            AND box_rule.is_blocked = 0
            AND (
              box_rule.vehicle_type IS NULL
              OR box_rule.vehicle_type = boxes.vehicle_type
            )
        )
        OR EXISTS (
          SELECT
            1
          FROM
            promotion_code_box box_allow
          WHERE
            box_allow.promotion_code_id = promotion.id
            AND box_allow.box_unique_id = boxes.box_unique_id
            AND box_allow.is_blocked = 0
            AND (
              box_allow.vehicle_type IS NULL
              OR box_allow.vehicle_type = boxes.vehicle_type
            )
        )
      )
  )
SELECT
  *
FROM
  ranked
WHERE
  promotion_rank = 1
ORDER BY
  box_vehicle_type ASC,
  box_id ASC

WITH all_boxes AS (
        SELECT id AS box_id, unique_id AS box_unique_id, 'car' AS vehicle_type
        FROM car_box
        WHERE visible = 1
            AND deleted_at IS NULL
            AND unique_id IS NOT NULL
            AND unique_id <> ''

        UNION ALL

        SELECT id AS box_id, unique_id AS box_unique_id, 'bike' AS vehicle_type
        FROM bike_box
        WHERE visible = 1
            AND deleted_at IS NULL
            AND unique_id IS NOT NULL
            AND unique_id <> ''
    ),
    boxes AS (
        SELECT *
        FROM all_boxes
        
    ),
    total_usage AS (
        SELECT code_id, COUNT(*) AS usage_count
        FROM promotion_code_used
        WHERE is_used = 1
        GROUP BY code_id
    ),
    user_usage AS (
        SELECT code_id, COUNT(*) AS usage_count
        FROM promotion_code_used
        WHERE is_used = 1
            AND user_id = 22655
        GROUP BY code_id
    ),
    eligible_promotions AS (
        SELECT
            pc.id,
            pc.code,
            pc.name,
            pc.name_vn,
            pc.discount_percent,
            pc.max_discount_amount,
            pc.start_at,
            pc.expired_at,
            pc.has_consumption_limit,
            pc.total_wattage_consumed_usage,
            pc.vehicle_type
        FROM promotion_code pc
        LEFT JOIN total_usage total_u ON total_u.code_id = pc.id
        LEFT JOIN user_usage user_u ON user_u.code_id = pc.id
        WHERE pc.enabled = 1
            AND pc.deleted_at IS NULL
            AND (pc.start_at IS NULL OR pc.start_at <= DATE_ADD(UTC_TIMESTAMP(), INTERVAL 7 HOUR))
            AND (pc.expired_at IS NULL OR pc.expired_at >= DATE_ADD(UTC_TIMESTAMP(), INTERVAL 7 HOUR))
            AND (COALESCE(pc.has_consumption_limit, 0) = 0 OR COALESCE(pc.total_wattage_consumed_usage, 0) > 0)
            AND (COALESCE(pc.max_total_usage, 0) <= 0 OR COALESCE(total_u.usage_count, 0) < pc.max_total_usage)
            AND (COALESCE(pc.max_usage_per_user, 0) <= 0 OR COALESCE(user_u.usage_count, 0) < pc.max_usage_per_user)
            AND NOT EXISTS (
                SELECT 1
                FROM promotion_code_user user_block
                WHERE user_block.promotion_code_id = pc.id
                    AND user_block.user_id = 22655
                    AND user_block.is_blocked = 1
            )
            AND (
                NOT EXISTS (
                    SELECT 1
                    FROM promotion_code_user user_rule
                    WHERE user_rule.promotion_code_id = pc.id
                )
                OR EXISTS (
                    SELECT 1
                    FROM promotion_code_user user_allow
                    WHERE user_allow.promotion_code_id = pc.id
                        AND user_allow.user_id = 22655
                        AND user_allow.is_blocked = 0
                )
            )
            AND pc.code NOT IN ('EboostXinChao')
    ),
    ranked AS (
        SELECT
            boxes.box_id,
            boxes.box_unique_id,
            boxes.vehicle_type AS box_vehicle_type,
            promotion.id AS promotion_id,
            promotion.code,
            promotion.name,
            promotion.name_vn,
            promotion.discount_percent,
            promotion.max_discount_amount,
            promotion.start_at,
            promotion.expired_at,
            promotion.has_consumption_limit,
            promotion.total_wattage_consumed_usage,
            ROW_NUMBER() OVER (
                PARTITION BY boxes.vehicle_type, boxes.box_id
                ORDER BY COALESCE(promotion.discount_percent, -1) DESC, promotion.id DESC
            ) AS promotion_rank
        FROM boxes
        INNER JOIN eligible_promotions promotion
            ON (promotion.vehicle_type IS NULL OR promotion.vehicle_type = boxes.vehicle_type)
            AND NOT EXISTS (
                SELECT 1
                FROM promotion_code_box box_block
                WHERE box_block.promotion_code_id = promotion.id
                    AND box_block.box_unique_id = boxes.box_unique_id
                    AND box_block.is_blocked = 1
                    AND (box_block.vehicle_type IS NULL OR box_block.vehicle_type = boxes.vehicle_type)
            )
            AND (
                NOT EXISTS (
                    SELECT 1
                    FROM promotion_code_box box_rule
                    WHERE box_rule.promotion_code_id = promotion.id
                        AND box_rule.is_blocked = 0
                        AND (box_rule.vehicle_type IS NULL OR box_rule.vehicle_type = boxes.vehicle_type)
                )
                OR EXISTS (
                    SELECT 1
                    FROM promotion_code_box box_allow
                    WHERE box_allow.promotion_code_id = promotion.id
                        AND box_allow.box_unique_id = boxes.box_unique_id
                        AND box_allow.is_blocked = 0
                        AND (box_allow.vehicle_type IS NULL OR box_allow.vehicle_type = boxes.vehicle_type)
                )
            )
    )
    SELECT *
    FROM ranked
    WHERE promotion_rank = 1
    ORDER BY box_vehicle_type ASC, box_id ASC;

SELECT
  t0.id AS id_1,
  t0.username AS username_2,
  t0.email AS email_3,
  t0.name AS name_4,
  t0.password AS password_5,
  t0.roles AS roles_6,
  t0.password_change_date AS password_change_date_7,
  t0.activated_mail AS activated_mail_8,
  t0.balance AS balance_9,
  t0.fp_token AS fp_token_10,
  t0.fp_token_expires_at AS fp_token_expires_at_11,
  t0.su_token AS su_token_12,
  t0.su_token_created_at AS su_token_created_at_13,
  t0.phone_number AS phone_number_14,
  t0.avatar_url AS avatar_url_15,
  t0.google_id AS google_id_16,
  t0.apple_id AS apple_id_17,
  t0.is_read_term AS is_read_term_18,
  t0.address AS address_19,
  t0.is_phone_verified AS is_phone_verified_20,
  t0.is_new AS is_new_21,
  t0.citizen_identification AS citizen_identification_22,
  t0.is_citizen_verified AS is_citizen_verified_23,
  t0.date_of_birth AS date_of_birth_24,
  t0.auto_charge AS auto_charge_25,
  t0.auto_apply_promotion_code AS auto_apply_promotion_code_26,
  t0.activity_point AS activity_point_27,
  t0.charge_point AS charge_point_28,
  t0.pay_point AS pay_point_29,
  t0.referral_point AS referral_point_30,
  t0.token_version AS token_version_31,
  t0.created_at AS created_at_32,
  t0.updated_at AS updated_at_33,
  t0.deleted_at AS deleted_at_34,
  t0.enabled AS enabled_35,
  t0.third_party_id AS third_party_id_36,
  t0.language_id AS language_id_37,
  t0.ward_id AS ward_id_38,
  t0.user_level_id AS user_level_id_39
FROM
  user t0
WHERE
  t0.id = ?

SELECT t0.id AS id_1, t0.username AS username_2, t0.email AS email_3, t0.name AS name_4, t0.password AS password_5, t0.roles AS roles_6, t0.password_change_date AS password_change_date_7, t0.activated_mail AS activated_mail_8, t0.balance AS balance_9, t0.fp_token AS fp_token_10, t0.fp_token_expires_at AS fp_token_expires_at_11, t0.su_token AS su_token_12, t0.su_token_created_at AS su_token_created_at_13, t0.phone_number AS phone_number_14, t0.avatar_url AS avatar_url_15, t0.google_id AS google_id_16, t0.apple_id AS apple_id_17, t0.is_read_term AS is_read_term_18, t0.address AS address_19, t0.is_phone_verified AS is_phone_verified_20, t0.is_new AS is_new_21, t0.citizen_identification AS citizen_identification_22, t0.is_citizen_verified AS is_citizen_verified_23, t0.date_of_birth AS date_of_birth_24, t0.auto_charge AS auto_charge_25, t0.auto_apply_promotion_code AS auto_apply_promotion_code_26, t0.activity_point AS activity_point_27, t0.charge_point AS charge_point_28, t0.pay_point AS pay_point_29, t0.referral_point AS referral_point_30, t0.token_version AS token_version_31, t0.created_at AS created_at_32, t0.updated_at AS updated_at_33, t0.deleted_at AS deleted_at_34, t0.enabled AS enabled_35, t0.third_party_id AS third_party_id_36, t0.language_id AS language_id_37, t0.ward_id AS ward_id_38, t0.user_level_id AS user_level_id_39 FROM user t0 WHERE t0.id = 22655;

SELECT
  t0.id AS id_1,
  t0.email AS email_2,
  t0.username AS username_3,
  t0.password AS password_4,
  t0.name AS name_5,
  t0.avatar_url AS avatar_url_6,
  t0.token_version AS token_version_7,
  t0.roles AS roles_8,
  t0.password_change_date AS password_change_date_9,
  t0.fp_token AS fp_token_10,
  t0.fp_token_expires_at AS fp_token_expires_at_11,
  t0.created_at AS created_at_12,
  t0.updated_at AS updated_at_13,
  t0.deleted_at AS deleted_at_14,
  t0.enabled AS enabled_15
FROM
  admin t0
WHERE
  t0.username = ?
  AND t0.enabled = ?
  AND t0.deleted_at IS NULL
LIMIT
  1

SELECT t0.id AS id_1, t0.email AS email_2, t0.username AS username_3, t0.password AS password_4, t0.name AS name_5, t0.avatar_url AS avatar_url_6, t0.token_version AS token_version_7, t0.roles AS roles_8, t0.password_change_date AS password_change_date_9, t0.fp_token AS fp_token_10, t0.fp_token_expires_at AS fp_token_expires_at_11, t0.created_at AS created_at_12, t0.updated_at AS updated_at_13, t0.deleted_at AS deleted_at_14, t0.enabled AS enabled_15 FROM admin t0 WHERE t0.username = 'developers@eboost.vn' AND t0.enabled = 1 AND t0.deleted_at IS NULL LIMIT 1;

SELECT
  t0.id AS id_1,
  t0.email AS email_2,
  t0.username AS username_3,
  t0.password AS password_4,
  t0.name AS name_5,
  t0.avatar_url AS avatar_url_6,
  t0.token_version AS token_version_7,
  t0.roles AS roles_8,
  t0.password_change_date AS password_change_date_9,
  t0.fp_token AS fp_token_10,
  t0.fp_token_expires_at AS fp_token_expires_at_11,
  t0.created_at AS created_at_12,
  t0.updated_at AS updated_at_13,
  t0.deleted_at AS deleted_at_14,
  t0.enabled AS enabled_15
FROM
  admin t0
WHERE
  t0.username = ?
LIMIT
  1

SELECT t0.id AS id_1, t0.email AS email_2, t0.username AS username_3, t0.password AS password_4, t0.name AS name_5, t0.avatar_url AS avatar_url_6, t0.token_version AS token_version_7, t0.roles AS roles_8, t0.password_change_date AS password_change_date_9, t0.fp_token AS fp_token_10, t0.fp_token_expires_at AS fp_token_expires_at_11, t0.created_at AS created_at_12, t0.updated_at AS updated_at_13, t0.deleted_at AS deleted_at_14, t0.enabled AS enabled_15 FROM admin t0 WHERE t0.username = 'developers@eboost.vn' LIMIT 1;