For a list of events, get the first occurrence of each event, programmatically

Question

In an add-on, I'd like to get a list of entries (events) ordered by the first upcoming occurrence of that event. For example:

• Event X: 2016-01-05 and 2016-01-08 (2 occurrences)
• Event Y: 2016-01-07, 2016-01-08 and 2016-01-09 (3 occurrences)
• Event Z: 2016-01-04 and 2016-01-09 (2 occurrences)

On 2016-01-02, I should get a list of Z, X, Y. On 2016-01-06, I should get a list of Y, X, Z.

I'm not sure where to begin here. Querying the calendar_events_occurrences seems incomplete.

Answer 1

Okay, I was able to get what I needed, mainly with the help of a query similar to this one:

SELECT entry_id, MIN(start_date) AS start
FROM (
    (SELECT entry_id, start_date FROM exp_calendar_events)
    UNION
    (SELECT entry_id, start_date FROM exp_calendar_events_occurrences)
) AS events
WHERE start_date >= '20160101'
GROUP BY entry_id
ORDER BY start ASC

...which gives me all entry/event IDs in the order of their first occurrence, from the given date onward.

Answer 2

If I understand your question properly, the template tag you'd want to use is Calendar:Cal

This tag allows you to display a chronological list of events with all occurrences. You can also check out the Getting Started documentation which explains the differences between each Calendar tag, used to achieve different things. :)

So, if you're making a Day view page you code might look something like this:

{exp:calendar:cal 
    pad_short_weeks="n" 
    {if segment_3 == ''}
        date_range_start="today"
        date_range_end="today"
    {/if}
    {if segment_3 != ''}
        date_range_start="{segment_3}-{segment_4}-{segment_5}"
        date_range_end="{segment_3}-{segment_4}-{segment_5}"
    {/if}
    }
    {display_each_day}
        <div class="header">
            <div style="float: left; width: 20%;">
                <a href="{path='calendar/day'}{prev_day format="%Y/%m/%d"}/">
                    Yesterday
                </a>
            </div>
            <div style="float: left; width: 60%; text-align: center;">
                <h1>{date format="%l, %F %d, %Y"} ({day_event_total} events)</h1>
            </div>
            <div style="float: right; text-align: right; width: 20%;">
                <a href="{path='calendar/day'}{next_day format="%Y/%m/%d"}/">
                    Tomorrow
                </a>
            </div>
            <br style="clear: both;" />
        </div>
        {display_each_hour}
            <h2>
                {date format="%g:%i %a"} ({hour_event_total} events)
            </h2>
            {events}
            <div class="event">
                <div class="head">
                    <h2>
                        <a href="{path='calendar/events'}{event_id}/">
                            {event_title}
                        </a>
                    </h2> 
                {if event_all_day}
                    <p>
                        {event_start_date format="%Y-%m-%d"}
                        {if event_end_date}
                            - {event_end_date format="%Y-%m-%d"}
                        {/if}
                    </p>
                {if:else}
                    <p>
                        {event_start_date format="%Y-%m-%d %H:%i"}
                        {if event_end_date}
                            - {event_end_date format="%Y-%m-%d %H:%i"}
                        {/if}
                    </p>
                    <p>
                        This event lasts {event_duration_days} days 
                        {event_duration_hours} hours 
                        {event_duration_minutes} minutes.
                    </p>
                {/if}
                    <p>
                        From calendar:
                        <a href="{path='calendar/calendars'}{event_calendar_id}/">
                            {event_calendar_title}
                        </a>
                    </p>
                </div>
                <div class="body">
                    <p>
                        Details: {event_summary}<br />
                        Location: {event_location}
                    </p>
                </div>
            </div>
            {/events}
        {/display_each_hour}
    {/display_each_day}
{/exp:calendar:cal}