EAV如果要查询多条件是需要链表的！
attribute_code 保存了属性名，在水平表中它就是指字段
backend_type 保存了属性的类型，在水平表中表示字段的类型，这个值是进入对应表查找对应值的依据。
public function showCollectionAction() {
$weblog2 = Mage::getModel('complexworld/eavblogpost');
$entries = $weblog2->getCollection()
->addAttributeToSelect('title')
->addAttributeToSelect('content')
->addAttributeToSelect('date');
$entries->load();
foreach($entries as $entry)
{
echo '<h2>' . $entry->getTitle() . '</h2>';
echo '<p>Date: ' . $entry->getDate() . '</p>';
echo '<p>' . $entry->getContent() . '</p>';
}
echo '</br>Done</br>';
}
对应以上代码，通过MySQL的日志记录，可以知道进行了如下的SQL查询：
SELECT `eav_entity_type`.* FROM `eav_entity_type` WHERE (`eav_entity_type`.`entity_type_code`='complexworld_eavblogpost')
SELECT `eav_attribute`.* FROM `eav_attribute` WHERE (`eav_attribute`.`attribute_code`='title') AND (entity_type_id = '31')
SELECT `eav_entity_type`.`additional_attribute_table` FROM `eav_entity_type` WHERE (entity_type_id = '31')
SELECT `eav_attribute`.* FROM `eav_attribute` WHERE (`eav_attribute`.`attribute_code`='content') AND (entity_type_id = '31')
SELECT `eav_entity_type`.`additional_attribute_table` FROM `eav_entity_type` WHERE (entity_type_id = '31')
SELECT `eav_attribute`.* FROM `eav_attribute` WHERE (`eav_attribute`.`attribute_code`='date') AND (entity_type_id = '31')
SELECT `eav_entity_type`.`additional_attribute_table` FROM `eav_entity_type` WHERE (entity_type_id = '31')
SELECT `e`.* FROM `eavblog_posts` AS `e` WHERE (`e`.`entity_type_id` = '31')
SELECT `eavblog_posts_varchar`.`entity_id`, `eavblog_posts_varchar`.`attribute_id`, `eavblog_posts_varchar`.`value` FROM `eavblog_posts_varchar` WHERE (entity_type_id =31) AND (entity_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) AND (attribute_id IN ('963')) UNION ALL SELECT `eavblog_posts_text`.`entity_id`, `eavblog_posts_text`.`attribute_id`, `eavblog_posts_text`.`value` FROM `eavblog_posts_text` WHERE (entity_type_id =31) AND (entity_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) AND (attribute_id IN ('964')) UNION ALL SELECT `eavblog_posts_datetime`.`entity_id`, `eavblog_posts_datetime`.`attribute_id`, `eavblog_posts_datetime`.`value` FROM `eavblog_posts_datetime` WHERE (entity_type_id =31) AND (entity_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) AND (attribute_id IN ('965'))
首先从eav_entity_type表中取出entity_type_code对应的entity_type_id，根据entity_type_id从eav_attribute取出对应的属性（title、content、date，每取出一个属性后还判断一下是否有额外的表保存了对应该属性的信息，是否有额外表保存信息是根据eav_entity_type表的additional_attribute_table给出的，比如catalog和customer的属性还使用catalog_eav_attribute和customer_eav_attribute表来保存额外的属性信息），取出的属性有一个标记其类型的字段backend_type，它用于后面构建联合查询时进入那个类型表的判断依据。然后从eavblog_posts中取出实体。这个时候实体ID知道了，实体的属性（字段）知道了，接下来要寻找每个实体的每个属性对应的值，Magento根据以前的查询，构建了如下的UNION ALL查询：
SELECT
`eavblog_posts_varchar`.`entity_id`,
`eavblog_posts_varchar`.`attribute_id`,
`eavblog_posts_varchar`.`value`
FROM
`eavblog_posts_varchar`
WHERE (entity_type_id = 31)
AND (
entity_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
)
AND (attribute_id IN ('963'))
UNION
ALL
SELECT
`eavblog_posts_text`.`entity_id`,
`eavblog_posts_text`.`attribute_id`,
`eavblog_posts_text`.`value`
FROM
`eavblog_posts_text`
WHERE (entity_type_id = 31)
AND (
entity_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
)
AND (attribute_id IN ('964'))
UNION
ALL
SELECT
`eavblog_posts_datetime`.`entity_id`,
`eavblog_posts_datetime`.`attribute_id`,
`eavblog_posts_datetime`.`value`
FROM
`eavblog_posts_datetime`
WHERE (entity_type_id = 31)
AND (
entity_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
)
AND (attribute_id IN ('965'))
其执行结果：
+-----------+--------------+------------------------+ | entity_id | attribute_id | value | +-----------+--------------+------------------------+ | 1 | 963 | This is a test 0 | | 2 | 963 | This is a test 1 | | 3 | 963 | This is a test 2 | | 4 | 963 | This is a test 3 | | 5 | 963 | This is a test 4 | | 6 | 963 | This is a test 5 | | 7 | 963 | This is a test 6 | | 8 | 963 | This is a test 7 | | 9 | 963 | This is a test 8 | | 10 | 963 | This is a test 9 | | 1 | 964 | This is test content 0 | | 2 | 964 | This is test content 1 | | 3 | 964 | This is test content 2 | | 4 | 964 | This is test content 3 | | 5 | 964 | This is test content 4 | | 6 | 964 | This is test content 5 | | 7 | 964 | This is test content 6 | | 8 | 964 | This is test content 7 | | 9 | 964 | This is test content 8 | | 10 | 964 | This is test content 9 | | 1 | 965 | 2013-03-14 15:09:36 | | 2 | 965 | 2013-03-14 15:09:36 | | 3 | 965 | 2013-03-14 15:09:36 | | 4 | 965 | 2013-03-14 15:09:36 | | 5 | 965 | 2013-03-14 15:09:36 | | 6 | 965 | 2013-03-14 15:09:36 | | 7 | 965 | 2013-03-14 15:09:36 | | 8 | 965 | 2013-03-14 15:09:37 | | 9 | 965 | 2013-03-14 15:09:37 | | 10 | 965 | 2013-03-14 15:09:37 | +-----------+--------------+------------------------+ 30 rows in set (0.01 sec)
使用PHP数组处理一下，看下如下输出：
Array
(
[1] => Array
(
[title] => This is a test 0
[date] => 2013-03-14 15:09:36
[content] => This is test content 0
)
[2] => Array
(
[title] => This is a test 1
[date] => 2013-03-14 15:09:36
[content] => This is test content 1
)
[3] => Array
(
[title] => This is a test 2
[date] => 2013-03-14 15:09:36
[content] => This is test content 2
)
[4] => Array
(
[title] => This is a test 3
[date] => 2013-03-14 15:09:36
[content] => This is test content 3
)
[5] => Array
(
[title] => This is a test 4
[date] => 2013-03-14 15:09:36
[content] => This is test content 4
)
[6] => Array
(
[title] => This is a test 5
[date] => 2013-03-14 15:09:36
[content] => This is test content 5
)
[7] => Array
(
[title] => This is a test 6
[date] => 2013-03-14 15:09:36
[content] => This is test content 6
)
[8] => Array
(
[title] => This is a test 7
[date] => 2013-03-14 15:09:37
[content] => This is test content 7
)
[9] => Array
(
[title] => This is a test 8
[date] => 2013-03-14 15:09:37
[content] => This is test content 8
)
[10] => Array
(
[title] => This is a test 9
[date] => 2013-03-14 15:09:37
[content] => This is test content 9
)
)
这样，EAV模型的数据是如何取出来的就非常清晰了。它跟一般的查询相比，第一感觉是它费了很大劲，首先要找出字段（相当于先扫描构建水平表字段），然后使用UNION ALL把保存数据的类型表中的数据取出来。
Magento提供了对Eav模型的操作，使得我们可以像操作水平表一样操作。
» 版权所有：YaoLei's Blog » Magento中EAV模型的SQL查询分析
» 本文链接：https://www.yaolei.info/archives/279
