DynamicQuery

DynamicQuery — demo

DynamicQuery leest veldwaarden uitsluitend uit metadata.cache.values.{lang}.*. Geen JOIN met DynamicItemsValues — alle waarden komen uit één tabel via JSON_VALUE.

Verbonden met demo.nl@127.0.0.1:3306/demo.nl — lokaal · MariaDB 10.6.23-MariaDB-0ubuntu0.22.04.1-log · live resultaten.

Basis: type + velden

fluent + immutable. Elke setter returnt een clone.

    $base = (new DynamicQuery($pdo))->language('nl')->onlyActive();

    $q = $base->type(72)->fields('title', 'label', 'link');

    [$sql, $params] = $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.title') AS `title`,
    JSON_VALUE(metadata, '$.cache.values.nl.label') AS `label`,
    JSON_VALUE(metadata, '$.cache.values.nl.link') AS `link`
FROM DynamicItems
WHERE (language = ? OR language = '')
AND type_id = ?
AND NULLIF(NULLIF(JSON_EXTRACT(metadata, '$.active'), true), 1) IS NULL
ORDER BY `order`

params: ["nl",72]

fields('*') — hele cache als JSON-blob

Voor ad-hoc inspectie zonder vooraf veldnamen te kennen.

    $q = (new DynamicQuery($pdo))->language('nl')->type('rentable')->fields('*');
    [$sql] = $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_EXTRACT(metadata, '$.cache.values.nl') AS _cache
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`

parent(), limit(), orderBy()

parent(null) → root-items; limit/offset zijn integer-literals; orderBy accepteert SQL-fragments (geen user input!).

    $q = (new DynamicQuery($pdo))->language('nl')->onlyActive()
        ->type(72)
        ->parent(null)
        ->fields('title', 'label')
        ->orderBy('`order`', 'id DESC')
        ->limit(5);

    [$sql] = $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.title') AS `title`,
    JSON_VALUE(metadata, '$.cache.values.nl.label') AS `label`
FROM DynamicItems
WHERE (language = ? OR language = '')
AND type_id = ?
AND NULLIF(NULLIF(JSON_EXTRACT(metadata, '$.active'), true), 1) IS NULL
AND parent_id IS NULL
ORDER BY `order`, id DESC
LIMIT 5

onlyActive(false) — alle items

Standaard filtert DynamicQuery op metadata.active. Schakel uit als je inactieve items ook wil zien.

    $q = (new DynamicQuery($pdo))->language('nl')
        ->onlyActive(false)
        ->type(72)
        ->fields('title', 'active');

    [$sql] = $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.title') AS `title`,
    JSON_VALUE(metadata, '$.cache.values.nl.active') AS `active`
FROM DynamicItems
WHERE (language = ? OR language = '')
AND type_id = ?
ORDER BY `order`

Live: eerste 3 resultaten — type 72 nav

Echte query tegen de live DB. Verandert mee met de inhoud.

    $items = (new DynamicQuery($pdo))->language('nl')->onlyActive()
        ->type(72)
        ->fields('title', 'label', 'link')
        ->limit(3)
        ->get();

Array
(
    [0] => Array
        (
            [id] => 178
            [title] => 
            [label] => Voetlaag: Opsomming + Media
            [link] => 
        )

    [1] => Array
        (
            [id] => 268
            [title] => 
            [label] => Tekst: sport + Afbeelding
            [link] => 
        )

    [2] => Array
        (
            [id] => 282
            [title] => 
            [label] => Tekst: sport + Afbeelding
            [link] => 
        )

)