Add 'Event is Full' field and filter.

Created on 7 January 2020, over 4 years ago
Updated 25 April 2024, about 2 months ago

It would be great to have Event is not full view filters for events to filter events which are not fully booked (where max participants > participants registered). I guess Event is full filter also would be useful.
Keeping in mind that if event property has_waitlist = 1 then users still can register for this event, new filter should be compatible with the has_waitlist filter.

Here's an example how I've done it for one of my sites via view hook, looking forward this filter to be a part of wonderful civicrm_entity module.

/**
 * Implements hook_views_query_alter().
 */
function my_module_views_query_alter(&$view, &$query) {
  if ($view->name == 'upcoming_events' && $view->current_display == 'block') {
    my_module_events_views_query_alter_participants_count($view, $query);
  }
}

/**
 * Extends hook_views_query_alter().
 *
 * Query alter to filter out fully booked events - we will create a table/view,
 * which contains participants count for each event, and then join that view
 * to the event data.
 */
function my_module_views_query_alter_participants_count(&$view, &$query) {
  // Create a view that contains participants count, grouped by event id.
  $sub_query = db_select('civicrm_participant', 'p');
  $sub_query->join('civicrm_participant_status_type', 'pst', 'p.status_id = pst.id');
  $sub_query->fields('p', ['event_id']);
  $sub_query->addExpression('COUNT(p.id)', 'qty');
  $sub_query->condition('pst.class', 'Positive');
  $sub_query->condition('pst.is_active', 1);
  $sub_query->groupBy('p.event_id');

  // Create a join object.
  $join = new views_join();
  $join->definition = [
    'table formula' => $sub_query,
    'field' => 'event_id',
    'left_table' => 'civicrm_event',
    'left_field' => 'id',
  ];
  $join->left_table = 'civicrm_event';
  $join->left_field = 'id';
  $join->field = 'event_id';
  $join->type = 'LEFT';

  // Do the actual join to the main view's query.
  $query->table_queue['participant_count'] = [
    'alias' => 'participant_count',
    'table' => $sub_query,
    'relationship' => 'civicrm_event',
    'join' => $join,
  ];

  // Add participants count conditions - we need to select any event matching
  // one of the following conditions:
  // + no participant limit.
  // + users can register any way and get added to the waiting list.
  // + participant limit is set and no participants registered yet.
  // + participant limit is greater than participant count.
  $key = $query->set_where_group('OR');
  $query->add_where($key, 'civicrm_event.max_participants', NULL, 'IS NULL');
  $query->add_where($key, 'civicrm_event.has_waitlist', 1);
  $query->add_where_expression($key, 'civicrm_event.max_participants > 0 AND participant_count.qty IS NULL');
  $query->add_where_expression($key, 'civicrm_event.max_participants > participant_count.qty');
}
✨ Feature request
Status

Closed: won't fix

Version

2.2

Component

CiviCRM Entity Views Extras

Created by

πŸ‡·πŸ‡ΊRussia i-grou

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

Production build 0.69.0 2024