cron job to auto delete posts of a specific post type older than x days

Question

I want to delete all posts of a specific post type (in this case "vfb_entry") that are older than 60 days. The cron job should run once a day.

I have the following code but it's not working if I trigger the cron job. However, running only the query in phpMyAdmin returns the correct result. So there must be an issue with the fuction itself.

Can anyone please help?

// Cron Job to Delete VFB Entries older than 60 days
if(!wp_next_scheduled( 'remove_old_vfb_entries')){
    wp_schedule_event(time(), 'daily', 'remove_old_vfb_entries');
}
add_action('remove_old_vfb_entries', 'myd_remove_old_vfb_entries');

// Build the function
function myd_remove_old_vfb_entries(){
global $wpdb;
// Set max post date and post_type name
$date = date("Y-m-d H:i:s", strtotime('-60 days'));
$post_type = 'vfb_entry';

// Build the query 
// Only select VFB Entries from LFG or LFM Form
$query = "
    SELECT $wpdb->posts.ID FROM $wpdb->posts 
    WHERE post_type = '$post_type' 
    AND post_status = 'publish' 
    AND post_date < '$date'
    AND ($wpdb->posts.ID IN (SELECT entry_id FROM wp_postmeta_lfg4 WHERE post_id IS NOT NULL) OR $wpdb->posts.ID IN (SELECT entry_id FROM wp_postmeta_lfm3 WHERE post_id IS NOT NULL))
    ORDER BY post_modified DESC
";
$results = $wpdb->get_results($query);
            foreach($results as $post){
                // Let the WordPress API clean up the entire post trails
                wp_delete_post( $post->ID, true);
          }
}

Edit: Solution without querying my views, just using wp_posts and wp_postmeta and an INNER JOIN below.

Answer 1

// add the schedule event if it has been removed 
if( ! wp_next_scheduled( 'mg_remove_old_entries' ) ) {
  wp_schedule_event( time(), 'daily', 'mg_remove_old_entries' ); //run the event daily
}

// action hooked to fired with wordpress cron job
add_action( 'mg_remove_old_entries', 'mg_remove_old_entries' );
function mg_remove_old_entries() {
  $posts = get_posts( [
    'numberposts' => -1,
    'post_type' => 'vfb_entry',
    'date_query' => [
      // get all the posts from the database which are older than 60 days
      'before' => date( "Y-m-d H:i:s", strtotime( '-60 days' ) ),
    ],
  ]);

  if( !empty($posts) ) {
    foreach( $posts as $post ) {
      wp_delete_post( $post->ID ); //remove the post from the database
    }
  }
}

Note: There is no need to run any custom sql query. It would make the query slow and also it is not good for wordpress. Wordpress has already in built functions for everything.

Answer 2

// Cron Job to Delete VFB Entries older than 60 days
if(!wp_next_scheduled( 'remove_old_vfb_entries')){
    wp_schedule_event(time(), 'daily', 'remove_old_vfb_entries');
}
add_action('remove_old_vfb_entries', 'myd_remove_old_vfb_entries');

// Build the function
function myd_remove_old_vfb_entries(){
global $wpdb;
// Set max post date and post_type name
$date = date("Y-m-d H:i:s", strtotime('-60 days'));
$post_type = 'vfb_entry';

// Build the query 
// Only Delete Entries from Form 5 and 8
$query = "
    SELECT $wpdb->posts.ID FROM $wpdb->posts 
    INNER JOIN $wpdb->postmeta 
      ON $wpdb->posts.ID = $wpdb->postmeta.post_id 
    WHERE post_type = '$post_type' 
    AND post_status = 'publish' 
    AND $wpdb->postmeta.meta_key = '_vfb_form_id' 
    AND ($wpdb->postmeta.meta_value = 5 OR $wpdb->postmeta.meta_value = 8) 
    AND post_date < '$date' 
";
$results = $wpdb->get_results($query);
            foreach($results as $post){
                // Let the WordPress API clean up the entire post trails
                wp_delete_post( $post->ID, true);
          }
}

Answer 3

I want to delete all posts of a specific post type (in this case "vfb_entry") that are older than 60 days. The cron job should run once a day.

The first step is setting up the cron job. The code in the question is correct for this part, so it's basically copied below.

The second part requires querying the database for a specific post type where the entry is older than 60 days. We can do this with get_posts() and specifying the post_type argument and the date_query argument.

//* If the scheduled event got removed from the cron schedule, re-add it
if( ! wp_next_scheduled( 'wpse_213720_remove_old_entries' ) ) {
  wp_schedule_event( time(), 'daily', 'wpse_213720_remove_old_entries' );
}

//* Add action to hook fired by cron event
add_action( 'wpse_213720_remove_old_entries', 'wpse_213720_remove_old_entries' );
function wpse_213720_remove_old_entries() {
  //* Get all the custom post type entries older than 60 days...
  $posts = get_posts( [
    'numberposts' => -1,
    'post_type' => 'wpse_262471_post_type',
    'date_query' => [
      'before' => date( "Y-m-d H:i:s", strtotime( '-60 days' ) ),
    ],
  ]);
  //* ...and delete them
  array_filter( function( $post ) {
    wp_delete_post( $post->ID );
  }, $posts );
}

The answer above directly queries $wpdb. Using the get_posts() abstraction is better for a variety of reasons including ease-of-reading and future-proofing.