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');
}
Not all content is available!
It's likely this issue predates Contrib.social: some issue and comment data are missing.