EventslistModel | change include of categories (improve loading)

[JKoelman]

Information provided by ai. thing was that i was wondering if things could improve for speeding. Do remember that there were questions in the past about loading etc..

Performance Improvement: Replace N+1 Category Queries with Bulk Category Loading

Problem

JEM currently loads categories on a per-event basis inside EventslistModel::getItems():

$item->categories = $this->getCategories($item->id);

Since JEM supports multi-category assignments, every event requires an additional query against:

#__jem_categories
#__jem_cats_event_relations

This creates a classic N+1 query problem.

Example

Events in list	Category queries
20	20
100	100
500	500
1000	1000

On installations with many events and recurring events, this becomes one of the most expensive parts of rendering event lists.

---

Proposed Solution

Load all categories for all retrieved events in a single query.

Instead of:

foreach ($items as $item)
{
    $item->categories = $this->getCategories($item->id);
}

Use:

$categoryMap = $this->getCategoriesForItems($items);

foreach ($items as $item)
{
    $item->categories = $categoryMap[$item->id] ?? [];
}

---

Why This Works

JEM's multi-category architecture remains completely unchanged.

Current relationship:

Event 1 -> Category A
         -> Category B
         -> Category C

Event 2 -> Category B

Event 3 -> Category A
         -> Category D

The proposed query simply retrieves all category relations at once:

SELECT
    rel.itemid,
    c.*
FROM #__jem_cats_event_relations rel
INNER JOIN #__jem_categories c
    ON c.id = rel.catid
WHERE rel.itemid IN (...)

Results are then grouped in PHP:

$map[$eventId][] = $category;

---

Expected Performance Gain

Before

1 query -> events
100 queries -> categories

Total: 101 queries

After

1 query -> events
1 query -> categories

Total: 2 queries

Reduction:

101 → 2 queries
≈ 98% fewer database queries

---

Benefits

Faster Event Lists

Especially noticeable on:

• Events List
• Calendar views
• Category views
• Venue views
• Search results
• Module outputs

Better Recurrence Scalability

Recurring events often generate large result sets.

Example:

50 recurring series
10 occurrences each

= 500 events

Current approach:

500 category queries

Bulk loading:

1 category query

---

Compatibility

Safe

This change does not modify:

• Multi-category support
• Category permissions
• Category filtering
• Child-category filtering
• Access level handling
• Existing category objects

Only the loading strategy changes.

No Database Changes Required

No schema changes are necessary.

---

Recommended Implementation

Add a new method:

protected function getCategoriesForItems(array $items)

which:

1. Collects all event IDs.
2. Executes a single category query.
3. Groups results by eventid.
4. Returns:

[
    15 => [cat1, cat2],
    16 => [cat3],
    17 => [cat2, cat4]
]

Then replace the current per-event call in getItems().

---

Priority

High

This is one of the simplest performance improvements available in JEM and becomes increasingly valuable on sites with:

• many events
• many recurring events
• many category assignments
• large calendars
• large event lists

[mckillo]

Thanks for the analysis. The proposal is technically valid and it points to a real optimization opportunity.

Loading categories event by event can create an N+1 query pattern, especially in large event lists or with recurring events. A bulk-loading method that retrieves all category relations for the current result set and groups them by event ID should be viable and could noticeably reduce database queries.

However, this touches a central model used by several views, so I would not include it at RC stage unless it fixes a blocking bug. For the current RC we should keep the risk low. I think this is a good candidate to start the next development cycle for JEM 5.1.0, with benchmarks and regression checks for category access, multi-category events, filtering, ordering and recurring event lists.

This is for review for the next version.