Wrong behaviour of search results

[Martin Fourdrignier]

Following-up on my previous post asking for help some months ago, here is the code I ended up with:

private function getAvailableRoomTypes() {
    global $wpdb;

    $roomsAtts = apply_filters(
        'mphb_search_available_rooms',
        array(
            'availability'      => 'locked',
            'from_date'         => $this->checkInDate,
            'to_date'           => $this->checkOutDate,
            'skip_buffer_rules' => false,
        )
    );

    $lockedRooms    = MPHB()->getRoomPersistence()->searchRooms($roomsAtts);
    $lockedRoomsStr = join(',', $lockedRooms);

    // Extract adults count directly from request
    $mphb_adults = isset($_REQUEST['mphb_adults']) ? intval($_REQUEST['mphb_adults']) : 1;

    // Start building the query
    $query = "SELECT DISTINCT room_types.ID AS id, COUNT(DISTINCT rooms.ID) AS count";
    $join = " FROM {$wpdb->posts} AS rooms"
          . " INNER JOIN {$wpdb->postmeta} AS room_type_ids ON rooms.ID = room_type_ids.post_id AND room_type_ids.meta_key = 'mphb_room_type_id'"
          . " INNER JOIN {$wpdb->posts} AS room_types ON room_type_ids.meta_value = room_types.ID"
          . " INNER JOIN {$wpdb->postmeta} AS room_capacity_ids ON room_types.ID = room_capacity_ids.post_id AND room_capacity_ids.meta_key = 'mphb_total_capacity'";

    // Start building the WHERE clause
    $where = " WHERE 1=1"
           . " AND CAST(room_capacity_ids.meta_value AS UNSIGNED) >= " . $wpdb->prepare("%d", $mphb_adults)
           . " AND rooms.post_type = '" . MPHB()->postTypes()->room()->getPostType() . "'"
           . " AND rooms.post_status = 'publish'"
           . ( !empty($lockedRoomsStr) ? " AND rooms.ID NOT IN ({$lockedRoomsStr})" : '' )
           . " AND room_type_ids.meta_value IS NOT NULL"
           . " AND room_type_ids.meta_value != ''"
           . " AND room_types.post_type = '" . MPHB()->postTypes()->roomType()->getPostType() . "'"
           . " AND room_types.post_status = 'publish'";

    // Add ordering and grouping
    $order = " GROUP BY room_type_ids.meta_value ORDER BY room_type_ids.meta_value DESC";

    // Handle attributes
    if (!empty($this->attributes)) {
        $inTerms = MPHB()->translation()->translateAttributes($this->attributes, MPHB()->translation()->getDefaultLanguage());
        $inTerms = array_unique($inTerms);
        $inTermsStr = join(',', $inTerms);

        // Add term relationships to the JOIN clause
        $join .= " INNER JOIN {$wpdb->term_relationships} AS room_relationships ON room_types.ID = room_relationships.object_id"
              . " INNER JOIN {$wpdb->term_taxonomy} AS room_attributes ON room_relationships.term_taxonomy_id = room_attributes.term_taxonomy_id";

        // Add terms filtering to WHERE clause
        $where .= " AND room_attributes.term_id IN ({$inTermsStr})";
    }

    // Fetch results
    $roomTypeDetails = $wpdb->get_results($query . $join . $where . $order, ARRAY_A);

    return $roomTypeDetails;
}

[Author]

Posts