Shop Project - Documentation
Shop LDM
Part 04.2: Navigation Views
This module contains views for stable navUID navigation with lazy-loading support.
The views enable hierarchical navigation through categories, years, and sets, optimized for dynamic loading of child nodes.
- v_nav_category_root: All level-0 categories as navigation roots.
- v_nav_category_children: Direct children of a category for lazy-loading.
- v_nav_years_by_category: All year groups for a category.
- v_nav_sets_by_year_category: Sets of a specific category + year.
- v_nav_count_children: Quick check if a node has children.
- v_nav_sets_by_category: All sets of a category (flat, sorted).
This module is the foundation for dynamic navigation in the shop area.
v_nav_category_root View
Purpose: All level-0 categories as navigation roots.
Fields:
- cat_ID -- Category ID
- cat_key -- Unique category key
- cat_status -- Status (A=Active)
- cat_node_type -- Node type ('category')
- cat_type -- Category type
- cat_level_actual -- 0 (constant for roots)
Source:
- FROM tb_category c
- WHERE c.cat_level = 0 AND c.cat_status = 'A'
Sorting: ORDER BY c.cat_tree_num ASC, c.cat_key ASC
Usage: First navigation level in the shop.v_nav_category_children View
Purpose: Direct children of a category (Level > 0) for lazy-loading of subcategories.
Fields:
- parent_cat_ID -- Parent category ID
- cat_ID -- Child category ID
- cat_key -- Category key
- cat_status -- Status
- cat_node_type -- Node type
- cat_type -- Category type
- sort_order -- Sort order
- relation_type -- 'tree' or 'ref'
Source:
- FROM tb_cat_hierarchy h
- JOIN tb_category c ON h.child_cat_ID = c.cat_ID
- WHERE c.cat_status = 'A' AND h.relation_type IN ('tree', 'ref')
Sorting: ORDER BY h.sort_order ASC, c.cat_key ASC
Usage: Dynamic loading of subcategories.v_nav_years_by_category View
Purpose: All year groups for a category (from set release data).
Fields:
- cat_ID -- Category ID
- release_year -- Release year
- set_count -- Number of sets in this year
Source:
- FROM tb_set s
- WHERE s.set_status = 'A' AND s.set_year IS NOT NULL
- GROUP BY s.cat_ID, s.set_year
Sorting: ORDER BY release_year DESC
Usage: Year nodes in navigation.v_nav_sets_by_year_category View
Purpose: Sets of a specific category + year.
Fields:
- cat_ID -- Category ID
- release_year -- Release year
- set_ID -- Set ID
- set_name -- Set name
- set_code -- Set code
- set_status -- Status
Source:
- FROM tb_set s
- WHERE s.set_status = 'A' AND s.set_year IS NOT NULL
Sorting: ORDER BY s.set_year DESC, s.set_name ASC
Usage: Set nodes in navigation below year nodes.v_nav_count_children View
Purpose: Quick check if a node has children (for has_children flag).
Fields:
- parent_id -- Parent node ID
- child_count -- Number of child nodes
- node_type -- Node type ('category' or 'year')
Source (UNION ALL):
Part 1 - Category children:
SELECT h.parent_cat_ID, 1 AS child_count, 'category' AS node_type
FROM tb_cat_hierarchy h
JOIN tb_category c ON h.child_cat_ID = c.cat_ID
WHERE h.relation_type IN ('tree', 'ref') AND c.cat_status = 'A'
Part 2 - Year children:
SELECT s.cat_ID, 1 AS child_count, 'year' AS node_type
FROM tb_set s
WHERE s.set_status = 'A' AND s.set_year IS NOT NULL
GROUP BY s.cat_ID, s.set_year
Grouping: GROUP BY parent_id, node_type
Usage: UI flag whether a node is expandable.v_nav_sets_by_category View
Purpose: All sets of a category (flat, sorted by release).
Fields:
- cat_ID -- Category ID
- set_ID -- Set ID
- set_name -- Set name
- set_code -- Set code
- set_status -- Status
- first_release -- First release year
Source:
- FROM tb_set s
- WHERE s.set_status = 'A' AND s.set_year IS NOT NULL
Sorting: ORDER BY first_release DESC, s.set_name ASC
Usage: Alternative flat set list without year grouping.