Модель базы данных EAV, список записей в соответствии с поиском

Question

Я строю динамическое приложение. У меня есть три таблицы: (стиль модели EAV)

1. Предметы ( ItemId, ItemName)
2. Поля (FieldId, FieldName)
3. Значения полей ( ItemID, FieldId, Value)

Можете ли вы сказать мне, как написать один запрос, чтобы получить стартовые 20 записей из всех элементов, где FieldId=4 равно TRUE.

Ожидаемый результат:

Columns =>  ItemID | Name  | Field1 | Field2 |  Field3  
Each Row=>  ItemId | ItemName| Value1 | Value2 | Value3

Важные проблемы:

1. Количество полей на единицу неизвестно
2. Мне нужен один, чтобы написать один запрос.
3. Запрос будет работать на 100K записей, поэтому производительность беспокоит.
4. Я использую MySQL 5.0, поэтому нужно решение для MYSQL

Должен ли я денормализовать таблицы, если приведенный выше запрос вообще невозможен? Любой совет?

Answer 1

Дизайн EAV денормализован. То есть это нереляционный дизайн. Нет правила нормализации, которое привело бы вас к использованию дизайна EAV.

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

SELECT i.ItemID, i.ItemName, f.FieldName, v.Value
FROM Items i
JOIN FieldsValues v4 ON (v4.ItemID, v4.FieldID, v4.Value) = (i.ItemID, 4, TRUE)
JOIN FieldsValues v ON i.ItemID = v.ItemID
JOIN Fields f ON v.FieldID = f.FieldID;

Вы должны обработать строки в вашем приложении. Например, с помощью PHP:

<?php

$pdo = new PDO(...);
$sql = "...above query...";

$collection = array();

foreach ($pdo->query($sql) as $row) {
  $id = $row["ItemID"];
  if (!array_key_exists($id, $collection)) {
    $collection[$id] = new stdClass();
    $collection[$id]->Name = $row["ItemName"];
  }
  $collection[$id]->$row["FieldName"] = $row["Value"];
}

Теперь у вас есть массив объектов, и каждый объект соответствует элементу из базы данных. Каждый объект имеет свой соответствующий набор полей.