Блог

Я упрощаю свой фактический случай, чтобы быстрее донести суть, просто в качестве предупреждения, если это кажется бессмысленным.

У меня есть таблица с именем Candy_Central. Candy_Central содержит все идентификаторы для my candy, цену за фунт, а также пару других важных фрагментов информации, которые одинаковы во всех записях Candy. В нем также есть поле с именем Candy_Type. Значение Candy_Type может быть шоколадным, твердым, липким или жидким.

У меня есть другой набор таблиц, которые используются для информации о candy, но их использование определяется типом candy. Например, если мне нужна остальная информация о моей плитке шоколада prime, мне нужно перейти к Chocolate_Standard, чтобы получить остальную информацию. Если я ищу липких червей с сахаром снаружи, мне нужно перейти к Gummy_Standard . Причина в том, что поля для одного набора информации полностью отличаются от полей для другого.

У меня есть еще один слой информации, цвета. У меня есть конфеты синего цвета, у меня есть конфеты черного цвета, и у меня есть конфеты обоих цветов. То, как я в настоящее время храню эту информацию, у меня есть таблица с полями Candy_Id и Color . Чтобы найти все цвета, я обычно просто выполняю запрос к этой таблице, используя поле color_id, чтобы найти интересующую меня конфету. Но SQL, конечно, предназначен для поиска всех ваших результатов. Поэтому, если бы я хотел найти всю необходимую информацию, я бы хотел выполнить какое-либо объединение или объединение. Я не очень хорошо знаком с этим типом запросов, поэтому я не уверен, как получить нужную мне таблицу результатов, которая в основном будет [извините за псевдопрос] (элементы из Candy_Main как CM … элементы из Gummy как g … элементы из color как c … где cm.candy_id = g.candy_id и cm.candy_id = c.candy_id ). Если бы у gummy не было цветов, я бы просто хотел null . Я в порядке с получением нескольких строк обратно для каждого candy_id, но я бы хотел, чтобы это было как можно более ограниченным, чтобы постобработка (превращение нескольких строк candy в один объект в моем приложении) занимала как можно меньше времени.

Таблицы:

 Candy_Main
    candy_id (primary key, auto increment, not null, etc)
    candy_name (varchar)
    candy_inventor_id (a primary key from some other table, but this is needed for the where clause.
    candy_type (varchar)

Gummy_Standard
    gummy_id (primary, auto)
    candy_id (should match to candy_main)
    softness (varchar)
    outer_coating_type (varchar)
    animal_likeness (varchar)

Gummy_Colors
    gummy_color_id (primary, auto)
    gummy_id (int)
    candy_id (int)
    color (varchar(64))

Gummy_Ingredients
    gummy_ingredient_id (primary, auto)
    gummy_id (int)
    candy_id (int)
    ingredient (varchar(64))
  

Теперь я хочу выполнить запрос для всех записей where candy_inventor_id = 5 AND candy_type =
'gummy' . В конце запроса я бы хотел candy_name, candy_type, softness, outer_coating_type, animal_likeness, colors, and ingredients .

Цвет и ингредиенты — единственные объекты, которые не относятся к 1 к 1 в этом запросе, поэтому я бы хотел, чтобы было столько записей, сколько цветов и ингредиентов. Спасибо за помощь, если это возможно. У меня такое чувство, что было бы достаточно легко ограничить это только цветами или ингредиентами, но сделать это с обоими будет слишком сложно.

попробуйте это

 SELECT c.candy_name, c.candy_type, s.softness, s.outer_coating_type, 
      s.animal_likeness, 
      GROUP_CONCAT(gc.color), 
      GROUP_CONCAT(gi.ingredient)
FROM candy_main c
LEFT JOIN gummy_standard s on s.candy_id=c.candy_id
LEFT JOIN gummy_colors gc on gc.candy_id = c.candy_id
LEFT JOIN gummy_ingredients gi cl on cl.candy_id = c.candy_id
WHERE c.candy_inventor_id=5 AND c.candy_type='gummy'
GROUP BY gc.color , gi.ingredient
  

Если я правильно понимаю вашу настройку БД, для каждой конфеты существует множество потенциальных gummy_standards (так что жесткий медведь и шоколадный медведь могут быть двумя стандартными записями gummy, связанными с одной записью candy). В этом случае должно работать следующее:

 SELECT cm.candy_name, cm.candy_type, gs.softness, gs.outer_coating_type, gs.animal_likeness, gc.color, gi.ingredient
FROM Candy_Main cm
    INNER JOIN Gummy_Standard gs ON gs.candy_id = cm.candy_id
    INNER JOIN Gummy_Colors gc ON gc.candy_id = gs.gummy_id
    INNER JOIN Gummy_Ingredients gi ON gi.gummy_id = gs.gummy_id
WHERE candy_inventor_id = 1  -- Of course, this 1 be replaced with the actual inventor's ID
  

Имейте в виду, что подобное выполнение приводит к перекрестному умножению дубликатов, поэтому, если у мармелада три цвета и 4 ингредиента, каждый ингредиент повторяется для всех трех цветов (таким образом, 12 строк только для одного мармелада). Если вы этого не хотите, попробуйте объединить строки в одну строку, а затем разделить строку в коде. Не знаю, что это за функция в MySQL, но если concat_ws() работает так же, как textcat_all() в PostgreSQL, то должно сработать следующее:

 SELECT cm.candy_name, cm.candy_type, gs.softness, gs.outer_coating_type, gs.animal_likeness, CONCAT_WS(', ', gc.color), CONCAT_WS(', ', gi.ingredient)
FROM Candy_Main cm
    INNER JOIN Gummy_Standard gs ON gs.candy_id = cm.candy_id
    INNER JOIN Gummy_Colors gc ON gc.candy_id = gs.gummy_id
    INNER JOIN Gummy_Ingredients gi ON gi.gummy_id = gs.gummy_id
WHERE candy_inventor_id = 1  -- Of course, this 1 be replaced with the actual inventor's ID
GROUP BY cm.candy_name, cm.candy_type, gs.softness, gs.outer_coating_type, gs.animal_likeness
  

Два совета по архитектуре:
1. Если я правильно понимаю ваши таблицы, а ингредиенты и цвета всегда применяются к стандартной записи gummy, то поле candy_id в Colors и Ingredients является избыточным. Лучше просто подключаться к Candy через Gummy Standard каждый раз.
2. Вы могли бы извлечь выгоду из более высокой степени нормализации, т. Е. Создания таблицы цветов и таблицы ингредиентов, а затем ссылки на них, вместо того, чтобы включать строковое описание в свои таблицы как есть. Это упростит внесение изменений во все соответствующие записи, т. Е. Скажем, Вы хотите изменить название синего цвета на синий (темный), потому что вы добавляете синий (светлый), вам нужно обновить только одну запись, а не все цветные записи с надписью Blue (или blue,или даже blu или blou, поскольку орфографические ошибки часто возникают, когда вам приходится вводить одно и то же описание каждый раз). Таким образом, ваши таблицы будут выглядеть так:

 Color
    color_id (primary, auto)
    color (varchar(64))

Ingredient
    ingredient_id (primary, auto)
    ingredient (varchar(64))

Gummy_Colors  
    gummy_color_id (primary, auto)  
    gummy_id (int)  
    candy_id (int)  
    color_id (foreign key to Color)  

Gummy_Ingredients  
    gummy_ingredient_id (primary, auto)  
    gummy_id (int)  
    candy_id (int)  
    ingredient_id (foreign key to Ingredient)  
  

Надеюсь, это поможет.