Category Filtering Logic is Non-Intuitive

[crh3675]

Let's say I created categories for a menu:

• Food
  • Vegan
  • Meat

I choose Food and Vegan for the item. When I filter using "Food" and "Vegan", I get everything with "Meat" as well. This logic is broken, filtering should exclude non-matching results for ALL selections. You need a pivot table dataset:

event_id    | category_1   | category_2   | category_3
1244          On                  Off             On

So when you run queries, you can do a:

SELECT event_id FROM `pivot_table` WHERE category_1 = 'On' and category_2 = 'On'

The logic using:

( ttc.term_id IN ( 9,12 ) )

is not the correct solution for what a user would expect. Here is a quasi-patch for the expected behavior:

wp-content/plugins/all-in-one-event-calendar/app/helper/class-ai1ec-calendar-helper.php Line 563

 //////////////////////////
// CRH PATCH: 2013-08-27

    $query = $wpdb->prepare(
        "SELECT DISTINCT p.*, e.post_id, i.id AS instance_id, " .
        "i.start AS start, " .
        "i.end end, " .
        // Treat event instances that span 24 hours as all-day
        "IF( e.allday, e.allday, i.end = DATE_ADD( i.start, INTERVAL 1 DAY ) ) AS allday, " .
        "e.recurrence_rules, e.exception_rules, e.recurrence_dates, e.exception_dates, " .
        "e.venue, e.country, e.address, e.city, e.province, e.postal_code, e.instant_event," .
        "ttc.term_id," . // add in term for filtering later
        "e.show_map, e.contact_name, e.contact_phone, e.contact_email, e.cost, " .
        "e.ical_feed_url, e.ical_source_url, e.ical_organizer, e.ical_contact, e.ical_uid " .
        "FROM {$wpdb->prefix}ai1ec_events e " .
            "INNER JOIN $wpdb->posts p ON p.ID = e.post_id " .
            $wpml_join_particle .
            "INNER JOIN {$wpdb->prefix}ai1ec_event_instances i ON e.post_id = i.post_id " .
            $filter['filter_join'] .
        "WHERE post_type = '" . AI1EC_POST_TYPE . "' " .
        $wpml_where_particle .
        "AND " .
            ( $spanning ? "i.end > %s AND i.start < %s "
                                    : "i.start >= %s AND i.start < %s " ) .
        $filter['filter_where'] .
        $post_status_where .
        "ORDER BY allday DESC, i.start ASC, post_title ASC",
        $args );


// store the amount of categories we are filtering by
$filterCount = count($filter['cat_ids']);

// array for storing our count of matches
$matches = array();

$events = $wpdb->get_results( $query, ARRAY_A );

foreach ( $events as &$event ) {

    foreach($filter['cat_ids'] as $cat_id)
    {
        // create ID entry with value of zero
        if(!isset($matches[$event['instance_id']]))
        {
            $matches[$event['instance_id']] = 0;
        }     

        // for each matching term, increment the value
        if($event['term_id'] == $cat_id)
        {
            $matches[$event['instance_id']]++;
        }
    }
}

$useEvents = array();
$inUse = array();

foreach ( $events as &$event ) {

    // only use our entries whose ID count matches the filterCount
    if($matches[$event['instance_id']] == $filterCount  && !in_array($event['instance_id'], $inUse))
    {
       $inUse = $event['instance_id'];        

        $event['start'] = Ai1ec_Time_Utility::from_mysql_date( $event['start'] );
        $event['end']   = Ai1ec_Time_Utility::from_mysql_date( $event['end'] );
        $event        = new Ai1ec_Event( $event );

        $useEvents[] = $event;
    }       
}

return $useEvents;