MySQL: запрос всех записей, имеющих данный набор отношений, к другой таблице

0

Да, своего рода немой титул, но мне было трудно описать мою проблему. У меня есть следующие таблицы:

Свойства

id | name
1  | color
2  | material

Функции

id | property_id | name
1  | 1           | yellow
2  | 1           | blue
3  | 2           | wood
4  | 2           | stone

вещества

id | name
1  | orange juice
2  | cheese

отношения

id | substance_id | option_id
1  | 2            | 1
2  | 2            | 3
3  | 1            | 1

Теперь у меня есть список вариантов и вы хотите знать, какие вещества связаны со всеми этими вариантами. (Например, какие вещества желтые и сделаны из дерева?) Возможно ли это с одним запросом?

Я пытаюсь сделать это в Rails.

  • 1
    Ваш сыр сделан из желтого дерева?
  • 0
    @Paul, я думаю, что это называется "holzkäse". Вкусные и с высоким содержанием клетчатки. :П
Показать ещё 1 комментарий
Теги:
relational-database

3 ответа

0
Лучший ответ
(SELECT s.name FROM substances s, relations r, options o
WHERE r.substance_id = s.id and r.option_id = o.id and o.name='yellow')
INTERSECT
(SELECT s.name FROM substances s, relations r, options o
WHERE r.substance_id = s.id and r.option_id = o.id and o.name='wood')

или

SELECT s.name FROM substances s
WHERE exists(SELECT * from relations r, options o 
      WHERE r.substance_id = s.id and r.option_id = o.id and o.name='yellow')
AND   exists(SELECT * from relations r, options o 
      WHERE r.substance_id = s.id and r.option_id = o.id and o.name='wood')

Наличие одной таблицы options с property_id, чтобы рассказать обо всех вариантах, не такая хорошая идея и действительно делает это намного сложнее. Я предлагаю разбить различные варианты в разные таблицы.

MATERIALS(id,name)
COLORS(id,name)

и используя отдельные отношения для каждого типа таблицы. В этом случае вам не нужны отдельные таблицы для каждого отношения, так как кажется, что это много (вещество) к одному (цветному) отношению.

SUBSTANCES(id, name, material_id, color_id)

Тогда ваш запрос намного проще

SELECT s.name FROM substances s, materials m, colors c
WHERE s.color_id = c.id AND m.material_id = m.id
AND m.name = 'wood'
AND c.name = 'yellow'

ActiveRecord должен легко иметь возможность обрабатывать этот последний запрос намного легче, чем первые два.

Поделиться
  • 0
    В моем приложении для реального мира это взаимосвязь между многими динамическими свойствами и параметрами. Я хотел бы, чтобы это было так просто. ;-)
  • 0
    Я использовал ваше решение с существующими пунктами, спасибо!
0

Просто со своей головы вы можете попробовать:

SELECT DISTINCT s.name FROM substances s, relation r 
WHERE r.substance_id = s.id 
      AND r.option_id IN ( 1, 3)
Поделиться
  • 0
    Разве это не выбирает все вещества желтого цвета ИЛИ из дерева?
0 
SELECT s.name
FROM   substances AS s
       LEFT JOIN relations AS r1
         ON s.id = r1.substance_id
       INNER JOIN relations AS r2
         ON r1.substance_id = r2.substance_id
            AND r1.option_id < r2.option_id
       LEFT JOIN options AS o1
         ON o1.id = r1.option_id
       LEFT JOIN options AS o2
         ON o2.id = r2.option_id
WHERE  o1.name = 'yellow'
       AND o2.name = 'wood'

Я не очень осведомлен об оптимизации SQL для производительности. Возможно, вам захочется сопоставить вышеизложенное с этим (ниже) и хорошо, как и другие решения, размещенные здесь.

SELECT s.name
FROM   substances AS s
       LEFT JOIN relations AS r1
         ON s.id = r1.substance_id
       INNER JOIN relations AS r2
         ON r1.substance_id = r2.substance_id
            AND r1.option_id < r2.option_id
       LEFT JOIN options AS o1
         ON o1.id = r1.option_id
     AND o1.name = 'wood'
       LEFT JOIN options AS o2
         ON o2.id = r2.option_id
     AND o2.name = 'yellow'
Поделиться
  • 0
    Эти утверждения становятся очень сложными, когда я ищу 3 или более вариантов.

Ещё вопросы

Сообщество Overcoder
Наверх
Меню