add-filters-to-post-table.php

<?php
add_action( 'restrict_manage_posts', 'toms_admin_filters', 10, 2 );
add_action( 'pre_get_posts', 'toms_filter_posts', 10, 1 );
add_filter( 'posts_join', 'toms_posts_join', 10, 2 );
add_filter( 'posts_where', 'toms_posts_where', 10, 2 );
add_filter( 'posts_groupby', 'toms_posts_groupby', 10, 2 );

/**
 * Adds custom filters to the post list table.
 *
 * @param string $post_type The post type.
 * @param string $which The location of the table.
 * @return void
 */
function toms_admin_filters( string $post_type, string $which ): void {
	$taxonomies = array( 'district' );

	foreach ( $taxonomies as $tax ) {
		if ( ! is_object_in_taxonomy( $post_type, $tax ) ) {
			continue;
		}

		$filtered_term = isset( $_GET[ "{$tax}_filter" ] ) ? $_GET[ "{$tax}_filter" ] : '';

		$dropdown_options = array(
			'show_option_all' => get_taxonomy( $tax )->labels->all_items,
			'hide_if_empty' => false,
			'hierarchical' => true,
			'show_count' => true,
			'orderby' => 'name',
			'id' => $tax,
			'name' => "{$tax}_filter",
			'taxonomy' => $tax,
			'echo' => true,
			'selected' => $filtered_term,
		);

		echo '<label class="screen-reader-text" for="' . $tax . '">' . get_taxonomy( $tax )->labels->filter_by_item . '</label>';
		wp_dropdown_categories( $dropdown_options );
	}
}

/**
 * Adds the custom filters to the main query.
 *
 * @param WP_Query $query The WP_Query instance.
 * @return void
 */
function toms_filter_posts( WP_Query $query ): void {
	$taxonomies = array( 'district' );

	if ( ! is_admin() || ! $query->is_main_query() ) {
		return;
	}

	foreach ( $taxonomies as $tax ) {
		if ( isset( $_GET[ "{$tax}_filter" ] ) && is_numeric( $_GET[ "{$tax}_filter" ] ) ) {
			$query->set( "{$tax}_filter", (int) $_GET[ "{$tax}_filter" ] );
		}
	}
}

/**
 * Joins the term_relationships table to the posts table.
 * 
 * @param string $join The JOIN clause of the query.
 * @param WP_Query $query The WP_Query instance.
 * @return string The updated JOIN clause.
 */
function toms_posts_join( string $join, WP_Query $query ): string {
	global $wpdb;

	$taxonomies = array( 'district' );

	if ( ! is_admin() || ! $query->is_main_query() || str_contains( $join, 'term_relationships' ) || empty( $taxonomies ) ) {
		return $join;
	}
	
	foreach ( $taxonomies as $tax ) {
		if ( str_contains( $join, 'term_relationships' ) ) {
			return $join;
		}

		if ( isset( $query->query_vars[ "{$tax}_filter" ] ) ) {
			$join .= " LEFT JOIN {$wpdb->term_relationships} ON {$wpdb->posts}.ID = {$wpdb->term_relationships}.object_id";
		}
	}

	return $join;
}

/**
 * Adds the term filters to the WHERE clause.
 * 
 * @param string $where The WHERE clause of the query.
 * @param WP_Query $query The WP_Query instance.
 * @return string The updated WHERE clause.
 */
function toms_posts_where( string $where, WP_Query $query ): string {
	global $wpdb;

	if ( ! is_admin() || ! $query->is_main_query() ) {
		return $where;
	}

	$taxonomies = array( 'district' );
	$terms = array();

	foreach ( $taxonomies as $tax ) {
		if ( ! empty( $query->query_vars[ "{$tax}_filter" ] ) ) {
			$terms[] = (int) $query->query_vars[ "{$tax}_filter" ];
		}
	}

	if ( empty( $terms ) ) {
		return $where;
	}

	$filtered_terms = implode( ', ', $terms );
	$where .= " AND {$wpdb->posts}.ID IN (
		SELECT object_id
		FROM {$wpdb->term_relationships}
		WHERE term_taxonomy_id IN ({$filtered_terms})
		GROUP BY object_id
		HAVING COUNT(DISTINCT term_taxonomy_id) = " . count( $terms ) . "
	)";

	return $where;
}

/**
 * Groups the posts by their ID to avoid duplicates.
 * 
 * @param string $groupby The GROUP BY clause of the query.
 * @param WP_Query $query The WP_Query instance.
 * @return string The updated GROUP BY clause.
 */
function toms_posts_groupby( string $groupby, WP_Query $query ): string {
	global $wpdb;

	if ( ! is_admin() || ! $query->is_main_query() ) {
		return $groupby;
	}

	$groupby = "{$wpdb->posts}.ID";

	return $groupby;
}