some suggestions about field relations parents / childs #400
Description
Hello we are working on few fields for joomcck, here you are some suggestions for relations parents / childs fields :
- Schema & Indexing (harden the defaults)
Relation tables (N–N): enforce solid composite indexes in the core.
CREATE INDEX rel_parent_field ON #__js_res_relations (parent_id, field_id);
CREATE INDEX rel_child_field ON #__js_res_relations (child_id, field_id);
CREATE INDEX rel_parent_child ON #__js_res_relations (parent_id, child_id);
CREATE INDEX rel_section_parent ON #__js_res_relations (section_id, parent_id);
Denormalized counters in #__js_res_record:
add related_count_children, related_count_parents (or a JSON/INT field per field_id) to avoid repeated COUNT(*) queries.
Sorting keys: ensure created, ordering, and id are indexed everywhere they’re used for default ordering.
Column types: use INT UNSIGNED for IDs, utf8mb4 charset, and avoid TEXT where VARCHAR is enough.
- Queries & Access Patterns
Seek pagination (keyset pagination) instead of OFFSET:
SELECT ...
WHERE rel.parent_id = :pid AND rel.field_id = :fid AND r_child.id < :cursor
ORDER BY r_child.id DESC
LIMIT :limit;
Batch-load relations to eliminate N+1 queries:
In list views, collect visible record IDs and perform one single query using IN (...) to fetch all related data at once.
Selective projection: only fetch the columns actually used (avoid SELECT * in large joins).
Add an internal debug helper that logs EXPLAIN for any query taking >100 ms (for developers and admins).
- First-Class Application Cache
Built-in Redis object cache (key → serialized rows/joins) with tag-based invalidation per record/section.
Micro-fragment cache (TTL 30–120 s) for relation blocks on the frontend.
Warm-up tasks: scheduled jobs to pre-cache the most-visited relation pages.
Targeted invalidation: when a record or relation changes, only purge the relevant keys
(rel:{section}:{field}:{parent}:{page}:{order}) — never a full cache flush.
- Write Operations & Consistency
Relation field event hooks should:
update the denormalized counters,
enqueue a “recalculate” job if too many relations changed in bulk.
Scheduled Tasks (Joomla 4/5 native) should handle:
periodic recalculation of related_count,
WebP/image compression,
cache rebuilds (lists, faceted filters, etc.).
- Search & Filtering
Official OpenSearch/Elasticsearch integration:
index text + facets externally,
run a hybrid query: search → get IDs → fetch data from MySQL by ID (fast).
MySQL 8 alternative: enable InnoDB Fulltext indexes with custom French stopwords and histograms for better query plans.
- Flattened EAV Layer (optional but powerful)
Add a “Flat Table Builder” per section/type to materialize key fields (sorting, filtering, computed relations) into a wide “flat” table updated by background jobs.
→ Heavy views query this flat table directly, while consistency is ensured by hooks and schedulers.
- Frontend & Backend Views
Lazy-load long relation lists (button “Show more”) with SSR preloading for the first 10–20 items.
Limit ORDER BY on non-indexed columns in stock templates.
Add UI parameters for:
default number of children/parents shown,
sort strategy (indexed fields only),
toggle for showing/hiding relation counters.
- ACL & Joins
Precompute ACL visibility flags (bitmask) per record to avoid heavy joins on every request.
Cache ACL bitmasks in Redis for public sections.
- Developer Tools & DX
CLI command joomcck:optimize:
creates/revalidates the recommended indexes,
scans views for non-indexed ORDER BY usage,
outputs an optimization plan (with ready-to-run SQL).
Profiling mode:
logs p50/p95/p99 latency per block (list, record, relations),
exports JSON for Grafana/Prometheus dashboards.
- Safe Defaults (ready for “millions”)
Enable by default:
strict pagination,
relation batch-loading,
60 s fragment caching,
denormalized counters (if Scheduled Tasks available).
Provide a “Large Scale” preset toggle that applies all these optimizations automatically, with a warning if Redis isn’t configured.