Featured posts in custom table

featured-post.php

<?php

namespace Gonzo\FeaturedPosts;

use WP_Query;

const DB_VERSION = 6;

const META_FIELDS = [
	'featured' => 0,
	'featured_order' => 0,
	'home_page' => 0,
	'home_page_order' => 0,
	'sidebar' => 0,
	'sidebar_order' => 0,
	'selected' => 0,
	'archive' => 0,
];

/**
 * Hook everything here.
 *
 * @return void
 */
function bootstrap(): void {
	add_action( 'muplugins_loaded', __NAMESPACE__ . '\\create_tables' );

	// Overwrite getting and saving post meta for our custom fields to use the custom table.
	add_filter( 'get_post_metadata', __NAMESPACE__ . '\\get_featured_post_meta', 10, 3 );
	add_filter( 'add_post_metadata', __NAMESPACE__ . '\\update_featured_post_meta', 10, 4 );
	add_filter( 'update_post_metadata', __NAMESPACE__ . '\\update_featured_post_meta', 10, 4 );

	// Add query vars for our features and filter the query to add SQL clauses that query the custom table.
	add_action( 'pre_get_posts', __NAMESPACE__ . '\\post_features_query', 9999 );
	add_filter( 'posts_clauses', __NAMESPACE__ . '\\post_features_clauses', 10, 2 );
}


/**
 * Create post features table.
 *
 * @return void
 */
function create_tables(): void {
	global $wpdb;

	$current_db_version = get_option( 'gonzo_postfeatures_db_version', 0 );
	if ( $current_db_version >= DB_VERSION ) {
		return;
	}

	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE {$wpdb->prefix}postfeatures (
		post_id BIGINT(20) UNSIGNED NOT NULL,
		featured TINYINT(1) NOT NULL DEFAULT 0,
		featured_order INT(11) NOT NULL DEFAULT 0,
		home_page TINYINT(1) NOT NULL DEFAULT 0,
		home_page_order INT(11) NOT NULL DEFAULT 0,
		sidebar TINYINT(1) NOT NULL DEFAULT 0,
		sidebar_order INT(11) NOT NULL DEFAULT 0,
		selected TINYINT(1) NOT NULL DEFAULT 0,
		archive TINYINT(1) NOT NULL DEFAULT 0,
		PRIMARY KEY  (post_id),
		KEY key_featured (featured, featured_order),
		KEY key_home_page (home_page, home_page_order),
		KEY key_sidebar (sidebar, sidebar_order),
		KEY key_selected (selected),
		KEY key_archive (archive)
	) $charset_collate;";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql );

	update_option( 'gonzo_postfeatures_db_version', DB_VERSION );
}

/**
 * Get feature post meta from custom table.
 *
 * @param mixed  $check The current value of the meta key.
 * @param int    $object_id The ID of the object.
 * @param string $meta_key The meta key to retrieve.
 *
 * @return mixed The value of the meta key.
 */
function get_featured_post_meta( $check, $object_id, $meta_key ) {
	global $wpdb;

	if ( ! in_array( $meta_key, array_keys( META_FIELDS ), true ) ) {
		return $check;
	}

	$check = $wpdb->get_col(
		$wpdb->prepare(
			"SELECT `{$meta_key}` FROM {$wpdb->prefix}postfeatures WHERE post_id = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$object_id
		)
	);

	return $check ?: META_FIELDS[ $meta_key ];
}

/**
 * Update feature post meta in custom table.
 *
 * @param mixed  $check The current value of the meta key.
 * @param int    $object_id The ID of the object.
 * @param string $meta_key The meta key to update.
 * @param mixed  $meta_value The new value for the meta key.
 *
 * @return bool.
 */
function update_featured_post_meta( $check, $object_id, $meta_key, $meta_value ) {
	global $wpdb;

	if ( ! in_array( $meta_key, array_keys( META_FIELDS ), true ) ) {
		return $check;
	}

	$results = $wpdb->query(
		$wpdb->prepare(
			"INSERT INTO {$wpdb->prefix}postfeatures (post_id, `{$meta_key}`) VALUES (%d, %d) ON DUPLICATE KEY UPDATE `{$meta_key}` = VALUES(`{$meta_key}`)", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$object_id,
			$meta_value
		)
	);
	if ( $wpdb->last_error ) {
		error_log( $wpdb->last_error );
	}

	if ( ! empty( $results ) ) {
		switch ( $meta_key ) {
			case 'featured':
			case 'featured_order':
				wp_cache_delete( 'featured_posts', 'gonzo-featured-posts' );
				break;

			case 'home_page':
			case 'home_page_order':
				wp_cache_delete( 'home_page_posts', 'gonzo-featured-posts' );
				break;

			case 'sidebar':
			case 'sidebar_order':
				wp_cache_delete( 'sidebar_posts', 'gonzo-featured-posts' );
				break;

			case 'selected':
				wp_cache_delete( 'featured_interviews', 'gonzo-featured-posts' );
				wp_cache_delete( 'featured_insights', 'gonzo-featured-posts' );
				wp_cache_delete( 'video_posts', 'gonzo-featured-posts' );
				break;
		}
	}

	return true;
}

/**
 * Filter the query to include post features query vars.
 *
 * Save the vars in a property on the query object for later use in the SQL clauses
 * because WP_Query filters the params later.
 *
 * @param WP_Query $query The WP_Query object.
 * @return WP_Query
 */
function post_features_query( $query ) {
	$query_vars = array_keys( array_filter( $query->query_vars ) );
	$features_vars = array_keys( META_FIELDS );

	if ( empty( array_intersect( $query_vars, $features_vars ) ) ) {
		return $query;
	}

	$query->features_vars = [];
	$query->set( 'suppress_filters', false );
	$query->set( 'ep_integrate', false );

	foreach ( META_FIELDS as $key => $default ) {
		if ( empty( $query->query_vars[ $key ] ) || $query->query_vars[ $key ] === $default ) {
			continue;
		}

		$query->features_vars[ $key ] = $query->query_vars[ $key ];
	}

	$query->features_vars['orderby'] = $query->query_vars['orderby'];

	return $query;
}

/**
 * Filter the SQL clauses for WP_Query and add post features queries.
 *
 * @param array    $clauses The SQL clauses for the query.
 * @param WP_Query $query The WP_Query object.
 * @return array
 */
function post_features_clauses( $clauses, $query ) {
	global $wpdb;

	if ( empty( $query->features_vars ) ) {
		return $clauses;
	}

	$query->set( 'ep_integrate', false );

	$features_vars = array_keys( META_FIELDS );

	$clauses['join'] .= " LEFT JOIN {$wpdb->prefix}postfeatures ON ( {$wpdb->prefix}postfeatures.post_id = {$wpdb->posts}.ID )";

	foreach ( META_FIELDS as $key => $default ) {
		if ( ! isset( $query->query_vars[ $key ] ) || $query->query_vars[ $key ] === $default ) {
			continue;
		}

		$clauses['where'] .= $wpdb->prepare(
			" AND {$wpdb->prefix}postfeatures.{$key} = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$query->query_vars[ $key ]
		);
	}

	if ( array_intersect( array_keys( (array) $query->features_vars['orderby'] ), $features_vars ) ) {
		$clauses['orderby'] = '';
		foreach ( $query->features_vars['orderby'] as $orderby => $order ) {
			if ( in_array( $orderby, $features_vars, true ) ) {
				$clauses['orderby'] .= "{$wpdb->prefix}postfeatures.{$orderby} {$order}, ";
			} else {
				$clauses['orderby'] .= "{$wpdb->posts}.post_{$orderby} {$order}, ";
			}
		}
		$clauses['orderby'] = rtrim( $clauses['orderby'], ', ' );
	}

	return $clauses;
}

/**
 * Get featured posts ordered by featured_order field.
 *
 * @return array Array of posts.
 */
function get_featured_posts(): array {
	$posts = wp_cache_get( 'featured_posts', 'gonzo-featured-posts' );

	if ( false !== $posts ) {
		return $posts;
	}

	$posts = get_posts(
		[
			'post_type' => 'post',
			'featured' => 1,
			'orderby' => [
				'featured_order' => 'ASC',
				'date' => 'DESC',
			],
			'cache_results' => false,
			'no_found_rows' => true,
		]
	);
	wp_cache_set( 'featured_posts', $posts, 'gonzo-featured-posts', 300 );

	return $posts;
}

/**
 * Get home page posts ordered by home_page_order field.
 *
 * @return array Array of posts.
 */
function get_home_posts(): array {
	$posts = wp_cache_get( 'home_page_posts', 'gonzo-featured-posts' );
	if ( false !== $posts ) {
		return $posts;
	}

	$posts = get_posts(
		[
			'post_type' => 'post',
			'home_page' => 1,
			'numberposts' => -1,
			'orderby' => [
				'home_page_order' => 'ASC',
				'date' => 'DESC',
			],
			'cache_results' => false,
			'no_found_rows' => true,
		]
	);
	wp_cache_set( 'home_page_posts', $posts, 'gonzo-featured-posts', 300 );

	return $posts;
}

/**
 * Get sidebar posts ordered by sidebar_order field.
 *
 * @return array Array of posts.
 */
function get_sidebar_posts(): array {
	$args = [
		'post_type' => 'post',
		'sidebar' => 1,
		'numberposts' => -1,
		'orderby' => [
			'sidebar_order' => 'ASC',
			'date' => 'DESC',
		],
		'cache_results' => false,
		'no_found_rows' => true,
	];

	if ( is_category() ) {
		$args['cat'] = get_queried_object_id();
	}

	$posts = wp_cache_get( 'sidebar_posts', 'gonzo-featured-posts' );
	if ( false !== $posts ) {
		return $posts;
	}

	$posts = get_posts( $args );
	wp_cache_set( 'sidebar_posts', $posts, 'gonzo-featured-posts', 300 );

	return $posts;
}

/**
 * Get featured interview posts.
 *
 * @return array Array of posts.
 */
function get_featured_interviews(): array {
	$posts = wp_cache_get( 'featured_interviews', 'gonzo-featured-posts' );
	if ( false !== $posts ) {
		return $posts;
	}

	$posts = get_posts(
		[
			'post_type' => 'post',
			'posts_per_page' => 3,
			'selected' => 1,
			'category_name' => 'intervyuta',
			'cache_results' => false,
			'no_found_rows' => true,
		]
	);
	wp_cache_set( 'featured_interviews', $posts, 'gonzo-featured-posts', 300 );

	return $posts;
}