Die Folgende Query liefert 2k Zeilen. Die meisten doppelt. Fehlt hier ggf. ein Distinct?
SELECT
category.*,
name.value AS name,
description.value AS description,
status.value AS status,
sibling.entity_id AS previousSiblingId,
defaultLocale.value AS defaultLocale,
image.value AS image
FROM
catalog_category_entity category
LEFT JOIN eav_attribute AS attribute
ON category.entity_type_id = attribute.entity_type_id
LEFT JOIN catalog_category_entity_varchar AS name
ON category.entity_id = name.entity_id
AND name.attribute_id = (SELECT attribute.attribute_id FROM eav_attribute attribute WHERE attribute.`entity_type_id` = category.`entity_type_id` AND attribute.attribute_code = 'name')
AND name.store_id = 0
LEFT JOIN catalog_category_entity_int AS status
ON category.entity_id = status.entity_id
AND status.attribute_id = (SELECT attribute.attribute_id FROM eav_attribute attribute WHERE attribute.`entity_type_id` = category.`entity_type_id` AND attribute.attribute_code = 'is_active')
AND status.store_id = 0
LEFT JOIN catalog_category_entity_text AS description
ON category.entity_id = description.entity_id
AND description.attribute_id = (SELECT attribute.attribute_id FROM eav_attribute attribute WHERE attribute.`entity_type_id` = category.`entity_type_id` AND attribute.attribute_code = 'description')
AND description.store_id = 0
LEFT JOIN catalog_category_entity_varchar AS image
ON category.entity_id = image.entity_id
AND image.attribute_id = (SELECT attribute.attribute_id FROM eav_attribute attribute WHERE attribute.`entity_type_id` = category.`entity_type_id` AND attribute.attribute_code = 'image')
AND image.store_id = 0
LEFT JOIN core_config_data AS defaultLocale
ON defaultLocale.scope = 'default' AND defaultLocale.path = 'general/locale/code'
LEFT JOIN catalog_category_entity AS sibling
ON sibling.entity_id = (SELECT previous.entity_id
FROM (SELECT sub_category.entity_id,
sub_category.parent_id,
IFNULL(sub_category.position,
IFNULL(
(SELECT new_position.position + sub_category.entity_id
FROM catalog_category_entity new_position
WHERE sub_category.parent_id = new_position.parent_id
ORDER BY new_position.position DESC
LIMIT 1),
sub_category.entity_id)) position
FROM catalog_category_entity sub_category) previous
WHERE previous.position <
IFNULL(category.position,
IFNULL(
(SELECT previous.position + category.entity_id
FROM catalog_category_entity previous
WHERE category.parent_id = previous.parent_id
ORDER BY previous.position DESC
LIMIT 1),
category.entity_id)
)
AND category.parent_id = previous.parent_id
ORDER BY previous.position DESC
LIMIT 1)
WHERE category.entity_id IN ('363', '364', '365', '366', '367', '368', '369', '370', '371', '372', '373', '377', '378', '379', '380', '381', '382', '383', '384', '385', '386', '387', '388', '389', '390', '391', '392', '393', '394', '396', '398', '399', '400', '401', '402', '403', '404', '405', '406', '407', '408', '409', '410', '411', '412', '413', '414')
ORDER BY level, position;
Mit einem Distinct verringert sich die Anzahl der Zeilen von ca. 2k auf 47.