Listing and filtering contents of repeater fields (similiar to SQL JOIN)

[j__]

On a site that lists events, I am using a repeater field 'event_time', which contains two Inputfield Time fields 'event_time_start' and 'event_time_end'. Some events take place multiple times, others just once. Here's an example for the data structure:

• event1
  • title: First Event
  • event_time (1):
    • event_time_start: 08:00
    • event_time_end: 09:00
• event2
  • title: Second Event
  • event_time (1):
    • event_time_start: 08:00
    • event_time_end: 09:00
  • event_time (2):
    • event_time_start: 14:00
    • event_time_end: 18:00
• event3
  • title: Third Event
  • event_time (1):
    • event_time_start: 07:00
    • event_time_end: 09:30

First, I'd like to generate a list of all events, sorted by event_time_start, with every repeater item added as an actual event (similar to a SQL JOIN clause). The desired output would be:

• 07:00-09:30 Third Event
• 08:00-09:00 First Event
• 08:00-09:00 Second Event
• 14:00-18:00 Second Event

A selector like

$pages->find('template=event,event_time.event_time_start!=,sort=event_time.event_time_start');

would only return each event page once:

• 07:00-09:30 Third Event
• 08:00-09:00 First Event
• 08:00-09:00 Second Event

without

• 14:00-18:00 Second Event

Is there a possible alteration of the selector to take all different occurrences into account?

Also I'd like to filter for events taking place in the morning, using a selector like:

$pages->find('template=event,event_time.event_time_start>=06:00,event_time.event_time_start<=12:00,sort=event_time.event_time_start');

However, this would only return each 'event' page, which then contains also the afternoon version of event2.

While I have the impression these are rather simple tasks, I struggle finding a selector-based solution to it. In this example on opening times are some similarities, but it does not deal with multiple occurrences. I'd appreciate your ideas to it.

[AndZyk]

Hello @j__,

using a Repeater field for this use case may be nice on the back-end but as you have already experienced difficult to output.

One possibility could be to build yourself a new PageArray, where you add all repeater items and sort or filter them however you like.

Or you could reconsider and use regular pages for your events. ?

Regards, Andreas

[Robin S]

You could use a selector to match the repeater pages (according to their template) and then get their owner pages (the events) with getForPage().

// You need check_access=0 to match repeater pages for non-superusers
$repeater_items = $pages->find("template=repeater_event_time, event_time_start>=06:00, event_time_start<=12:00, sort=event_time_start, check_access=0");
foreach($repeater_items as $repeater_item) {
    $owner_page = $repeater_item->getForPage();
    // Use $repeater_item and $owner_page as needed to build the event/time string
    // ...
}