StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFetch recurrent events: CQRS or complex query?
text
Body
copied!<p><strong>MODEL</strong> </p> <p>In the current model of my application, <strong>events</strong> can occur at <strong>several</strong> non-combinable frequencies, whose values are enumerated as follows: ONCE, DAILY, WEEKLY, MONTHLY, YEARLY, and that (i.e. the event occurrences) are starting at a certain date and <strong>optionally</strong> ending at a later one (when they're not ending, their value is NULL).</p> <p>For instance, an event:</p> <ul> <li>happening YEARLY and WEEKLY </li> <li>that started exactly <strong>n</strong> weeks ago <strong>in both cases</strong></li> <li>and whose end date is null </li> </ul> <p>has: </p> <ul> <li>a weekly occurrence right now</li> <li>a yearly occurrence in (52 - n%52) weeks</li> </ul> <p><strong>USAGE</strong></p> <p>This data can be updated via a back-office very frequently. It is needed on our public pages in order to display a 3-day timetable of all event occurrences, gathered by 1-hour slots.</p> <p>The default start of the timetable is the date of today at midnight. The timetable start can go down to 1 month before the current date and 2 months after.</p> <p><strong>WHAT I NEED</strong></p> <p>All events data is inserted without any problem via the back-office. The tricky part comes when I need to fetch them, organized in a timetable way. I tried a brute approach (select all event, and duplicating the occurrences myself) but I kinda failed (and this raises the issue of updated the big cache of replicated events whenever the data changes and everyday!).</p> <p>What I really want from this beautiful model of mine (stupid me: I didn't think of both the write AND read query at the same time) is to have all occurrences of events between beginning_date and end_date, organized by 1-hour slot.</p> <p>What do you suggest ?</p> <ol> <li>Denormalize my model and find a tool that automagically syncs my "master" model to the re-organized model? Then, how do I first denormalize it? How do I sync it?</li> <li>Find a JPQL (or HQL, or raw SQL) query that can fetch all the replicated occurrences of events during the provided interval (+ maybe some post-processing to index them by 1-hour slots)? I'd be extremely happy if you could provide it to me :-)</li> <li>something else?</li> </ol> <p>By the way, the model is open, so it can be changed in order to better address the issue. Everything is open to discussion here.</p> <p>Best regards,</p> <p>Rolf</p> <p>P.S.: the current (interesting part of the) model looks like:</p> <pre><code>mysql> describe occurrences; +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | start_date | datetime | NO | | NULL | | | end_date | datetime | YES | | NULL | | | frequency_type | varchar(50) | NO | MUL | NULL | | | event_id | bigint(20) | NO | MUL | NULL | | +----------------+-------------+------+-----+---------+----------------+ mysql> describe frequency_types; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | value | varchar(50) | NO | PRI | NULL | | +-------+-------------+------+-----+---------+-------+ </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload