Symfony Profiler

SELECT
  h.id,
  h.name,
  h.surface,
  h.nb_places AS nbPlaces,
  h.nb_confortable_places AS nbConfortablePlaces,
  h.max_nb_places_with_baby AS maxNbPlacesWithBaby,
  h.nb_rooms AS nbRooms,
  h.points,
  h.latitude,
  h.longitude,
  ht.id AS hostingTypeId,
  ht.name AS hostingTypeName,
  s.name AS stationName,
  s.slug_name AS stationSlug,
  s.massif AS stationMassif,
  s.department AS stationDepartment,
  CASE WHEN r.boost_date >= NOW() THEN
    1
  ELSE
    0
  END AS boost,
  COALESCE(r.exceptional_rental, 0) AS isExclusive,
  COALESCE(
    r.system_is_renting_downgraded,
    0
  ) AS isDowngraded,
  hc.classification AS hostingClassification,
  hc.level AS hostingClassificationLevel,
  hd_s.distance AS distanceFromStationKm,
  hd_l.distance AS distanceFromSkiLiftKm,
  COALESCE(l.trusted, 0) AS isTrusted,
  UNIX_TIMESTAMP(r.updated_at) AS updatedAt,
  MIN(
    CASE WHEN ps.end_date >= CURDATE() THEN
      ps.week_price
    ELSE
      NULL
    END
  ) AS minPricePerWeek,
  MAX(
    CASE WHEN ps.end_date >= CURDATE() THEN
      ps.week_price
    ELSE
      NULL
    END
  ) AS maxPricePerWeek,
  MIN(
    CASE WHEN ps.end_date >= CURDATE() THEN
      ps.base_price
    ELSE
      NULL
    END
  ) AS minBasePrice,
  MAX(
    CASE WHEN ps.end_date >= CURDATE() THEN
      ps.base_price
    ELSE
      NULL
    END
  ) AS maxBasePrice,
  MAX(
    CASE WHEN (
      (
        ps.week_price IS NOT NULL
        AND ps.week_price > 0
        AND ps.discount_percentage IS NOT NULL
        AND ps.discount_percentage > 0
        AND ps.end_date > CURDATE()
        AND (
          ps.starting_date IS NULL
          OR ps.starting_date <= NOW()
        )
      )
    ) THEN
      1
    ELSE
      0
    END
  ) AS isLastMinute,
  CAST(
    FLOOR(
      SUBSTRING_INDEX(
        GROUP_CONCAT(
          CASE WHEN (
            (
              ps.week_price IS NOT NULL
              AND ps.week_price > 0
              AND ps.discount_percentage IS NOT NULL
              AND ps.discount_percentage > 0
              AND ps.end_date > CURDATE()
              AND (
                ps.starting_date IS NULL
                OR ps.starting_date <= NOW()
              )
            )
          ) THEN
            ps.week_price
          ELSE
            NULL
          END
          ORDER BY
            DATE_ADD(
              CASE WHEN ps.start_date < CURDATE() THEN
                DATE_ADD(
                  CURDATE(),
                  INTERVAL 1 DAY
                )
              ELSE
                ps.start_date
              END,
              INTERVAL (
                (
                  COALESCE(
                    CASE ps.arrival_day
                    WHEN 'Sunday' THEN
                      1
                    WHEN 'Monday' THEN
                      2
                    WHEN 'Tuesday' THEN
                      3
                    WHEN 'Wednesday' THEN
                      4
                    WHEN 'Thursday' THEN
                      5
                    WHEN 'Friday' THEN
                      6
                    WHEN 'Saturday' THEN
                      7
                    ELSE
                      NULL
                    END,
                    DAYOFWEEK(
                      CASE WHEN ps.start_date < CURDATE() THEN
                        DATE_ADD(
                          CURDATE(),
                          INTERVAL 1 DAY
                        )
                      ELSE
                        ps.start_date
                      END
                    )
                  ) - DAYOFWEEK(
                    CASE WHEN ps.start_date < CURDATE() THEN
                      DATE_ADD(
                        CURDATE(),
                        INTERVAL 1 DAY
                      )
                    ELSE
                      ps.start_date
                    END
                  ) + 7
                ) % 7
              ) DAY
            ) ASC,
            ps.id ASC SEPARATOR '||'
        ),
        '||',
        1
      )
    ) AS UNSIGNED
  ) AS lastMinutePrice,
  CAST(
    SUBSTRING_INDEX(
      GROUP_CONCAT(
        CASE WHEN (
          (
            ps.week_price IS NOT NULL
            AND ps.week_price > 0
            AND ps.discount_percentage IS NOT NULL
            AND ps.discount_percentage > 0
            AND ps.end_date > CURDATE()
            AND (
              ps.starting_date IS NULL
              OR ps.starting_date <= NOW()
            )
          )
        ) THEN
          ps.base_price
        ELSE
          NULL
        END
        ORDER BY
          DATE_ADD(
            CASE WHEN ps.start_date < CURDATE() THEN
              DATE_ADD(
                CURDATE(),
                INTERVAL 1 DAY
              )
            ELSE
              ps.start_date
            END,
            INTERVAL (
              (
                COALESCE(
                  CASE ps.arrival_day
                  WHEN 'Sunday' THEN
                    1
                  WHEN 'Monday' THEN
                    2
                  WHEN 'Tuesday' THEN
                    3
                  WHEN 'Wednesday' THEN
                    4
                  WHEN 'Thursday' THEN
                    5
                  WHEN 'Friday' THEN
                    6
                  WHEN 'Saturday' THEN
                    7
                  ELSE
                    NULL
                  END,
                  DAYOFWEEK(
                    CASE WHEN ps.start_date < CURDATE() THEN
                      DATE_ADD(
                        CURDATE(),
                        INTERVAL 1 DAY
                      )
                    ELSE
                      ps.start_date
                    END
                  )
                ) - DAYOFWEEK(
                  CASE WHEN ps.start_date < CURDATE() THEN
                    DATE_ADD(
                      CURDATE(),
                      INTERVAL 1 DAY
                    )
                  ELSE
                    ps.start_date
                  END
                ) + 7
              ) % 7
            ) DAY
          ) ASC,
          ps.id ASC SEPARATOR '||'
      ),
      '||',
      1
    ) AS UNSIGNED
  ) AS basePrice,
  CAST(
    SUBSTRING_INDEX(
      GROUP_CONCAT(
        CASE WHEN (
          (
            ps.week_price IS NOT NULL
            AND ps.week_price > 0
            AND ps.discount_percentage IS NOT NULL
            AND ps.discount_percentage > 0
            AND ps.end_date > CURDATE()
            AND (
              ps.starting_date IS NULL
              OR ps.starting_date <= NOW()
            )
          )
        ) THEN
          ps.id
        ELSE
          NULL
        END
        ORDER BY
          DATE_ADD(
            CASE WHEN ps.start_date < CURDATE() THEN
              DATE_ADD(
                CURDATE(),
                INTERVAL 1 DAY
              )
            ELSE
              ps.start_date
            END,
            INTERVAL (
              (
                COALESCE(
                  CASE ps.arrival_day
                  WHEN 'Sunday' THEN
                    1
                  WHEN 'Monday' THEN
                    2
                  WHEN 'Tuesday' THEN
                    3
                  WHEN 'Wednesday' THEN
                    4
                  WHEN 'Thursday' THEN
                    5
                  WHEN 'Friday' THEN
                    6
                  WHEN 'Saturday' THEN
                    7
                  ELSE
                    NULL
                  END,
                  DAYOFWEEK(
                    CASE WHEN ps.start_date < CURDATE() THEN
                      DATE_ADD(
                        CURDATE(),
                        INTERVAL 1 DAY
                      )
                    ELSE
                      ps.start_date
                    END
                  )
                ) - DAYOFWEEK(
                  CASE WHEN ps.start_date < CURDATE() THEN
                    DATE_ADD(
                      CURDATE(),
                      INTERVAL 1 DAY
                    )
                  ELSE
                    ps.start_date
                  END
                ) + 7
              ) % 7
            ) DAY
          ) ASC,
          ps.id ASC SEPARATOR '||'
      ),
      '||',
      1
    ) AS UNSIGNED
  ) AS lastMinutePricingScheduleId,
  CAST(
    SUBSTRING_INDEX(
      GROUP_CONCAT(
        CASE WHEN (
          (
            ps.week_price IS NOT NULL
            AND ps.week_price > 0
            AND ps.discount_percentage IS NOT NULL
            AND ps.discount_percentage > 0
            AND ps.end_date > CURDATE()
            AND (
              ps.starting_date IS NULL
              OR ps.starting_date <= NOW()
            )
          )
        ) THEN
          ps.discount_percentage
        ELSE
          NULL
        END
        ORDER BY
          DATE_ADD(
            CASE WHEN ps.start_date < CURDATE() THEN
              DATE_ADD(
                CURDATE(),
                INTERVAL 1 DAY
              )
            ELSE
              ps.start_date
            END,
            INTERVAL (
              (
                COALESCE(
                  CASE ps.arrival_day
                  WHEN 'Sunday' THEN
                    1
                  WHEN 'Monday' THEN
                    2
                  WHEN 'Tuesday' THEN
                    3
                  WHEN 'Wednesday' THEN
                    4
                  WHEN 'Thursday' THEN
                    5
                  WHEN 'Friday' THEN
                    6
                  WHEN 'Saturday' THEN
                    7
                  ELSE
                    NULL
                  END,
                  DAYOFWEEK(
                    CASE WHEN ps.start_date < CURDATE() THEN
                      DATE_ADD(
                        CURDATE(),
                        INTERVAL 1 DAY
                      )
                    ELSE
                      ps.start_date
                    END
                  )
                ) - DAYOFWEEK(
                  CASE WHEN ps.start_date < CURDATE() THEN
                    DATE_ADD(
                      CURDATE(),
                      INTERVAL 1 DAY
                    )
                  ELSE
                    ps.start_date
                  END
                ) + 7
              ) % 7
            ) DAY
          ) ASC,
          ps.id ASC SEPARATOR '||'
      ),
      '||',
      1
    ) AS UNSIGNED
  ) AS discountPercentage,
  SUBSTRING_INDEX(
    GROUP_CONCAT(
      CASE WHEN (
        (
          ps.week_price IS NOT NULL
          AND ps.week_price > 0
          AND ps.discount_percentage IS NOT NULL
          AND ps.discount_percentage > 0
          AND ps.end_date > CURDATE()
          AND (
            ps.starting_date IS NULL
            OR ps.starting_date <= NOW()
          )
        )
      ) THEN
        DATE_ADD(
          CASE WHEN ps.start_date < CURDATE() THEN
            DATE_ADD(
              CURDATE(),
              INTERVAL 1 DAY
            )
          ELSE
            ps.start_date
          END,
          INTERVAL (
            (
              COALESCE(
                CASE ps.arrival_day
                WHEN 'Sunday' THEN
                  1
                WHEN 'Monday' THEN
                  2
                WHEN 'Tuesday' THEN
                  3
                WHEN 'Wednesday' THEN
                  4
                WHEN 'Thursday' THEN
                  5
                WHEN 'Friday' THEN
                  6
                WHEN 'Saturday' THEN
                  7
                ELSE
                  NULL
                END,
                DAYOFWEEK(
                  CASE WHEN ps.start_date < CURDATE() THEN
                    DATE_ADD(
                      CURDATE(),
                      INTERVAL 1 DAY
                    )
                  ELSE
                    ps.start_date
                  END
                )
              ) - DAYOFWEEK(
                CASE WHEN ps.start_date < CURDATE() THEN
                  DATE_ADD(
                    CURDATE(),
                    INTERVAL 1 DAY
                  )
                ELSE
                  ps.start_date
                END
              ) + 7
            ) % 7
          ) DAY
        )
      ELSE
        NULL
      END
      ORDER BY
        DATE_ADD(
          CASE WHEN ps.start_date < CURDATE() THEN
            DATE_ADD(
              CURDATE(),
              INTERVAL 1 DAY
            )
          ELSE
            ps.start_date
          END,
          INTERVAL (
            (
              COALESCE(
                CASE ps.arrival_day
                WHEN 'Sunday' THEN
                  1
                WHEN 'Monday' THEN
                  2
                WHEN 'Tuesday' THEN
                  3
                WHEN 'Wednesday' THEN
                  4
                WHEN 'Thursday' THEN
                  5
                WHEN 'Friday' THEN
                  6
                WHEN 'Saturday' THEN
                  7
                ELSE
                  NULL
                END,
                DAYOFWEEK(
                  CASE WHEN ps.start_date < CURDATE() THEN
                    DATE_ADD(
                      CURDATE(),
                      INTERVAL 1 DAY
                    )
                  ELSE
                    ps.start_date
                  END
                )
              ) - DAYOFWEEK(
                CASE WHEN ps.start_date < CURDATE() THEN
                  DATE_ADD(
                    CURDATE(),
                    INTERVAL 1 DAY
                  )
                ELSE
                  ps.start_date
                END
              ) + 7
            ) % 7
          ) DAY
        ) ASC,
        ps.id ASC SEPARATOR '||'
    ),
    '||',
    1
  ) AS lastMinuteDateStart,
  SUBSTRING_INDEX(
    GROUP_CONCAT(
      CASE WHEN (
        (
          ps.week_price IS NOT NULL
          AND ps.week_price > 0
          AND ps.discount_percentage IS NOT NULL
          AND ps.discount_percentage > 0
          AND ps.end_date > CURDATE()
          AND (
            ps.starting_date IS NULL
            OR ps.starting_date <= NOW()
          )
        )
      ) THEN
        DATE_ADD(
          DATE_ADD(
            CASE WHEN ps.start_date < CURDATE() THEN
              DATE_ADD(
                CURDATE(),
                INTERVAL 1 DAY
              )
            ELSE
              ps.start_date
            END,
            INTERVAL (
              (
                COALESCE(
                  CASE ps.arrival_day
                  WHEN 'Sunday' THEN
                    1
                  WHEN 'Monday' THEN
                    2
                  WHEN 'Tuesday' THEN
                    3
                  WHEN 'Wednesday' THEN
                    4
                  WHEN 'Thursday' THEN
                    5
                  WHEN 'Friday' THEN
                    6
                  WHEN 'Saturday' THEN
                    7
                  ELSE
                    NULL
                  END,
                  DAYOFWEEK(
                    CASE WHEN ps.start_date < CURDATE() THEN
                      DATE_ADD(
                        CURDATE(),
                        INTERVAL 1 DAY
                      )
                    ELSE
                      ps.start_date
                    END
                  )
                ) - DAYOFWEEK(
                  CASE WHEN ps.start_date < CURDATE() THEN
                    DATE_ADD(
                      CURDATE(),
                      INTERVAL 1 DAY
                    )
                  ELSE
                    ps.start_date
                  END
                ) + 7
              ) % 7
            ) DAY
          ),
          INTERVAL COALESCE(ps.min_nb_of_nights, 7) DAY
        )
      ELSE
        NULL
      END
      ORDER BY
        DATE_ADD(
          CASE WHEN ps.start_date < CURDATE() THEN
            DATE_ADD(
              CURDATE(),
              INTERVAL 1 DAY
            )
          ELSE
            ps.start_date
          END,
          INTERVAL (
            (
              COALESCE(
                CASE ps.arrival_day
                WHEN 'Sunday' THEN
                  1
                WHEN 'Monday' THEN
                  2
                WHEN 'Tuesday' THEN
                  3
                WHEN 'Wednesday' THEN
                  4
                WHEN 'Thursday' THEN
                  5
                WHEN 'Friday' THEN
                  6
                WHEN 'Saturday' THEN
                  7
                ELSE
                  NULL
                END,
                DAYOFWEEK(
                  CASE WHEN ps.start_date < CURDATE() THEN
                    DATE_ADD(
                      CURDATE(),
                      INTERVAL 1 DAY
                    )
                  ELSE
                    ps.start_date
                  END
                )
              ) - DAYOFWEEK(
                CASE WHEN ps.start_date < CURDATE() THEN
                  DATE_ADD(
                    CURDATE(),
                    INTERVAL 1 DAY
                  )
                ELSE
                  ps.start_date
                END
              ) + 7
            ) % 7
          ) DAY
        ) ASC,
        ps.id ASC SEPARATOR '||'
    ),
    '||',
    1
  ) AS lastMinuteDateEnd,
  SUBSTRING_INDEX(
    GROUP_CONCAT(
      CASE WHEN (
        (
          ps.week_price IS NOT NULL
          AND ps.week_price > 0
          AND ps.discount_percentage IS NOT NULL
          AND ps.discount_percentage > 0
          AND ps.end_date > CURDATE()
          AND (
            ps.starting_date IS NULL
            OR ps.starting_date <= NOW()
          )
        )
      ) THEN
        ps.arrival_day
      ELSE
        NULL
      END
      ORDER BY
        DATE_ADD(
          CASE WHEN ps.start_date < CURDATE() THEN
            DATE_ADD(
              CURDATE(),
              INTERVAL 1 DAY
            )
          ELSE
            ps.start_date
          END,
          INTERVAL (
            (
              COALESCE(
                CASE ps.arrival_day
                WHEN 'Sunday' THEN
                  1
                WHEN 'Monday' THEN
                  2
                WHEN 'Tuesday' THEN
                  3
                WHEN 'Wednesday' THEN
                  4
                WHEN 'Thursday' THEN
                  5
                WHEN 'Friday' THEN
                  6
                WHEN 'Saturday' THEN
                  7
                ELSE
                  NULL
                END,
                DAYOFWEEK(
                  CASE WHEN ps.start_date < CURDATE() THEN
                    DATE_ADD(
                      CURDATE(),
                      INTERVAL 1 DAY
                    )
                  ELSE
                    ps.start_date
                  END
                )
              ) - DAYOFWEEK(
                CASE WHEN ps.start_date < CURDATE() THEN
                  DATE_ADD(
                    CURDATE(),
                    INTERVAL 1 DAY
                  )
                ELSE
                  ps.start_date
                END
              ) + 7
            ) % 7
          ) DAY
        ) ASC,
        ps.id ASC SEPARATOR '||'
    ),
    '||',
    1
  ) AS lastMinuteArrivalDay
FROM
  hosting h
  INNER JOIN rental r ON h.rental_id = r.id
  LEFT JOIN hosting_type ht ON h.hosting_type_id = ht.id
  LEFT JOIN station s ON h.main_station_id = s.id
  LEFT JOIN user u ON h.lessor_id = u.id
  LEFT JOIN pricing_schedule ps ON ps.hosting_id = h.id
  AND (
    ps.starting_date IS NULL
    OR ps.starting_date <= NOW()
  )
  LEFT JOIN tag t ON h.tag_id = t.id
  LEFT JOIN hosting_class hc ON h.hosting_class_id = hc.id
  LEFT JOIN hosting_distance hd_s ON h.distance_from_station_km_id = hd_s.id
  LEFT JOIN hosting_distance hd_l ON h.distance_from_ski_lift_id = hd_l.id
  LEFT JOIN lessor l ON l.id = u.id
WHERE
  (h.deleted_at IS NULL)
  AND (r.deleted_at IS NULL)
  AND (r.system_is_renting_allowed = 1)
GROUP BY
  h.id
HAVING
  MAX(
    CASE WHEN ps.end_date >= CURDATE() THEN
      ps.base_price
    ELSE
      NULL
    END
  ) > 0
ORDER BY
  boost DESC,
  isExclusive DESC,
  isDowngraded ASC,
  h.points DESC
LIMIT
  20

SELECT h.id, h.name, h.surface, h.nb_places AS nbPlaces, h.nb_confortable_places AS nbConfortablePlaces, h.max_nb_places_with_baby AS maxNbPlacesWithBaby, h.nb_rooms AS nbRooms, h.points, h.latitude, h.longitude, ht.id AS hostingTypeId, ht.name AS hostingTypeName, s.name AS stationName, s.slug_name AS stationSlug, s.massif AS stationMassif, s.department AS stationDepartment, CASE WHEN r.boost_date >= NOW() THEN 1 ELSE 0 END AS boost, COALESCE(r.exceptional_rental, 0) AS isExclusive, COALESCE(r.system_is_renting_downgraded, 0) AS isDowngraded, hc.classification AS hostingClassification, hc.level AS hostingClassificationLevel, hd_s.distance AS distanceFromStationKm, hd_l.distance AS distanceFromSkiLiftKm, COALESCE(l.trusted, 0) AS isTrusted, UNIX_TIMESTAMP(r.updated_at) AS updatedAt, MIN(CASE WHEN ps.end_date >= CURDATE() THEN ps.week_price ELSE NULL END) AS minPricePerWeek, MAX(CASE WHEN ps.end_date >= CURDATE() THEN ps.week_price ELSE NULL END) AS maxPricePerWeek, MIN(CASE WHEN ps.end_date >= CURDATE() THEN ps.base_price ELSE NULL END) AS minBasePrice, MAX(CASE WHEN ps.end_date >= CURDATE() THEN ps.base_price ELSE NULL END) AS maxBasePrice, MAX(CASE WHEN ((ps.week_price IS NOT NULL AND ps.week_price > 0 AND ps.discount_percentage IS NOT NULL AND ps.discount_percentage > 0 AND ps.end_date > CURDATE() AND (ps.starting_date IS NULL OR ps.starting_date <= NOW()))) THEN 1 ELSE 0 END) AS isLastMinute, CAST(FLOOR(SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN ((ps.week_price IS NOT NULL AND ps.week_price > 0 AND ps.discount_percentage IS NOT NULL AND ps.discount_percentage > 0 AND ps.end_date > CURDATE() AND (ps.starting_date IS NULL OR ps.starting_date <= NOW()))) THEN ps.week_price ELSE NULL END ORDER BY DATE_ADD(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END, INTERVAL ((COALESCE(CASE ps.arrival_day WHEN 'Sunday' THEN 1 WHEN 'Monday' THEN 2 WHEN 'Tuesday' THEN 3 WHEN 'Wednesday' THEN 4 WHEN 'Thursday' THEN 5 WHEN 'Friday' THEN 6 WHEN 'Saturday' THEN 7 ELSE NULL END, DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END)) - DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END) + 7) % 7) DAY) ASC, ps.id ASC SEPARATOR '||'), '||', 1)) AS UNSIGNED) AS lastMinutePrice, CAST(SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN ((ps.week_price IS NOT NULL AND ps.week_price > 0 AND ps.discount_percentage IS NOT NULL AND ps.discount_percentage > 0 AND ps.end_date > CURDATE() AND (ps.starting_date IS NULL OR ps.starting_date <= NOW()))) THEN ps.base_price ELSE NULL END ORDER BY DATE_ADD(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END, INTERVAL ((COALESCE(CASE ps.arrival_day WHEN 'Sunday' THEN 1 WHEN 'Monday' THEN 2 WHEN 'Tuesday' THEN 3 WHEN 'Wednesday' THEN 4 WHEN 'Thursday' THEN 5 WHEN 'Friday' THEN 6 WHEN 'Saturday' THEN 7 ELSE NULL END, DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END)) - DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END) + 7) % 7) DAY) ASC, ps.id ASC SEPARATOR '||'), '||', 1) AS UNSIGNED) AS basePrice, CAST(SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN ((ps.week_price IS NOT NULL AND ps.week_price > 0 AND ps.discount_percentage IS NOT NULL AND ps.discount_percentage > 0 AND ps.end_date > CURDATE() AND (ps.starting_date IS NULL OR ps.starting_date <= NOW()))) THEN ps.id ELSE NULL END ORDER BY DATE_ADD(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END, INTERVAL ((COALESCE(CASE ps.arrival_day WHEN 'Sunday' THEN 1 WHEN 'Monday' THEN 2 WHEN 'Tuesday' THEN 3 WHEN 'Wednesday' THEN 4 WHEN 'Thursday' THEN 5 WHEN 'Friday' THEN 6 WHEN 'Saturday' THEN 7 ELSE NULL END, DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END)) - DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END) + 7) % 7) DAY) ASC, ps.id ASC SEPARATOR '||'), '||', 1) AS UNSIGNED) AS lastMinutePricingScheduleId, CAST(SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN ((ps.week_price IS NOT NULL AND ps.week_price > 0 AND ps.discount_percentage IS NOT NULL AND ps.discount_percentage > 0 AND ps.end_date > CURDATE() AND (ps.starting_date IS NULL OR ps.starting_date <= NOW()))) THEN ps.discount_percentage ELSE NULL END ORDER BY DATE_ADD(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END, INTERVAL ((COALESCE(CASE ps.arrival_day WHEN 'Sunday' THEN 1 WHEN 'Monday' THEN 2 WHEN 'Tuesday' THEN 3 WHEN 'Wednesday' THEN 4 WHEN 'Thursday' THEN 5 WHEN 'Friday' THEN 6 WHEN 'Saturday' THEN 7 ELSE NULL END, DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END)) - DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END) + 7) % 7) DAY) ASC, ps.id ASC SEPARATOR '||'), '||', 1) AS UNSIGNED) AS discountPercentage, SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN ((ps.week_price IS NOT NULL AND ps.week_price > 0 AND ps.discount_percentage IS NOT NULL AND ps.discount_percentage > 0 AND ps.end_date > CURDATE() AND (ps.starting_date IS NULL OR ps.starting_date <= NOW()))) THEN DATE_ADD(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END, INTERVAL ((COALESCE(CASE ps.arrival_day WHEN 'Sunday' THEN 1 WHEN 'Monday' THEN 2 WHEN 'Tuesday' THEN 3 WHEN 'Wednesday' THEN 4 WHEN 'Thursday' THEN 5 WHEN 'Friday' THEN 6 WHEN 'Saturday' THEN 7 ELSE NULL END, DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END)) - DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END) + 7) % 7) DAY) ELSE NULL END ORDER BY DATE_ADD(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END, INTERVAL ((COALESCE(CASE ps.arrival_day WHEN 'Sunday' THEN 1 WHEN 'Monday' THEN 2 WHEN 'Tuesday' THEN 3 WHEN 'Wednesday' THEN 4 WHEN 'Thursday' THEN 5 WHEN 'Friday' THEN 6 WHEN 'Saturday' THEN 7 ELSE NULL END, DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END)) - DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END) + 7) % 7) DAY) ASC, ps.id ASC SEPARATOR '||'), '||', 1) AS lastMinuteDateStart, SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN ((ps.week_price IS NOT NULL AND ps.week_price > 0 AND ps.discount_percentage IS NOT NULL AND ps.discount_percentage > 0 AND ps.end_date > CURDATE() AND (ps.starting_date IS NULL OR ps.starting_date <= NOW()))) THEN DATE_ADD(DATE_ADD(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END, INTERVAL ((COALESCE(CASE ps.arrival_day WHEN 'Sunday' THEN 1 WHEN 'Monday' THEN 2 WHEN 'Tuesday' THEN 3 WHEN 'Wednesday' THEN 4 WHEN 'Thursday' THEN 5 WHEN 'Friday' THEN 6 WHEN 'Saturday' THEN 7 ELSE NULL END, DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END)) - DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END) + 7) % 7) DAY), INTERVAL COALESCE(ps.min_nb_of_nights, 7) DAY) ELSE NULL END ORDER BY DATE_ADD(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END, INTERVAL ((COALESCE(CASE ps.arrival_day WHEN 'Sunday' THEN 1 WHEN 'Monday' THEN 2 WHEN 'Tuesday' THEN 3 WHEN 'Wednesday' THEN 4 WHEN 'Thursday' THEN 5 WHEN 'Friday' THEN 6 WHEN 'Saturday' THEN 7 ELSE NULL END, DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END)) - DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END) + 7) % 7) DAY) ASC, ps.id ASC SEPARATOR '||'), '||', 1) AS lastMinuteDateEnd, SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN ((ps.week_price IS NOT NULL AND ps.week_price > 0 AND ps.discount_percentage IS NOT NULL AND ps.discount_percentage > 0 AND ps.end_date > CURDATE() AND (ps.starting_date IS NULL OR ps.starting_date <= NOW()))) THEN ps.arrival_day ELSE NULL END ORDER BY DATE_ADD(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END, INTERVAL ((COALESCE(CASE ps.arrival_day WHEN 'Sunday' THEN 1 WHEN 'Monday' THEN 2 WHEN 'Tuesday' THEN 3 WHEN 'Wednesday' THEN 4 WHEN 'Thursday' THEN 5 WHEN 'Friday' THEN 6 WHEN 'Saturday' THEN 7 ELSE NULL END, DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END)) - DAYOFWEEK(CASE WHEN ps.start_date < CURDATE() THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) ELSE ps.start_date END) + 7) % 7) DAY) ASC, ps.id ASC SEPARATOR '||'), '||', 1) AS lastMinuteArrivalDay FROM hosting h INNER JOIN rental r ON h.rental_id = r.id LEFT JOIN hosting_type ht ON h.hosting_type_id = ht.id LEFT JOIN station s ON h.main_station_id = s.id LEFT JOIN user u ON h.lessor_id = u.id LEFT JOIN pricing_schedule ps ON ps.hosting_id = h.id AND (ps.starting_date IS NULL OR ps.starting_date <= NOW()) LEFT JOIN tag t ON h.tag_id = t.id LEFT JOIN hosting_class hc ON h.hosting_class_id = hc.id LEFT JOIN hosting_distance hd_s ON h.distance_from_station_km_id = hd_s.id LEFT JOIN hosting_distance hd_l ON h.distance_from_ski_lift_id = hd_l.id LEFT JOIN lessor l ON l.id = u.id WHERE (h.deleted_at IS NULL) AND (r.deleted_at IS NULL) AND (r.system_is_renting_allowed = 1) GROUP BY h.id HAVING MAX(CASE WHEN ps.end_date >= CURDATE() THEN ps.base_price ELSE NULL END) > 0 ORDER BY boost DESC, isExclusive DESC, isDowngraded ASC, h.points DESC LIMIT 20;