Ответ 1

Следующий код должен дать вам то, что вы ищете, однако вы должны заметить, что в вашей таблице в настоящее время есть томатный, который называется желтым и овощным. Очевидно, что вы хотите, чтобы помидор был красным, а томатный на самом деле фруктом, а не овощем.

Select distinct title 
from products p
inner join
product_variants pv on pv.product_id = p.id
inner join
product_variant_has_properties pvp on pvp.variant_id = pv.id
inner join
product_has_properties php on php.product_id = p.id
inner join
properties ps1 on ps1.id = pvp.property_id --Color
inner join
properties ps2 on ps2.id = php.property_id --Type
inner join
property_keys pk on pk.id = ps1.property_key_id or pk.id = ps2.property_key_id

where ps1.value = 'Red' and ps2.value = 'Vegetable'

Вот скрипт SQL: http://www.sqlfiddle.com/#!9/309ad/3/0

Ответ 2

Это сложный ответ, и это возможно сделать это гораздо проще. Однако, учитывая, что вы, похоже, хотите иметь возможность запрашивать color = xx и type = xx, нам явно нужно иметь столбцы с этими именами, которые, как вы указали, означают, что нам нужно поворачивать данные.

Кроме того, поскольку мы хотим получить все комбинации цветов и типов для каждого продукта, нам нужно выполнить какое-то перекрестное объединение, чтобы объединить их.

Это приводит нас к запросу - сначала мы получаем все типы для продукта и его вариантов, затем присоединяем его ко всем цветам для продукта и его варианта. Мы используем union для комбинирования свойств продукта и варианта, чтобы держать их в одном столбце, а не для проверки нескольких столбцов.

Конечно, все продукты могут не иметь указанной информации, поэтому мы используем left joins полностью. Если гарантируется, что продукт всегда будет иметь по крайней мере один цвет и по крайней мере один тип - все они могут быть изменены на inner joins.

Кроме того, в вашем примере вы говорите, что tomato должен иметь цвет red, но в данных примера, которые вы предоставляете, я уверен, что tomato имеет цвет yellow.

В любом случае, здесь запрос:

select distinct title from 
(select q1.title, q1.value as color, q2.value as type from
(
select products.id, products.title, properties.value, properties.property_key_id
  from products
    left join product_has_properties
      on products.id = product_has_properties.product_id 
    left join properties
      on properties.id = product_has_properties.property_id and properties.property_key_id = 1
union
select product_variants.product_id, products.title, properties.value, properties.property_key_id
  from product_variants
    inner join products
      on product_variants.product_id = products.id
    left join product_variant_has_properties
      on product_variants.id = product_variant_has_properties.variant_id
    left join properties
      on properties.id = product_variant_has_properties.property_id and properties.property_key_id = 1
) q1
left join
(
select products.id,  products.title, properties.value, properties.property_key_id
  from products
    left join product_has_properties
      on products.id = product_has_properties.product_id
    left join properties
      on properties.id = product_has_properties.property_id  and properties.property_key_id = 2
union
select product_variants.product_id, products.title, properties.value, properties.property_key_id
  from product_variants
    inner join products
      on product_variants.product_id = products.id
    left join product_variant_has_properties
      on product_variants.id = product_variant_has_properties.variant_id
    left join properties
      on properties.id = product_variant_has_properties.property_id  and properties.property_key_id = 2
) q2
on q1.id = q2.id 
where q1.value is not null or q2.value is not null
) main
where ((color = 'red' or color = 'yellow') and type = 'fruit')

И вот демо: http://sqlfiddle.com/#!9/d3ded/76

Если вам нужно получить больше типов свойств, в дополнение к цвету и типу, запрос нужно будет изменить - извините, но это в значительной степени то, за что вы застряли, пытаясь свернуть в mysql

Ответ 3

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

Лучшее решение - рассмотреть более простой подход. Попытайтесь сгладить структуру данных, чтобы у вас не было таких зависимостей.

Я не знаю, что именно означают product_variants, поэтому я не могу точно сказать, как это сделать.

Но основная идея - сохранить свойства всегда для каждого варианта. Если у вас есть только один вариант - определите его как вариант.

И я предлагаю вам заставить таблицу свойств ссылаться на точный вариант вместо глобальной нумерации со ссылками на таблицы в структуре:

+----+-----------------+-------------+-----------+
| id | property_key_id |   variant_id|   value   |
+----+-----------------+-------------+-----------+
|  1 |               1 |           1 | Yellow    |
|  2 |               1 |           1 | Green     |
|  3 |               1 |           1 | Red       |
|  4 |               2 |           1 | Fruit     |
|  5 |               2 |           2 | Vegetable |
|  6 |               1 |           2 | Blue      |
|  7 |               1 |           2 | Yellow    |
+----+-----------------+-------------+-----------+

Если этот подход - у вас будут повторяющиеся значения, но все ваши запросы будут проще, и у вас будет свобода сохранять значения, которые вы хотите для каждого конкретного варианта продукта.

ОБНОВЛЕНИЕ

Если у вас нет возможности изменить структуру данных, "LEFT OUTER JOIN" - ваша единственная надежда.

Проверьте приведенный ниже запрос, который выбирает цвет с цветом "Желтый"

    select p.* from products p 
       left outer join product_has_properties pp 
            on p.id=pp.product_id 
       left outer join product_variants v 
            on p.id=v.product_id 
       left outer join product_variant_has_properties vp 
            on v.id = vp.variant_id 
    where vp.property_id=1 or pp.property_id=1;

Ответ 4

Учитывая продукты, а не варианты, вы можете имитировать это (по крайней мере до некоторой степени) с помощью объединений, чтобы вы

• замените каждый OR в вашем запросе эквивалентным условием в предложении WHERE. Например. иметь (color='red' OR color='yellow'),

  SELECT product_id FROM product_has_properties
  WHERE property_id IN (1, 3)
  

• замените каждый AND в своем запросе самосоединением и условием в предложении WHERE. Это должно давать строки, соответствующие продуктам, которые имеют пару рассматриваемых свойств. Например, (color='red' AND type='vegetable'),

  SELECT p1.product_id
  FROM product_has_properties p1
  INNER JOIN product_has_properties p2 ON (p1.product_id = p2.product_id)
  WHERE p1.property_id = 3 AND p2.property_id = 5
  

Очевидно, что это осложняется по мере роста числа условий. Чтобы получить ((color='red' OR color='yellow') AND type='fruit'), вам нужно будет сделать

SELECT p1.product_id
FROM product_has_properties p1
INNER JOIN product_has_properties p2 ON (p1.product_id = p2.product_id)
WHERE (p1.property_id = 1 OR p1.property_id = 3) AND p2.property_id = 4

Предполагая, что некоторые фрукты могут быть как синими, так и красными, чтобы получить pkey1='red' AND pkey1='blue' AND pkey2='fruit', вам нужно будет сделать

SELECT p1.product_id
FROM product_has_properties p1
INNER JOIN product_has_properties p2 ON (p1.product_id = p2.product_id)
INNER JOIN product_has_properties p3 ON (p1.product_id = p3.product_id)
WHERE p1.property_id = 3 AND p2.property_id = 6 AND p3.property_id = 4

Однако может быть какой-то случай, который не подпадает под этот подход.

Ответ 5

Короткий ответ

Я собираюсь выкинуть немного другой ответ на те, которые вы получили. Хотя для этого вполне возможно иметь чисто SQL-ответ, вопрос, который я вам поставил бы: Почему?

Этот ответ определит ваш следующий шаг.

Если ваш ответ состоит в том, чтобы попытаться выучить чистый SQL-способ сделать это, здесь есть несколько отличных ответов, которые помогут вам больше всего, если не так, как есть.

Если ваш ответ заключается в создании масштабируемых динамических запросов для конечного приложения, вы можете обнаружить, что ваша работа упрощена, опираясь на ваш язык программирования.

Немного личный фон

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

Я попытался создать чистые SQL-решения, которые работали для конкретных случаев использования, но требовали обширной настройки для каждого дополнительного варианта использования. Когда я попытался масштабировать запросы, я сначала попытался создать Хранимые процедуры. Это был кошмар и довольно рано в моем развитии, я понял, что это будет головная боль для поддержания.

Я продолжил использовать PHP и создал свой собственный запрос. Хотя некоторые из этого кода превратились во что-то, что сегодня очень полезно для меня, я узнал, что большая часть его будет сложной задачей, если я не создам библиотеки служб. В этот момент я понял, что в основном собираюсь создать объектно-реляционный Mapper (ORM). Если мое приложение не было SO, а SO уникальным, что ORM на рынке не приблизился бы к тому, что я хотел, мне нужно было воспользоваться этой возможностью, чтобы изучить использование ORM для моего приложения. Несмотря на мои первоначальные оговорки, которые заставляли меня делать все, но посмотрите на ORM, я начал использовать его, и это помогло значительно увеличить скорость разработки.

Достижение желаемого конечного результата

Select all products with (color='red' AND type='vegetable'). This should return only Tomato.
Select all products with ((color='red' OR color='yellow') AND type='fruit') should return Apple and Banana

Это возможно в ORM. То, что вы описываете, только слабо определено в вашем SQL, но на самом деле прекрасно суммируется в ООП. Это то, что было бы похоже на PHP, как пример.

<?
Abtract class AbstractProductType {
    public function __construct() {

    }
}

class Color extends AbstractProductType {

}

class Yellow extends Color {

}

class Red extends Color {

}

class Type extends AbstractProductType {

}

class Vegetable extends Type {

}

class Fruit extends Type {

}

class Product {
     public function setColor(Color $color) {
         //
     }
     public function setType(Type $type) {
         //
     }
}

$product = new Product();
$product->setColor(new Red());
$product->setType(new Fruit());
$result = $product->find();
?>

Идея заключается в том, что вы можете в полной мере использовать SQL в объектно-ориентированном программировании.

Немного более низкая версия этого будет заключаться в создании класса, который генерирует фрагменты SQL. Мой личный опыт заключался в том, что большая работа для ограниченной окупаемости. Если ваш проект будет оставаться относительно небольшим, он может работать нормально. Тем не менее, если вы проявите склонность к тому, что ваш проект будет расти, ORM, возможно, стоит изучить.

Заключение

Хотя я не уверен, какой язык вы будете использовать для запроса и управления вашими данными, есть отличные ORM, которые не должны быть уценены. Несмотря на их много минусов (вы можете найти много споров об этом по всему Интернету), я неохотно верю, что, хотя это, безусловно, не идеально для всех ситуаций, их следует рассматривать для некоторых. Если это не одна из этих ситуаций для вас, будьте готовы написать много JOIN самостоятельно. Когда вы ссылаетесь на таблицу n раз и требуете ссылки обратно в таблицу, единственный способ, с помощью которого я могу добавить ссылку, - создать n JOIN s.

Мне будет очень интересно узнать, есть ли лучший способ, конечно!

Ответ 6

Условная агрегирование

Вы можете использовать условную агрегацию в своем предложении having, чтобы узнать, есть ли у продукта определенные свойства. Например, для запроса всех продуктов, имеющих свойства типа "растительный" и "красный".

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

select p.id, pv.id from products p
left join product_has_properties php on php.product_id = p.id
left join properties pr on pr.id = php.property_id
left join property_keys pk on pk.id = pr.property_key_id
left join product_variants pv on pv.product_id = p.id
left join product_variant_has_properties pvhp on pvhp.variant_id = pv.id
left join properties pr2 on pr2.id = pvhp.property_id
left join property_keys pk2 on pk2.id = pr2.property_id
group by p.id, pv.id
having (
  count(case when pk.value = 'Color' and pr.value = 'Red' then 1 end) > 0
  and count(case when pk.value = 'Type' and pr.value = 'Vegetable' then 1 end) > 0
) or (
  count(case when pk2.value = 'Color' and pr2.value = 'Red' then 1 end) > 0
  and count(case when pk2.value = 'Type' and pr2.value = 'Vegetable' then 1 end) > 0
)

Ответ 7

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

Трудность, с которой OP сталкивается с написанием SQL в отношении таблиц, указанных в "вопросе", связана с (как я называю) "несоответствием импеданса" между "реляционной" моделью и "значением атрибута объекта", (EAV).

SQL предназначен для работы с моделью "Реляционная". Каждый экземпляр объекта представляется в виде кортежа, который хранит строку в таблице. Атрибуты объекта хранятся как значения в столбцах строки сущности.

Модель EAV существенно отличается от реляционной модели. Он перемещает значения атрибутов из строки сущности и перемещает их в несколько отдельных строк в других таблицах. И это затрудняет сложность написания запросов, если запросы пытаются эмулировать запросы к "реляционной" модели, преобразуя данные из представления "EAV" обратно в представление "Relational".

Существует несколько подходов к написанию SQL-запросов к модели EAV, которые эмулируют результаты, возвращаемые реляционной моделью (как показано в примере SQL, представленном в других ответах на этот "вопрос".

Один из подходов заключается в использовании подзапросов в списке SELECT для возврата значений атрибутов в виде столбцов в строке сущности.

Другим подходом является выполнение объединений между строкой в ​​таблице сущностей в строках в таблице атрибутов и использование GROUP BY для свертывания строк, а в списке SELECT - условные выражения "выберите" значение, которое будет возвращено для столбца.

Есть много примеров обоих этих подходов. И ни то, ни другое не лучше, чем другое, пригодность каждого подхода действительно зависит от конкретного варианта использования.

Несмотря на то, что можно писать SQL-запросы по показанным таблицам стиля EAV, эти запросы на порядок сложнее, чем эквивалентные запросы к данным, хранящимся в "реляционной" модели.

Результат, возвращаемый тривиальным запросом в реляционной модели, например

SELECT p.id
  FROM product p
 WHERE p.color = 'red'

Чтобы вернуть тот же самый набор из данных в модели EAV, требуется гораздо более сложный SQL-запрос, включающий объединение нескольких таблиц и/или подзапросов.

И как только мы выходим за тривиальный запрос, к запросу, в котором мы хотим вернуть атрибуты из нескольких связанных объектов... в качестве простого примера, возвращаем информацию о заказах за последние 30 дней для продуктов, которые были "красными"

SELECT c.customer_name
     , c.address
     , o.order_date
     , p.product_name
     , l.qty
  FROM customer c
  JOIN order o ON ...
  JOIN line_item l ON ...
  JOIN product p ON ...
 WHERE p.color = 'red'
   AND o.order_date >= DATE(NOW()) + INTERVAL 30

получение того же результата, используя SQL, из модели EAV является более запутанным и запутанным и может быть мучительным упражнением в расстройстве.

Конечно, возможно написать SQL. И как только нам удастся получить инструкции SQL, которые работают, чтобы вернуть "правильный" набор результатов, когда количество строк в таблицах выходит за рамки тривиальной демонстрации, вплоть до того объема, который мы ожидаем от баз данных... производительность из этих запросов является ужасающим (по сравнению с запросами, возвращающими те же результаты из традиционной реляционной модели).

(И мы даже не затронули дополнительную сложность только для добавления и обновления атрибутов сущностей, обеспечения ссылочной целостности между объектами и т.д.)

Но почему мы хотим это сделать? Почему нам нужно (или нужно) писать инструкции SQL в отношении таблиц модели EAV, которые эмулируют результаты, возвращаемые запросами, в таблицы реляционных моделей?

В нижней строке, если мы собираемся использовать модель EAV, нам намного лучше не пытаться использовать один оператор SQL для возврата результатов, как мы бы вернулись из запроса модели Relational.

Проблема получения информации из модели EAV гораздо более подходит для языка программирования, который является объектно-ориентированным, и обеспечивает структуру. Что-то, что полностью шнурует в SQL.