Как проверить повторения в бд и выдать номер?

Question

[del993788]

Здравствуйте. Пишу скрипт для бронирования номеров в гостинице.
Есть две вот такие таблицы:
allnomer sqlfiddle.com/#!9/75102 , где указаны все номера. В гостинице есть 3 номера lux: 301, 302 и 303
и main sqlfiddle.com/#!9/02c4e , где указаны все брони.
Я пытаюсь сделать скрипт, который будет проверять номера на занятость, и в случае нахождения свободного номера, выдаёт брони этот номер.
Чуть подробнее. У меня есть дата заезда и дата выезда новой брони: 2016-11-28 и 2016-11-30. Это диапазон дат. То есть 28, 29 и 30 числа в гостинице собираются жить люди. В гостинице уже забронированы два номера. Первый (301) на даты с 2016-11-29 по 2016-12-01, второй (302) на даты с 2016-12-01 по 2016-12-02. Человек не может заехать в 301 номер, так как 29 и 30 числа этот номер будет занят. У нас остаётся два номера: 302 и 303. 302 номер занят только 01 и 02. Ни одна дата не пересекается и поэтому нам 302 номер подходит. Переменная $namenomer принимает значение 302 и подставляется в скрипт.
Подскажите, как сделать проверку на свободность номера, чтобы, в случае хотя бы одного совпадения, проверялся следующий номер? Вероятность того, что на этом этапе не найдётся ни одного свободного номера исключена. До этого этапа уже есть скрипт, который проверяет все номера на свободность. Однако он проверяет все номера. Этот же скрипт нужен для проверки определённых номеров.

Вот такой у меня сейчас код:

SELECT a.nomer
FROM allnomer a
LEFT JOIN main m ON
a.nomer = m.numbernomer AND (
m.datestart BETWEEN '2016-11-28' AND '2016-11-30' OR
m.dateend BETWEEN '2016-11-28' AND '2016-11-30' )
WHERE a.TYPE = 'lux' AND m.id IS NULL

И эти даты он обрабатывает правильно. Я получаю ответ 302.

Но если структура таблицы main вот такая sqlfiddle.com/#!9/fba1bd
А запрос вот такой:

SELECT a.nomer
FROM allnomer a
LEFT JOIN main m ON
a.nomer = m.numbernomer AND (
m.datestart BETWEEN '2016-12-23' AND '2016-12-23' OR
m.dateend BETWEEN '2016-12-23' AND '2016-12-23' )
WHERE a.TYPE = 'lux' AND m.id IS NULL

То возвращается 301, хотя 23 число находится в диапазоне чисел таблицы main.
Тоже самое со всеми числами, которые находятся в этом промежутке, кроме 19 и 25 числа.

Comments

[del993788]

latteo , какими судьбами?

Answer 1

[Сергей Горностаев]

Во-первых, стоит повысить нормализацию:

create table rooms (id integer primary key, type varchar(7), number integer);

insert into rooms
    (id, type, number)
values
    (1, 'lux', 301),
    (2, 'lux', 302),
    (3, 'lux', 303);

create table booking (
  id integer primary key,
  start_date date,
  end_date date,
  room integer,
  foreign key(room) references rooms(id)
);
	
insert into booking (id, start_date, end_date, room)
values
    (1, '2016-11-29', '2016-12-01', 1),
    (2, '2016-12-01', '2016-12-02', 2),
    (3, '2016-12-02', '2016-12-02', 1),
    (4, '2016-12-02', '2016-12-02', 3);

Во-вторых, проверить перекрытие диапазонов дат можно проще:

select number from rooms
where id not in (
    select room from booking
    where start_date > '2016-11-28' and start_date < '2016-11-30'
) and type = 'lux'
order by number
limit 1;

Вернёт первый по порядку номер не забронированный ...

Comments

[del993788]

ну нормализация - понятие относительное. в booking засовывать room 1,2,1,3? Зачем? Я потом делаю из бд выборку, и вместо 301 номера я получаю 1. Не кажется это странным? Испокон веков 301 означало: третий этаж, первый номер/кабинет. А тут 1. Весело)
Самое смешное, что у меня не получилось вашу нормализацию привести в мой нормальный вид)

[Сергей Горностаев]

qqignatqq: нормализация - понятие вполне конкретное. Джоины для того и существуют, чтобы при выборке получать не внешний ключ, а его значение.

[del993788]

Сергей Горностаев: пользуясь случаем, хочу спросить, как вам такое решение?

SET @start = '2016-11-28';
SET @end = '2016-11-30';
SELECT a.nomer
FROM allnomer a
LEFT JOIN main m
  ON a.nomer = m.numbernomer
  AND DATEDIFF(m.datestart, @end) * DATEDIFF(m.dateend, @start) <= 0
WHERE a.type = 'lux' AND m.numbernomer IS NULL
LIMIT 1

[Сергей Горностаев]

qqignatqq: Вот вам вариант для вашей структуры

select a.nomer from allnomer as a
left join main as m
on m.numbernomer = a.nomer and (m.datestart > '2016-11-28' and m.datestart < '2016-11-30')
where m.id is null and a.type = 'lux'
order by a.number
limit 1;

[Сергей Горностаев]

qqignatqq: думаю, что такое решение аналогично моему, но работать будет не в каждой СУБД.

[del993788]

Сергей Горностаев: Я извиняюсь, но разве должен придти ответ 301? Он на все запросы 301 отвечает sqlfiddle.com/#!9/344e8/12

[Сергей Горностаев]

qqignatqq: надо условие задать строже и проверку на пересечение диапазонов инвертировать sqlfiddle.com/#!9/363451/11 Поэтому я и люблю варианты с подзапросами, они интуитивно понятнее.