DynamicQueryEav

Drie EAV-strategieën met dezelfde fluent API. SQL gebouwd voor type base_layer (id=1), velden owner_item_id, label. Voor live performance-vergelijking: /bench.php.

Subselect — gecorreleerde subselect per veld

Equivalent aan library/Db/Dynamic/Select.php: één (SELECT value FROM DynamicItemsValues …) per gevraagd veld.

$q = (new DynamicQueryEav($pdo, $structure, EavStrategy::Subselect))
    ->type('base_layer')
    ->fields('owner_item_id', 'label')
    ->onlyActive();

return $q->toSql();
SELECT
    DynamicItems.id AS id,
    DynamicItems.type_id AS type_id,
    DynamicItems.`order` AS `order`,
    DynamicItems.parent_id AS parent_id,
    (SELECT `name` FROM DynamicTypes WHERE id = DynamicItems.type_id) AS type_name,
    NULLIF(NULLIF(JSON_EXTRACT(DynamicItems.metadata, '$.active'), true), 1) IS NULL AS active,
    (SELECT value FROM DynamicItemsValues WHERE item_id = DynamicItems.id AND field_id = 1 AND (language = ? OR language = '') LIMIT 1) AS `owner_item_id`,
    (SELECT value FROM DynamicItemsValues WHERE item_id = DynamicItems.id AND field_id = 2 AND (language = ? OR language = '') LIMIT 1) AS `label`
FROM DynamicItems
WHERE (DynamicItems.language = ? OR DynamicItems.language = '')
AND DynamicItems.type_id = ?
AND NULLIF(NULLIF(JSON_EXTRACT(DynamicItems.metadata, '$.active'), true), 1) IS NULL
ORDER BY `order`

LeftJoin — alias per veld

Per veld een aparte LEFT JOIN met alias dv_<naam>. Vaak het snelst bij weinig velden + goede composite index.

$q = (new DynamicQueryEav($pdo, $structure, EavStrategy::LeftJoin))
    ->type('base_layer')
    ->fields('owner_item_id', 'label');

return $q->toSql();
SELECT
    DynamicItems.id AS id,
    DynamicItems.type_id AS type_id,
    DynamicItems.`order` AS `order`,
    DynamicItems.parent_id AS parent_id,
    (SELECT `name` FROM DynamicTypes WHERE id = DynamicItems.type_id) AS type_name,
    NULLIF(NULLIF(JSON_EXTRACT(DynamicItems.metadata, '$.active'), true), 1) IS NULL AS active,
    dv_owner_item_id.value AS `owner_item_id`,
    dv_label.value AS `label`
FROM DynamicItems
LEFT JOIN DynamicItemsValues dv_owner_item_id ON dv_owner_item_id.item_id = DynamicItems.id AND dv_owner_item_id.field_id = 1 AND (dv_owner_item_id.language = ? OR dv_owner_item_id.language = '')
LEFT JOIN DynamicItemsValues dv_label ON dv_label.item_id = DynamicItems.id AND dv_label.field_id = 2 AND (dv_label.language = ? OR dv_label.language = '')
WHERE (DynamicItems.language = ? OR DynamicItems.language = '')
AND DynamicItems.type_id = ?
AND NULLIF(NULLIF(JSON_EXTRACT(DynamicItems.metadata, '$.active'), true), 1) IS NULL
ORDER BY `order`

Pivot — MAX(CASE) + GROUP BY

Eén LEFT JOIN voor alle velden, server-side gepivot met MAX(CASE WHEN field_id=N …). Beste plan bij veel velden.

$q = (new DynamicQueryEav($pdo, $structure, EavStrategy::Pivot))
    ->type('base_layer')
    ->fields('owner_item_id', 'label');

return $q->toSql();
SELECT
    DynamicItems.id AS id,
    DynamicItems.type_id AS type_id,
    DynamicItems.`order` AS `order`,
    DynamicItems.parent_id AS parent_id,
    (SELECT `name` FROM DynamicTypes WHERE id = DynamicItems.type_id) AS type_name,
    NULLIF(NULLIF(JSON_EXTRACT(DynamicItems.metadata, '$.active'), true), 1) IS NULL AS active,
    MAX(CASE WHEN v.field_id = 1 THEN v.value END) AS `owner_item_id`,
    MAX(CASE WHEN v.field_id = 2 THEN v.value END) AS `label`
FROM DynamicItems
LEFT JOIN DynamicItemsValues v ON v.item_id = DynamicItems.id AND v.field_id IN (1, 2) AND (v.language = ? OR v.language = '')
WHERE (DynamicItems.language = ? OR DynamicItems.language = '')
AND DynamicItems.type_id = ?
AND NULLIF(NULLIF(JSON_EXTRACT(DynamicItems.metadata, '$.active'), true), 1) IS NULL
GROUP BY DynamicItems.id
ORDER BY `order`

Cache-pad (DynamicQuery) — geen EAV-strategie

Voor vergelijking: dezelfde query uit metadata.cache.values.{lang}.* in plaats van DynamicItemsValues. Eén tabel, geen joins.

$q = (new DynamicQuery($pdo))
    ->type('base_layer')
    ->fields('owner_item_id', 'label')
    ->onlyActive();

return $q->toSql();
SELECT
    id,
    type_id,
    `order`,
    parent_id,
    (SELECT `name` FROM DynamicTypes WHERE id = DynamicItems.type_id) AS type_name,
    NULLIF(NULLIF(JSON_EXTRACT(metadata, '$.active'), true), 1) IS NULL AS active,
    JSON_VALUE(metadata, '$.cache.values.nl.owner_item_id') AS `owner_item_id`,
    JSON_VALUE(metadata, '$.cache.values.nl.label') AS `label`
FROM DynamicItems
WHERE (language = ? OR language = '')
AND type_id = (SELECT id FROM DynamicTypes WHERE name = ?)
AND NULLIF(NULLIF(JSON_EXTRACT(metadata, '$.active'), true), 1) IS NULL
ORDER BY `order`