数据库EAV模型，按搜索记录列表 [英] Database EAV model, record listing as per search

问题描述

我正在构建一个动态应用程序. 我有三个表:( EAV模型样式)

I am building a dynamic application. I have three tables : ( EAV model style)

1. 项目(ItemId，ItemName)
2. 字段(FieldId，FieldName)
3. 字段值(ItemID，FieldId，Value)

您能告诉我如何编写SINGLE查询以从FieldId = 4等于TRUE的所有项中获取20条记录.

Can you tell me how to write SINGLE query to get starting 20 records from ALL items where FieldId=4 is equal to TRUE.

预期结果:

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

重要问题:

1. 每个项目的字段数未知
2. 我需要一个来编写一个查询.
3. 查询将在10万条记录上运行，因此需要关注性能.
4. 我正在使用MySQL 5.0，因此需要MYSQL解决方案

如果根本无法进行上述查询，我​​应该对表格进行非规范化吗?有什么建议吗?

Should I denormalize the tables if above query is not possible at all ? Any advice ?

推荐答案

EAV设计 已非规范化.也就是说，这是一个非关系设计.没有标准化的规则会导致您使用EAV设计.

The EAV design is denormalized. That is, it's a non-relational design. There is no rule of normalization that would lead you to use the EAV design.

SQL要求您在编写查询时知道列，并且结果集的每一行都具有相同的列.使用EAV，如果您不知道每个项目有多少个字段，那么唯一的解决方案是将它们作为行而不是列取回.

SQL requires that you know the columns when you write the query, and also that every row of the result set has the same columns. With EAV, the only solution if you don't know how many fields per item is to fetch them back as rows, not columns.

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:

You have to process the rows in your application. For instance, with 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"];
}

现在您有了一个对象数组，每个对象对应于数据库中的一个项目.每个对象都有其各自的字段集.

Now you have an array of objects, and each object corresponds to an item from the database. Each object has its own respective set of fields.

这篇关于数据库EAV模型，按搜索记录列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！