Advanced Custom Fields: clean orphaned values in wp_postmeta table

run.php

<?php
// this file requires a export.json in the same directory
// also define the database parameters below
// the `TARGET` has to be a flexible-content field

define('HOST', 'localhost');
define('USER', 'root');
define('PASS', '1234');
define('DATABASE', 'my_database');
define('TABLE', 'wp_postmeta');
define('TABLE_POSTS', 'wp_posts');
define('TARGET', 'layout-module'); // the flex field key

define('DRY_RUN', true); // set this explicitly to false to delete it

// set to false if you want more information
// only makes sense if you run this script on a single post
// otherwise you get like 1B+ lines output
define('DEBUG', false);
define('VERBOSE', false);

$db = new PDO('mysql:dbname='.DATABASE, USER, PASS);
$export = json_decode(file_get_contents(__DIR__.'/export.json'), true);

$allPostsHavingTheTarget = $db->query("SELECT DISTINCT post_id FROM ".TABLE." m JOIN ".TABLE_POSTS." p ON p.ID = m.post_id WHERE post_status='publish' AND `meta_key` LIKE '%".TARGET."%'")->fetchAll();

logs("found ".count($allPostsHavingTheTarget)." posts having this target");

// to test a single post use this:
//iterateOverPost($db, TABLE, $export, TARGET, 28210f);
// and exit after it!

foreach ($allPostsHavingTheTarget as $value) {
  $postId = $value['post_id'];
  iterateOverPost($db, TABLE, $export, TARGET, $postId);
}

/**
 * [iterateOverPost description]
 * @param  [type] $db     [description]
 * @param  [type] $table  [description]
 * @param  [type] $export [description]
 * @param  [type] $target [description]
 * @param  [type] $postId [description]
 * @return [type]         [description]
 */
function iterateOverPost($db, $table, $export, $target, $postId){
  $correctlyReferencedFields = getFieldReferences($db, $table, $export, $target, $postId);
  $allMetaFields = getAllMetaFieldsForTarget($db, $table, $target, $postId);

  debug("found ".count($correctlyReferencedFields)." correct field references");
  debug("found ".count($allMetaFields)." meta fields");

  debug("correct field references: ". var_export($correctlyReferencedFields, true) );

  $tobedeleted = [];

  // check if any meta_keys are not referenced
  foreach($allMetaFields as $i => $row){
    if( !in_array($row['meta_key'], $correctlyReferencedFields) ){
      $tobedeleted[] = $row['meta_key'];
    }
  }

  if( empty($tobedeleted) ){
    debug("All fields are correctly referenced");
    return;
  }

  debug("NOT REFERENCED FIELDS: ".count($tobedeleted));
  debug(var_export($tobedeleted, true));
  deleteReferences($tobedeleted, $db, $table, $postId, DRY_RUN);
}

/**
 * [deleteReferences description]
 * @param  [type] $refs   [description]
 * @param  [type] $db     [description]
 * @param  [type] $table  [description]
 * @param  [type] $postId [description]
 * @return [type]         [description]
 */
function deleteReferences($refs, $db, $table, $postId, $dryRun){
  foreach( $refs as $ref ){
    $sql1 = "DELETE FROM $table WHERE meta_key='$ref' AND post_id=$postId";
    $sql2 = "DELETE FROM $table WHERE meta_key='_$ref' AND post_id=$postId";

    if( $dryRun === false ){
      logs("exec: ".$sql1);
      $db->exec($sql1);
      logs("exec: ".$sql2);
      $db->exec($sql2);
    }else{
      logs("would exec sql: ".$sql1);
      logs("would exec sql: ".$sql2);
    }
    
  }
}

/**
 * [getFieldReferences description]
 * @param  [type] $root [description]
 * @return [type]       [description]
 */
function getFieldReferences($db, $table, $export, $root, $postId){
  $result = [$root];
  $layout = $db->query("SELECT * FROM ".$table." WHERE post_id=".$postId." AND meta_key='".$root."'")->fetch();
  $fieldKey = $db->query("SELECT * FROM ".$table." WHERE post_id=".$postId." AND meta_key='_".$root."'")->fetch();
  $referencedLayouts = unserialize($layout['meta_value']);
  
  if( empty($referencedLayouts) ){
    return $result;
  }

  $refLayoutConfig = array_map(function($layout) use ($export, $fieldKey) {
    return findLayoutInConfig($export, $fieldKey['meta_value'], $layout);
  }, $referencedLayouts);

  debug("flex field ".$root." has ".count($referencedLayouts)." referenced Layouts:");
  debug('['.implode(' | ', $referencedLayouts) . ']');

  foreach( $refLayoutConfig as $idx=>$layout ){
    foreach($layout['sub_fields'] as $sub){
      $meta_key = $root . '_'.$idx.'_'.$sub['name'];
      if( $sub['type'] === 'flexible_content' ){
        $list = getFieldReferences($db, $table, $export, $meta_key, $postId);
        $result = array_merge_recursive($result, $list);
      }

      if( !empty($sub['name']) ){
        $result[] = $meta_key;
      }
    }
  }
  return $result;
}

/**
 * [getAllMetaFieldsForTarget description]
 * @param  [type] $db     [description]
 * @param  [type] $table  [description]
 * @param  [type] $target [description]
 * @param  [type] $postId [description]
 * @return [type]         [description]
 */
function getAllMetaFieldsForTarget($db, $table, $target, $postId){
  return $db->query("SELECT * FROM ".$table." WHERE post_id=".$postId." AND meta_key LIKE '".$target."%'")->fetchAll();
}

/**
 * [findFieldInConfig description]
 * @param  [type] $export [description]
 * @param  [type] $prop   [description]
 * @param  [type] $key    [description]
 * @return [type]         [description]
 */
function findFieldInConfig( $export, $prop, $key ){
  foreach ($export as $group) {
    foreach( $group['fields'] as $field ){
      if( $field[$prop] === $key  ){
        return $field;
      }
    }
  }
  debug("$key not found in export json");
  return false;
}

/**
 * [findLayoutInConfig description]
 * @param  [type] $export   [description]
 * @param  [type] $fieldKey [description]
 * @param  [type] $target   [description]
 * @return [type]           [description]
 */
function findLayoutInConfig($export, $fieldKey, $target){
  debug("LOOKUP layout '$target' with key '$fieldKey'");
  foreach($export as $group){
    foreach($group['fields'] as $field){
      $layout = findLayoutInField($field, $fieldKey, $target);
      if( $layout ){
        return $layout;
      }
    }
  }
  debug('layout not found');
  return false;
}

/**
 * [findLayoutInField description]
 * @param  [type] $field    [description]
 * @param  [type] $fieldKey [description]
 * @param  [type] $target   [description]
 * @return [type]           [description]
 */
function findLayoutInField($field, $fieldKey, $target){
  verbose("looking for '$target' with $fieldKey in ".$field['name']);
  if( $field['type'] !== 'flexible_content' ){
    return false;
  }

  foreach( $field['layouts'] as $layout ){
    if( $layout['name'] === $target && $field['key'] == $fieldKey ){
      debug("found $target in ".$field['name']);
      return $layout;
    }

    foreach($layout['sub_fields'] as $sub){
      verbose("looking for $target in sub fields of ".$layout['name']);
      $found = findLayoutInField($sub, $fieldKey, $target);
      if( $found ){
        return $found;
      }
    }
  }
  debug("..layout not found in ".$field['name']);
  return false;
}

/**
 * [logs description]
 * @param  [type] $msg [description]
 * @return [type]      [description]
 */
function logs($msg){
  echo $msg.PHP_EOL;
}

/**
 * [debug description]
 * @param  [type] $msg [description]
 * @return [type]      [description]
 */
function debug($msg){
  if(DEBUG === true){
    logs($msg);
  }
}

/**
 * [verbose description]
 * @param  [type] $msg [description]
 * @return [type]      [description]
 */
function verbose($msg){
  if(VERBOSE === true){
    logs($msg);
  }
}