WordPress到期文章状态更新：SQL替代WP_Query并定时执行

Got it, let's convert your WP_Query logic into an efficient SQL query and set up a scheduled task to run it automatically. Here's a step-by-step implementation tailored to your needs:

1. Core Function to Update Expired Posts #

This function will run the SQL query, fetch expired posts, and update their status safely using WordPress's built-in tools:

function update_expired_featured_posts() {
    global $wpdb;
    
    // Get current timestamp (matches your original $todayDate logic)
    $current_timestamp = strtotime(date('m/d/Y h:i:s'));
    
    // SQL query to find matching posts (uses table prefix for compatibility)
    $sql = $wpdb->prepare(
        "SELECT p.ID
         FROM {$wpdb->prefix}posts p
         INNER JOIN {$wpdb->prefix}postmeta m1 ON p.ID = m1.post_id
         INNER JOIN {$wpdb->prefix}postmeta m2 ON p.ID = m2.post_id
         WHERE p.post_type = 'post'
           AND p.post_status = 'publish' -- Optional: Skip already non-published posts
           AND m1.meta_key = 'featured_post'
           AND m1.meta_value = '1'
           AND m2.meta_key = 'post_price_plan_expiration_date'
           AND m2.meta_value <= %d",
        $current_timestamp
    );
    
    // Fetch array of expired post IDs
    $expired_post_ids = $wpdb->get_col($sql);
    
    // Update each post's status
    if (!empty($expired_post_ids)) {
        foreach ($expired_post_ids as $post_id) {
            // Switch post status to 'draft' (adjust to 'private', 'trash', or your preferred status)
            wp_update_post(array(
                'ID' => $post_id,
                'post_status' => 'draft'
            ));
            
            // Optional: Trigger custom actions (e.g., send expiration notifications)
            do_action('post_expired', $post_id);
        }
    }
}

Key Details: #

• Uses INNER JOIN to match both meta conditions (featured_post = 1 and expired date)
• $wpdb->prepare() prevents SQL injection risks
• The post_status = 'publish' filter avoids re-updating posts that are already not published (remove this if you want to target all statuses)

2. Set Up Scheduled Cron Event #

Add this code to your theme's functions.php or a custom plugin to run the function automatically:

// Register the scheduled event on site init
add_action('init', 'schedule_expired_posts_update');
function schedule_expired_posts_update() {
    // Avoid duplicate events
    if (!wp_next_scheduled('update_expired_posts_event')) {
        // Schedule daily (use 'hourly', 'twicedaily', or custom interval for more frequent runs)
        wp_schedule_event(time(), 'daily', 'update_expired_posts_event');
    }
}

// Link our function to the scheduled event
add_action('update_expired_posts_event', 'update_expired_featured_posts');

// Optional: Clean up the cron event if your plugin/theme is deactivated
register_deactivation_hook(__FILE__, 'unschedule_expired_posts_event');
function unschedule_expired_posts_event() {
    wp_clear_scheduled_hook('update_expired_posts_event');
}

Customize the Schedule: #

If you need a non-default interval (e.g., every 6 hours), register a custom cron schedule first:

add_filter('cron_schedules', 'add_custom_cron_interval');
function add_custom_cron_interval($schedules) {
    $schedules['every_six_hours'] = array(
        'interval' => 21600, // 6 hours in seconds
        'display' => __('Every 6 Hours')
    );
    return $schedules;
}

Then replace 'daily' with 'every_six_hours' in the wp_schedule_event() call.

3. Test the Function #

To verify it works immediately, add this line temporarily and visit any page on your site:

update_expired_featured_posts();

Remove it after testing to avoid unintended runs.

Important Tips: #

• Ensure your WordPress cron is functional. Some hosts disable wp-cron.php, so you may need to set up a server-side cron job pointing to this file.
• For large post datasets, add a LIMIT clause to the SQL query (e.g., LIMIT 50) and run the task more frequently to prevent timeouts.
• Always back up your database before implementing post status changes.

内容的提问来源于stack exchange，提问作者Mr Shabir