I have two custom element types, Event and Booking.

They are related via the eventId column on the Booking record which is a FK to the Event record.

The Booking record also has a seats column.

A usage scenario is that someone may come along and make a booking for two people (seats) at an event. Another person may then make a separate booking for the same event for 4 people.

For each event I'm going to need to know the total seats booked (in our example that would be 6).

Option 1

I could just have a totalSeatsBooked property on the EventModel with something like this:

$totalBookedSeats = 0;
$criteria = craft()->elements->getCriteria('Booking');
$criteria->occurrenceId = $this->id;
$eventBookings = $criteria->find();

if ($eventBookings)
    foreach ($eventBookings as $booking)
        $totalSeatsBooked += $booking->seats;

return $totalSeatsBooked;

But that's going to get inefficient if I start having to list multiple events at the same time.

Option 2

I could have a column on the EventRecord called totalSeatsBooked and use an onSaveBooking event to recalculate and save the total when ever a booking is saved.

This could work but it might be prone to becoming out-of-sync. I'd also only want to save the total when the booking is created, or when the seats value changes. Which adds complexity.

Option 3

This is the one I'm unsure about.

Could I extend the event's element query some how to join the two tables and run a sub-query to get the seats count? If it was possible and relatively efficient this would be my choice I think.

Any help appreciated!


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy