How to make an SQL query to get only 1 row with specific data?

I have the following MySQL query:

SELECT work_from, work_until, restaurant_id, from_date, to_date FROM restaurant_working_times
WHERE restaurant_id = ?
  and restaurant_id is not null
  and `week_day` = ?
  and `is_closed` = 0
  and ((from_date <= ? and until_date >= ?) or (from_date is null and until_date is null))
ORDER BY (from_date IS NOT NULL AND until_date IS NOT NULL) DESC
LIMIT 1

The logic of the query is to get opening hours for the restaurant. There’re 2 types of open hours:

Permanent. It is default opening hours for restaurant for every day
Specific. It is a row that contains the range between from_date and until_date dates when this schedule works.

Query gets the rows which:

has current day of week in ISO format ISO (1 => Monday etc)
where current date is in the range of from_date and until_date (custom restaurant schedule) or from_date and until_date equals to NULL (default restaurant schedule)
where is_closed column equals to 0 (if the column equals to 0, then the restaurant closed today)

And then I sort the result by from_date and until_date and limit the result by 1 row, so if restaurant has a custom schedule, then I’ll take the row that contains custom schedule for today. If no, I’ll take the row with default schedule.

The problem: this query allows to get opening hours for certain restaurant (I have to execute this query for each restaurant). It makes the N+1 problem if I want to get the list of the open restaurants.

Question: what is the query that will find open restaurants?

restaurant_working_times table schema

+----+---------------+----------+------------+------------+-----------+------------+-----------+
| id | restaurant_id | week_day | from_date  | until_date | from_hour | until_hour | is_closed |
+----+---------------+----------+------------+------------+-----------+------------+-----------+
|  8 |             5 |        1 | NULL       | NULL       | 10:00:00  | 22:00:00   |         0 |
| 15 |             5 |        1 | 2020-09-01 | 2020-09-10 | 10:00:00  | 22:00:00   |         0 |
|  9 |             5 |        2 | NULL       | NULL       | 10:00:00  | 22:00:00   |         0 |
| 16 |             5 |        2 | 2020-09-01 | 2020-09-10 | 10:00:00  | 22:00:00   |         0 |
| 10 |             5 |        3 | NULL       | NULL       | 10:00:00  | 22:00:00   |         0 |
| 17 |             5 |        3 | 2020-09-01 | 2020-09-10 | 10:00:00  | 22:00:00   |         0 |
| 11 |             5 |        4 | NULL       | NULL       | 10:00:00  | 22:00:00   |         0 |
| 18 |             5 |        4 | 2020-09-01 | 2020-09-10 | 10:00:00  | 22:00:00   |         0 |
| 12 |             5 |        5 | NULL       | NULL       | 10:00:00  | 22:00:00   |         0 |
| 19 |             5 |        5 | 2020-09-01 | 2020-09-10 | 10:00:00  | 22:00:00   |         0 |
| 13 |             5 |        6 | NULL       | NULL       | 10:00:00  | 22:00:00   |         0 |
| 20 |             5 |        6 | 2020-09-01 | 2020-09-10 | 10:00:00  | 22:00:00   |         0 |
| 14 |             5 |        7 | NULL       | NULL       | 10:00:00  | 22:00:00   |         0 |
| 21 |             5 |        7 | 2020-09-01 | 2020-09-10 | 10:00:00  | 22:00:00   |         0 |
+----+---------------+----------+------------+------------+-----------+------------+-----------+

I want to get the output with the following columns:

id, restaurant_id, from_hour, until_hour

There should be only open restaurants, so open restaurant means that we found the row with restaurant_id where current time greater than from_hour and less than until_hour. Also I should repeat that “custom” schedule has a higher priority than “default” schedule. “custom” schedule means that from_date and until_date are not equal to null. If there’s no custom schedule for the day — we take “default” schedule, if there’s no default schedule — the restaurant is closed today.

Answer

This is a prioritization problem. For each restaurant, you want the row with dates to have the priority. To accomplish this, you can use window functions:

SELCT rwt.
FROM (SELECT rwt.*,
             ROW_NUMBER() OVER (PARTITION BY rwt.restaurant_id
                                ORDER BY (rwt.from_date is not null AND rwt.until_date is not null) DESC
                               ) as seqnum
      FROM restaurant_working_times rwt
      WHERE rwt.restaurant_id IS NOT NULL AND
            rwt.week_day = ? AND
            rwt.is_closed = 0 AND
            (rwt.from_date <= ? AND rwt.until_date >= ? OR 
             rwt.from_date is null AND rwt.until_date is null
            )
     ) rwt
WHERE seqnum = 1;

EDIT:

In older versions of MySQL, you can phrase this as:

select rwt.*
from restaurant_working_times rwt
where rwt.restaurant_id IS NOT NULL AND
      rwt.week_day = ? AND
      rwt.is_closed = 0 AND
      (rwt.from_date <= ? AND rwt.until_date >= ?)
union all
select rwt.*
from restaurant_working_times rwt
where rwt.restaurant_id IS NOT NULL AND
      rwt.week_day = ? AND
      rwt.is_closed = 0 AND
      (rwt.from_date is null AND rwt.until_date is null) and
      not exists (select 1
                  from restaurant_working_times rwt2
                  where rwt2.restaurant_id IS NOT NULL AND
                        rwt2.week_day = rwt.week_day AND
                        rwt2.is_closed = 0 and
                        (rwt2.from_date <= ? AND rwt2.until_date >= ?)
                ) ;

Note that the number of parameters has changed. This is a case where named parameters would be helpful.

Advertisement

Answer