Xpoint
   [напомнить пароль]

Help с выборкой: 3 табл. + many-to-many

Метки: [без меток]
2006-08-08 14:46:58 [обр] Дмитрий[досье]

Суть вроде проста. Есть 3 таблицы: таблица номеров (rooms), таблица возрастных категорий (ages) и таблица связки номеров и возр. категорий (rooms_ages) - то есть, какие возрастные категории могут размещаться в номерах.

Таблица rooms:

+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| room_id      | int(11)               |      | PRI | NULL    | auto_increment |
| name         | varchar(255)          |      |     |         |                |
+--------------+-----------------------+------+-----+---------+----------------+

Таблица ages (здесь age_start, age_end - возраст от и до):

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| age_id    | int(11)     |      | PRI | NULL    | auto_increment |
| age_start | tinyint(4)  |      |     | 0       |                |
| age_end   | tinyint(4)  |      |     | 0       |                |
+-----------+-------------+------+-----+---------+----------------+

И таблица связки rooms_ages:

+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| room_id  | int(11) |      | MUL | 0       |       |
| age_id   | int(11) |      | MUL | 0       |       |
+----------+---------+------+-----+---------+-------+

На входе имеем массив возрастов (количество неизвестно), на пальцах пусть 16 и 25 лет.
Нужно отобрать все комнаты, которые могут принять этих двух поселенцев.

Полдня попыток приводили к каким-то запросам, но не совсем верным. На чем остановился:

SELECT r.*, COUNT(*) as total
FROM rooms r, ages a1, ages a2, rooms_ages ra1, rooms_ages ra2

WHERE
(
     r.room_id = ra1.room_id
     AND ra1.age_id = a1.age_id
     AND (
          a1.age_start <=16
          AND a1.age_end >=16
          )
)

AND

(
     r.room_id = ra2.room_id
     AND ra2.age_id = a2.age_id
     AND (
          a2.age_start <=25
          AND a2.age_end >=25
          )
)

GROUP BY room_id

То есть кол-во таблиц (aN, raN) == количеству поселенцев.

Среди результатов запроса есть правильные данные, но есть и другие, не подходящие пол логике.

Вобщем что-то не так, а что - не пойму.
Да, кстати выбрать сначала все age_id, а потом сделать что-то типа WHERE ... IN () не подходит.

Буду ОЧЕНЬ благодарен за помощь. Если нужны дампы - выложу.

спустя 11 минут [обр] Crizis[досье]
выложите, пожалуйста, дамп... а то создавать структуру лень немножко. запрос в голове сложился, но отладить не на чем... :)
спустя 14 минут [обр] Дмитрий[досье]
-- 
-- Структура таблицы `ages`
-- 

CREATE TABLE `ages` (
  `age_id` int(11) NOT NULL auto_increment,
  `age_start` tinyint(4) NOT NULL default '0',
  `age_end` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`age_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- 
-- Дамп данных таблицы `ages`
-- 

INSERT INTO `ages` VALUES (1, 2, 15);
INSERT INTO `ages` VALUES (2, 16, 30);

-- --------------------------------------------------------

-- 
-- Структура таблицы `rooms`
-- 

CREATE TABLE `rooms` (
  `room_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`room_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- 
-- Дамп данных таблицы `rooms`
-- 

INSERT INTO `rooms` VALUES (1, 'deluxe');
INSERT INTO `rooms` VALUES (2, 'bungalo');

-- --------------------------------------------------------

-- 
-- Структура таблицы `rooms_ages`
-- 

CREATE TABLE `rooms_ages` (
  `room_id` int(11) NOT NULL default '0',
  `age_id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Дамп данных таблицы `rooms_ages`
-- 

INSERT INTO `rooms_ages` VALUES (1, 1);
INSERT INTO `rooms_ages` VALUES (1, 2);
INSERT INTO `rooms_ages` VALUES (2, 1);
спустя 7 минут [обр] Crizis[досье]
такое, по идее, должно работать... хотя, данных бы побольше, а то вручную забивать тяжко... так или иначе, думаю, ход мысли ясен... :)
SELECT r.room_id,
       r.`name`
FROM `rooms` r
     INNER JOIN `rooms_ages` USING (room_id)
     INNER JOIN `ages` `a` USING (age_id)
WHERE `a`.`age_start` <= LEAST(16, 25) AND
      `a`.`age_end` >= GREATEST(16, 25)
GROUP BY room_id;
спустя 12 минут [обр] Дмитрий[досье]
Немного не то... Дело в том, что отношение между таблицами rooms и ages много-ко-многим.
Он вернет результаты, если двое наших поселенцев находятся в ОДНОЙ возрастной категории, если же нет (например 5 лет и 20 лет) - будет empty set.
спустя 5 минут [обр] Crizis[досье]
но ведь если нет комнаты, которая может поселить 5-тилетнего жильца и выведется не empty set, а набор комнат, которые смогут вместить всех остальных, разве это правильно? ведь получаемый набор комнат должен удовлетворять всем критериям из списка. или я не так понял условия выборки?
спустя 24 минуты [обр] Дмитрий[досье]

Нет, вы поняли задачу правильно. Смотрите, если сделать так:

SELECT r.room_id, r.`name` 
FROM `rooms` r
   INNER JOIN `rooms_ages` USING ( room_id ) 
   INNER JOIN `ages` `a` USING ( age_id ) 
WHERE `a`.`age_start` <= LEAST( 4, 25 ) 
AND `a`.`age_end` >= GREATEST( 4, 25 ) 
GROUP BY room_id

то даже при тех данных что я давал должна быть выбрана комната deluxe (room_id=1), так как есть записи:

room_id age_id
1         1
1         2

то есть она может селить как от 2 до 15, так и от 16 до 30. А в результате ряды не находятся.

спустя 41 минуту [обр] Crizis[досье]
Дмитрий[досье], пока что даже не могу представить, как эту задачу решить одним запросом, учитывая, что количество представителей возрастов, насколько я понимаю, является переменной > 0. может ещё кто чего-нибудь присоветует.
спустя 25 минут [обр] Дмитрий[досье]
 :) Я даже не знаю как обойтись более чем одним запросом. Как вариант это отобрать age_id, а потом сделать WHERE ... IN (), но меня такое не спасает, так как возрастные категории привязываются к отелям, и отобрав возрасты я не знаю, к какому отелю они относятся. то есть нужно одним запросом, который ориентируеться только на возрасты поселенцев. Тем не менее спасибо за ответы!
спустя 39 минут [обр] ola(0/1)[досье]
SELECT r.room_id, r.`name`, count(*) c
FROM `rooms` r
   INNER JOIN `rooms_ages` USING ( room_id ) 
   INNER JOIN `ages` `a` USING ( age_id ) 
WHERE `a`.`age_start` <= LEAST( @age1, @age2, ..., @ageN ) 
OR `a`.`age_end` >= GREATEST( @age1, @age2, ..., @ageN ) 
GROUP BY room_id
HAVING c=@N
где @age1, @age2, ..., @ageN — возрасты поселяемых, @N — количество поселяемых.
спустя 1 день 3 часа [обр] Дмитрий[досье]
Тот же самый результат. Может не совсем понятно из условия, но всех поселенцев мы должны поселить в ОДНУ комнату.
То есть нельзя одного поселить в одну, двух других в другую. все трое должны жить вместе :)
спустя 1 день 14 часов [обр] ola(0/1)[досье]

Должен получаться нужный результат. Проверьте еще раз.
Запрос

SELECT r.room_id, r.`name`, count(*) c
FROM `rooms` r
   INNER JOIN `rooms_ages` USING ( room_id ) 
   INNER JOIN `ages` `a` USING ( age_id ) 
WHERE `a`.`age_start` <= LEAST( 4, 25 ) 
OR `a`.`age_end` >= GREATEST( 4, 25 ) 
GROUP BY room_id
HAVING c=2

возвращает комнату deluxe

спустя 3 часа 16 минут [обр] Дмитрий[досье]

Мне кажется мы не можем отбирать возр. категории используя ОДИН экземпляр
таблицы ages. Каждое из этих двух неравенств не несет информации о верхней (нижней) границе.

Кстати, при возрастах 23 и 25 лет получаем обе комнаты, хотя должен быть вообще пустой результат.
[code]
WHERE `a`.`age_start` <= LEAST( 23, 25 )
OR `a`.`age_end` >= GREATEST( 23, 25 )
[/code]

Еще раз про условие задачи.
Ставится задача поиска номеров для размещения пришедших в отель поселенцев.
Вся група этих чуваков (кол-во неизвестно, будем оперировать хотя-бы двумя -
16 и 25 лет) хочет жить в ОДНОМ номере, то есть уговорить их расселиться
нельзя. В отеле есть комнаты, каждая из которых имеет свой набор возрастных
промежутков. И селить комната может ТОЛЬКО этот набор. Например, (одного
человека от 16 до 30 лет и двух от 40 до 60) => 3 человека и никак иначе.
Даже если придет богатый дядька и захочет жить в трех-местном номере, ему
будет отказана эта комната.
Структура таблиц составлена вроде логично. Но я не отрицаю ее
частичное/полное изменение, добавление новых таблиц и т.д., главное чтобы
решить задачу.

На нашем примере:
Комната 1 может принять ТОЛЬКО => (одного от 2 до 15 лет и одного от 16 до 30 лет).
Надо учитывать, что записи в таблице room_ages определяют ТОЧНУЮ комбинацию
количества человек и их возрастов. То есть комната 1 не может селить одного
поселенца от 2 до 15, нужен еще один от 16 до 30.

спустя 6 часов [обр] GRAy(8/259)[досье]

Что-то мне в вашей схеме не нравится... пока интуитивно.
Я так понимаю в таблице room_ages возможные комбинации возрастных групп проживающих для комнат определяются наличием соотв. связей. Предположим есть 2-х местный номер в который можно поселить либо двух человек из группы 20-50 лет, либо одного из группы 16-30 и одного из группы 2-15. В вашей схеме для отображения этого варианта потребуется (минимум) три записи:
комн.1 - возр.(16-30)
комн.1 - возр.(16-30)
комн.1 - возр.(2-15)
Как теперь понять что это 2-х местный номер? По всей видимости вы исходите из предположения что вариантов "либо либо" не существует - имхо достаточно искуственное ограничение. Ну это не важно.
Давайте попробуем последовательно подойти к задаче.
Пришли к нам в отель 2 человека, 16 и 25 лет соответственно. Мы поискали в нашей базе все комнаты в которых каждый из них потенциально мог бы жить и получили два множества комнат, сформировать их картезианово произведение (т.е. все возможные сочетания) и отфильтровать полученное по трём критериям:

  1. Оба человека должны размещаться в одной комнате (номер комнаты из первого множества = номер комнаты из второго).
  2. Общее кол-во привязанных к этой комнате возрастных групп равно количеству пришедших людей и соответсвует их возрастным группам.

Итого (сорри, но я не стану тут приводить всю последовательность преобразований, которая привела к такому селекту ;) ):

select room_id , count(*), count(a.age_id)
from rooms_ages ra left outer join (
  ages a join (<выборка из таблицы с возрастами потенциальных заселенцев тут есть колонка age>) r
  on (a.age_end > r.age and r.age >= a.age_start)
) on (ra.age_id = a.age_id)
group by ra.room_id
having count(*) = 2 /* здесь мы убеждаемся что всего к комнате привязано именно две возрастных группы, чтобы случайно не заселить в бОльшую комнату меньшее кол-во человек */ 
  and count(a.age_id)=2 /* а здесь мы проверяем, что оба человека присутствуют в выборке */

Селект этот подразумевает что человек никогда не принадлежит одновременно к двум возрастным группам (т.е. группы не накладываются по диапазонам).

Powered by POEM™ Engine Copyright © 2002-2005