From c4aa5cdb5e90ad4b420e22772797d16980232a2b Mon Sep 17 00:00:00 2001
From: Jake Vanderwerf <get@jakevanderwerf.ca>
Date: Wed, 15 Apr 2026 18:38:55 +0000
Subject: [PATCH] =Updating custom tables to utilize CustomTable.php
---
inc/managers/TaxonomyRelationships.php | 360 ++++++++++++++++++++++--------------------------------------
1 files changed, 132 insertions(+), 228 deletions(-)
diff --git a/inc/managers/TaxonomyRelationships.php b/inc/managers/TaxonomyRelationships.php
index 2becd42..1f919bd 100644
--- a/inc/managers/TaxonomyRelationships.php
+++ b/inc/managers/TaxonomyRelationships.php
@@ -1,9 +1,10 @@
<?php
namespace JVBase\managers;
-use JVBase\managers\Cache;
+use Exception;
use WP_Error;
use WP_Post;
+use WP_Query;
if (!defined('ABSPATH')) {
exit; // Exit if accessed directly
@@ -11,15 +12,15 @@
class TaxonomyRelationships
{
- private string $table_name;
- protected object $cache;
-
+ protected Cache $cache;
+ protected CustomTable $relationships;
public function __construct()
{
- global $wpdb;
- $this->table_name = $wpdb->prefix . BASE.'taxonomy_relationships';
+ $this->defineTables();
+
$this->cache = Cache::for('term_relationship', WEEK_IN_SECONDS);
+ $this->cache->connect('terms');
// Ensure the table exists
// $this->create_table_if_not_exists();
@@ -28,13 +29,41 @@
add_action('init', [$this, 'init']);
}
- /**
- * @return string
- */
- public function getTableName():string
- {
- return $this->table_name;
- }
+ protected function defineTables():void
+ {
+ $relationships = CustomTable::for('taxonomy_relationships');
+
+ $relationships->setColumns([
+ 'id' => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT',
+ 'term_id' => "{$relationships->getTermIDType()} NOT NULL",
+ 'related_term_id' => "{$relationships->getTermIDType()} NOT NULL",
+ 'taxonomy' => 'varchar(32) NOT NULL',
+ 'related_taxonomy' => 'varchar(32) NOT NULL',
+ 'post_count' => 'int(11) NOT NULL DEFAULT 0',
+ 'is_direct' => 'tinyint(1) NOT NULL DEFAULT 1',
+ 'is_hierarchical' => 'tinyint(1) NOT NULL DEFAULT 0',
+ 'last_updated' => 'timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
+ ]);
+
+ $relationships->setKeys([
+ ['key'=>'PRIMARY', 'value' => '(`id`)'],
+ ['key'=>'UNIQUE', 'value' => '`relation` (`term_id`, `related_term_id`, `taxonomy`, `related_taxonomy`)'],
+ '`term_id` (`term_id`)',
+ '`related_term_id` (`related_term_id`)',
+ '`taxonomy` (`taxonomy`)',
+ '`related_taxonomy` (`related_taxonomy`)'
+ ]);
+ $base = BASE;
+ $relationships->setConstraints([
+ "CONSTRAINT {$base}tax_rel_term_id FOREIGN KEY (`term_id`)
+ REFERENCES `{$relationships->getTermTable()}` (`term_id`) ON DELETE CASCADE",
+ "CONSTRAINT `{$base}tax_rel_related_id` FOREIGN KEY (`related_term_id`)
+ REFERENCES `{$relationships->getTermTable()}` (`term_id`) ON DELETE CASCADE"
+ ]);
+ $relationships->defineTable();
+
+ $this->relationships = $relationships;
+ }
/**
* Hook into term and post saves
@@ -59,6 +88,10 @@
*/
public function updatePostRelationships(int $post_id, WP_Post $post):void
{
+ // Skip autosaves and revisions
+ if (wp_is_post_autosave($post_id) || wp_is_post_revision($post_id)) {
+ return;
+ }
$post_type = $post->post_type;
if (in_array($post_type, jvbIgnoredPostTypes())) {
return;
@@ -119,8 +152,6 @@
*/
public function updateRelationship(int $term_id, int $related_term_id, string $taxonomy, string $related_taxonomy):bool
{
- global $wpdb;
-
// Make sure both terms exist
$term = get_term($term_id, $taxonomy);
$related_term = get_term($related_term_id, $related_taxonomy);
@@ -130,61 +161,27 @@
return false;
}
- // Ensure term_id and related_term_id are integers
- $term_id = (int)$term_id;
- $related_term_id = (int)$related_term_id;
+ // Check if term is parent of related term
+ $is_hierarchical = 0;
+ if ($taxonomy === $related_taxonomy) {
+ $ancestors = get_ancestors($related_term_id, $taxonomy, 'taxonomy');
+ if (in_array($term_id, $ancestors)) {
+ $is_hierarchical = 1;
+ }
+ }
+ // Calculate number of shared posts
+ $shared_posts_count = $this->countSharedPosts($term_id, $related_term_id, $taxonomy, $related_taxonomy);
+ $updated = $this->relationships->findOrCreate([
+ 'term_id' => $term_id,
+ 'related_term_id' => $related_term_id,
+ 'taxonomy' => $taxonomy,
+ 'related_taxonomy' => $related_taxonomy
+ ],[
+ 'is_hierarchical' => $is_hierarchical,
+ 'post_count' => $shared_posts_count
+ ]);
- // Check if relationship exists
- $existing = $wpdb->get_row($wpdb->prepare(
- "SELECT id, post_count FROM {$this->table_name}
- WHERE term_id = %d AND related_term_id = %d
- AND taxonomy = %s AND related_taxonomy = %s",
- $term_id,
- $related_term_id,
- $taxonomy,
- $related_taxonomy
- ));
-
- // Calculate number of shared posts
- $shared_posts_count = $this->countSharedPosts($term_id, $related_term_id, $taxonomy, $related_taxonomy);
-
- // Check if term is parent of related term
- $is_hierarchical = 0;
- if ($taxonomy === $related_taxonomy) {
- $ancestors = get_ancestors($related_term_id, $taxonomy, 'taxonomy');
- if (in_array($term_id, $ancestors)) {
- $is_hierarchical = 1;
- }
- }
-
- if ($existing) {
- // Update existing relationship
- $wpdb->update(
- $this->table_name,
- [
- 'post_count' => $shared_posts_count,
- 'is_hierarchical' => $is_hierarchical
- ],
- [
- 'id' => $existing->id
- ]
- );
- } else {
- // Insert new relationship
- $wpdb->insert(
- $this->table_name,
- [
- 'term_id' => $term_id,
- 'related_term_id' => $related_term_id,
- 'taxonomy' => $taxonomy,
- 'related_taxonomy' => $related_taxonomy,
- 'post_count' => $shared_posts_count,
- 'is_hierarchical' => $is_hierarchical
- ]
- );
- }
-
- return true;
+ return (bool)$updated;
}
/**
@@ -197,23 +194,35 @@
*/
private function countSharedPosts(int $term_id, int $related_term_id, string $taxonomy, string $related_taxonomy):int
{
- global $wpdb;
- $term_posts = $wpdb->get_col($wpdb->prepare(
- "SELECT object_id FROM {$wpdb->term_relationships} tr
+ $term_posts = $this->cache->remember(
+ $this->cache->generateKey(['term' => $term_id, 'taxonomy' => $taxonomy]),
+ function () use ($term_id, $taxonomy) {
+ global $wpdb;
+ return $wpdb->get_col($wpdb->prepare(
+ "SELECT object_id FROM {$wpdb->term_relationships} tr
JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.term_id = %d AND tt.taxonomy = %s",
- $term_id,
- $taxonomy
- ));
+ $term_id,
+ $taxonomy
+ ));
+ }
+ );
- $related_term_posts = $wpdb->get_col($wpdb->prepare(
- "SELECT object_id FROM {$wpdb->term_relationships} tr
+
+ $related_term_posts = $this->cache->remember(
+ $this->cache->generateKey(['term' => $related_term_id, 'taxonomy' => $related_taxonomy]),
+ function () use($related_term_id, $related_taxonomy) {
+ global $wpdb;
+ return $wpdb->get_col($wpdb->prepare(
+ "SELECT object_id FROM {$wpdb->term_relationships} tr
JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.term_id = %d AND tt.taxonomy = %s",
- $related_term_id,
- $related_taxonomy
- ));
+ $related_term_id,
+ $related_taxonomy
+ ));
+ });
+
return count(array_intersect($term_posts, $related_term_posts));
}
@@ -230,48 +239,14 @@
public function getRelatedTerms(int $term_id, string $desired_taxonomy, bool $include_hierarchical = true):array
{
- $key = sprintf(
- '%d_to_%s',
- $term_id,
- $desired_taxonomy
- );
- if ($include_hierarchical) {
- $key.='_hierarchy';
- }
- $cache = $this->cache->get($key);
- if ($cache) {
- return $cache;
- }
-
- global $wpdb;
-
- $term = get_term($term_id);
- if (is_wp_error($term) || empty($term)) {
- return [];
- }
-
- $query = $wpdb->prepare(
- "SELECT related_term_id
- FROM {$this->table_name}
- WHERE term_id = %d
- AND related_taxonomy = %s",
- $term_id,
- $desired_taxonomy
- );
-
- // Optionally include hierarchical relationships
- if (!$include_hierarchical) {
- $query .= " AND is_hierarchical = 0";
- }
-
- // Order by post count for popularity
- $query .= " ORDER BY post_count DESC";
-
- $related_term_ids = $wpdb->get_col($query);
-
- $this->cache->set($key, $related_term_ids);
-
- return $related_term_ids;
+ $where = [
+ 'term_id' => $term_id,
+ 'related_taxonomy' => $desired_taxonomy
+ ];
+ if (!$include_hierarchical) {
+ $where['is_hierarchical'] = 0;
+ }
+ return $this->relationships->pluck('related_term_id', $where, 'post_count', 'DESC');
}
// Get all related terms based on multiple source terms (for "any" match)
@@ -284,50 +259,9 @@
*/
public function getAnyRelatedTerms(array $term_ids, string $taxonomy):array
{
- if (empty($term_ids)) {
- return [];
- }
+ $term_ids = array_filter(array_map('absint', $term_ids));
+ return array_unique($this->relationships->pluck('related_term_id', ['term_id' => ['IN' => $term_ids], 'related_taxonomy' => $taxonomy], 'post_count', 'DESC'));
- $related_term_ids = [];
- foreach ($term_ids as $term_id) {
- $related = $this->getRelatedTerms($term_id, $taxonomy);
- $related_term_ids = array_merge($related_term_ids, $related);
- }
-
- return array_unique($related_term_ids);
- }
-
- // Get related terms that are common to all source terms (for "all" match)
-
- /**
- * @param array $term_ids
- * @param string $taxonomy
- *
- * @return array
- */
- public function getAllRelatedTerms(array $term_ids, string $taxonomy):array
- {
- if (empty($term_ids)) {
- return [];
- }
-
- $related_sets = [];
- foreach ($term_ids as $term_id) {
- $related = $this->getRelatedTerms($term_id, $taxonomy);
- if (!empty($related)) {
- $related_sets[] = $related;
- }
- }
-
- if (count($related_sets) === 1) {
- return $related_sets[0];
- } elseif (count($related_sets) > 1) {
- // Find intersection of all sets
- $result = array_intersect(...$related_sets);
- return array_values($result);
- }
-
- return [];
}
// Rebuild all relationships (useful for initial setup)
@@ -341,31 +275,34 @@
global $wpdb;
// Clear existing relationships
- $wpdb->query("TRUNCATE TABLE {$this->table_name}");
- $total_posts = $wpdb->get_var("SELECT COUNT(ID) FROM {$wpdb->posts} WHERE post_status = 'publish'");
+ $wpdb->query("TRUNCATE TABLE {$this->relationships->getFullTableName()}");
- // Calculate number of batches needed (50 posts per batch)
- $batch_size = 50;
- $total_batches = ceil($total_posts / $batch_size);
+ $posts = new WP_Query([
+ 'post_status' => 'publish',
+ 'posts_per_page'=> -1,
+ 'fields' => 'ids'
+ ]);
+ if (!$posts->have_posts()) {
+ wp_reset_postdata();
+ return true;
+ }
+
+ $ids = $posts->posts;
+ wp_reset_postdata();
+
// Queue the operation
$queue = JVB()->queue();
- $operation_id = 'taxonomy_rebuild_' . uniqid();
$queue->queueOperation(
'taxonomy_relationships',
1,
[
- 'action' => 'rebuild_all',
- 'offset' => 0,
- 'limit' => $batch_size,
- 'total_posts' => $total_posts,
- 'total_batches' => $total_batches
+ 'posts' => $ids,
],
[
- 'operation_id' => $operation_id,
- 'count' => $total_batches,
- 'priority' => 'normal',
+ 'chunk_key' => 'posts',
+ 'chunk_size' => 50
]
);
@@ -387,47 +324,20 @@
return $result;
}
- if (isset($data['action']) && $data['action'] === 'rebuild_all') {
- // Clear existing relationships
- global $wpdb;
-
- // Get batch of posts to process
- $offset = isset($data['offset']) ? $data['offset'] : 0;
- $limit = isset($data['limit']) ? $data['limit'] : 50; // Process in batches
-
- $posts = $wpdb->get_col($wpdb->prepare(
- "SELECT ID FROM {$wpdb->posts}
- WHERE post_status = 'publish'
- ORDER BY ID
- LIMIT %d OFFSET %d",
- $limit,
- $offset
- ));
-
- // Process each post
- foreach ($posts as $post_id) {
- $this->updatePostRelationships($post_id);
- }
-
- // Get total number of posts
- $total_posts = $wpdb->get_var("SELECT COUNT(ID) FROM {$wpdb->posts} WHERE post_status = 'publish'");
-
- // Return progress information
- return [
- 'success' => true,
- 'result' => [
- 'processed' => count($posts),
- 'offset' => $offset,
- 'next_offset' => $offset + $limit,
- 'total' => $total_posts,
- 'completed' => ($offset + $limit >= $total_posts)
- ]
- ];
- }
- return [
- 'success' => false,
- 'message' => __('Hmmm.', 'jvb')
- ];
+ try {
+ foreach ($data['posts'] as $postID) {
+ $post = get_post($postID);
+ $this->updatePostRelationships($postID, $post);
+ }
+ } catch (Exception $e) {
+ return [
+ 'success' => false,
+ 'message' => $e->getMessage()
+ ];
+ }
+ return [
+ 'success' => true
+ ];
}
// Hook this to term deletion
@@ -439,13 +349,7 @@
*/
public function deleteTermRelationships(int $term_id):void
{
- global $wpdb;
-
- $wpdb->query($wpdb->prepare(
- "DELETE FROM {$this->table_name}
- WHERE term_id = %d OR related_term_id = %d",
- $term_id,
- $term_id
- ));
+ $termIDs = $this->relationships->delete(['term_id' => $term_id]);
+ $related = $this->relationships->delete(['related_term_id' => $term_id]);
}
}
--
Gitblit v1.10.0