From a6413da67a4579866f0fd0323ddb23a7b6b9654a Mon Sep 17 00:00:00 2001
From: Jake Vanderwerf <get@jakevanderwerf.ca>
Date: Fri, 18 Sep 2026 17:00:53 +0000
Subject: [PATCH] =Cleaning up the Integrations setup a bit, started the Helcim integration work too
---
inc/managers/CustomTable.php | 288 +++++++++++++++++++++++++++++++++++++--------------------
1 files changed, 186 insertions(+), 102 deletions(-)
diff --git a/inc/managers/CustomTable.php b/inc/managers/CustomTable.php
index 1a6719c..3bb0114 100644
--- a/inc/managers/CustomTable.php
+++ b/inc/managers/CustomTable.php
@@ -2,6 +2,8 @@
namespace JVBase\managers;
use Exception;
+use JVBase\inc\managers\ErrorHelper;
+use wpdb;
if (!defined('ABSPATH')) {
exit;
@@ -19,8 +21,11 @@
*/
class CustomTable
{
- protected \wpdb $wpdb;
+ use ErrorHelper;
+ private static ?wpdb $wpdb = null;
+ private static string $prefix;
protected string $tableName;
+ protected bool $table_exists = false;
protected string $definition;
protected string $fullTableName;
protected bool $useTransactions;
@@ -38,26 +43,34 @@
protected static string $termIDType;
protected static string $postIDType;
protected static string $postTable;
+ protected ?CustomTable $karmaTable = null;
+ protected ?CustomTable $favouritesTable = null;
protected Cache $cache;
/**
* Fluent factory method
- *
- * @param string $tableName Table name without prefix/BASE
+ * @param string $tableName
+ * @param bool $user
+ * @param bool $auth
+ * @param bool $useTransactions
* @return self
- *
* @example CustomTable::for('favourites')->insert($data);
*/
public static function for(string $tableName, bool $user = false, bool $auth = false, bool $useTransactions = false): self
{
if (!isset(self::$instances[$tableName])) {
self::$instances[$tableName] = new self($tableName, $user, $auth, $useTransactions);
+ self::$instances[$tableName]->setFlags();
}
return self::$instances[$tableName];
}
+ protected function setFlags():void
+ {
+ $this->table_exists = !is_null(self::$wpdb->get_var("SHOW TABLES LIKE '{$this->fullTableName}'"));
+ }
public static function destroyInstance(string $tableName):void
{
@@ -73,6 +86,15 @@
return self::$instances[$tableName];
}
+ public function setKarma(CustomTable $karma):void
+ {
+ $this->karmaTable = $karma;
+ }
+ public function setFavourite(CustomTable $favourites):void
+ {
+ $this->favouritesTable = $favourites;
+ }
+
public function ensureDefined() {
$this->ensureUserTable();
$this->ensureUserIDType();
@@ -82,20 +104,20 @@
protected function ensureUserTable():void
{
if (!isset(static::$userTable)) {
- static::$userTable = is_multisite() ? $this->getMultisiteUsersTable() : $this->wpdb->users;
+ static::$userTable = is_multisite() ? $this->getMultisiteUsersTable() : self::$wpdb->users;
}
}
protected function getMultisiteUsersTable():string
{
- $siteUsersTable = $this->wpdb->prefix . 'users';
- $siteExists = $this->wpdb->get_var(
- $this->wpdb->prepare("SHOW TABLES LIKE %s", $siteUsersTable)
+ $siteUsersTable = self::$wpdb->prefix . 'users';
+ $siteExists = self::$wpdb->get_var(
+ self::$wpdb->prepare("SHOW TABLES LIKE %s", $siteUsersTable)
);
if ($siteExists) {
return $siteUsersTable;
}
//fallback to main one
- return $this->wpdb->users;
+ return self::$wpdb->users;
}
protected function ensureUserIDType():void
{
@@ -108,27 +130,27 @@
protected function ensureTermIDType():void
{
if (!isset(static::$termIDType)) {
- static::$termIDType = $this->getColumnType($this->wpdb->terms, 'term_id');
+ static::$termIDType = $this->getColumnType(self::$wpdb->terms, 'term_id');
}
}
protected function ensureTermTable():void
{
if (!isset(static::$termTable)) {
- static::$termTable = $this->wpdb->terms;
+ static::$termTable = self::$wpdb->terms;
}
}
protected function ensurePostIDType():void
{
if (!isset(static::$postIDType)) {
- static::$postIDType = $this->getColumnType($this->wpdb->posts, 'ID');
+ static::$postIDType = $this->getColumnType(self::$wpdb->posts, 'ID');
}
}
protected function ensurePostTable():void
{
if (!isset(static::$postTable)) {
- static::$postTable = $this->wpdb->posts;
+ static::$postTable = self::$wpdb->posts;
}
}
@@ -143,6 +165,11 @@
return $this;
}
+ public function getColumnKeys():array
+ {
+ return array_keys($this->columns);
+ }
+
/**
* @param array $keys An array of {string} $keys. If a $key is an array, you can define a custom $key => $value, example: 'UNIQUE' => $value, or 'PRIMARY' => $value
* @return $this
@@ -210,7 +237,7 @@
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
foreach (self::$instances as $instance) {
- if (!$instance->wpdb->get_var("SHOW TABLES LIKE '{$instance->fullTableName}'")) {
+ if (!$instance->table_exists) {
$instance->createTable();
}
}
@@ -222,29 +249,29 @@
error_log('[CustomTable]No definition set for '.$this->tableName);
return;
}
- if ($this->wpdb->get_var("SHOW TABLES LIKE '{$this->fullTableName}'")) {
+ if ($this->table_exists) {
return;
}
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
error_log('Creating Database Table: '. $this->tableName);
$charset = self::$charsetCollate;
$schema = "CREATE TABLE IF NOT EXISTS {$this->fullTableName} {$this->definition} {$charset}";
- $this->wpdb->flush();
+ self::$wpdb->flush();
$hasForeignKey = stripos($schema, 'FOREIGN KEY') !== false;
if ($hasForeignKey) {
- $result = $this->wpdb->query($schema);
- $success = ($result !== false && !$this->wpdb->last_error);
+ $result = self::$wpdb->query($schema);
+ $success = ($result !== false && !self::$wpdb->last_error);
} else {
dbDelta($schema.';');
- $success = !$this->wpdb->last_error;
+ $success = !self::$wpdb->last_error;
}
if (!$success) {
- $error_msg = "SQL Error creating table {$this->tableName}: " . $this->wpdb->last_error;
+ $error_msg = "SQL Error creating table {$this->tableName}: " . self::$wpdb->last_error;
error_log($error_msg);
error_log("Failed SQL Query: " . $schema);
- } elseif ($this->wpdb->get_var("SHOW TABLES LIKE '{$this->fullTableName}'")) {
+ } else {
error_log("Successfully created table: {$this->fullTableName}");
}
}
@@ -257,16 +284,30 @@
self::$instances = [];
}
+ protected function checkWPDB():void
+ {
+ if (is_null(self::$wpdb)) {
+ global $wpdb;
+ self::$wpdb = $wpdb;
+ self::$prefix = $wpdb->prefix;
+
+ $usersStatus = self::$wpdb->get_row("SHOW TABLE STATUS LIKE '{self::$wpdb->users}'");
+ $parentCollation = $usersStatus->Collation ?? 'utf8mb4_general_ci';
+ self::$charsetCollate = "DEFAULT CHARACTER SET utf8mb4 COLLATE {$parentCollation}";
+ }
+
+ }
+
/**
* @param string $tableName Table name without prefix/BASE (e.g., 'favourites', 'notifications')
* @param bool $useTransactions Whether to auto-wrap operations in transactions
*/
- public function __construct(string $tableName, bool $user = false, bool $auth = false, bool $useTransactions = false)
+ private function __construct(string $tableName, bool $user = false, bool $auth = false, bool $useTransactions = false)
{
- global $wpdb;
- $this->wpdb = $wpdb;
+ $this->checkWPDB();
+
$this->tableName = $tableName;
- $this->fullTableName = $wpdb->prefix . apply_filters('jvb_base', BASE) . $tableName;
+ $this->fullTableName = self::$prefix . apply_filters('jvb_base', BASE) . $tableName;
$this->useTransactions = $useTransactions;
$this->cache = Cache::for($tableName);
@@ -277,9 +318,6 @@
$this->cache->auth();
}
- $usersStatus = $this->wpdb->get_row("SHOW TABLE STATUS LIKE '{$this->wpdb->users}'");
- $parentCollation = $usersStatus->Collation ?? 'utf8mb4_general_ci';
- self::$charsetCollate = "DEFAULT CHARACTER SET utf8mb4 COLLATE {$parentCollation}";
}
@@ -318,6 +356,26 @@
return $this;
}
+ public function orderByKarma(string $metric = 'karmic_score', string $direction = 'DESC'): self
+ {
+ if (!$this->karmaTable || !in_array($metric, ['upvotes', 'downvotes', 'karmic_score'])) {
+ return $this->orderBy('created_at');
+ }
+ $this->builder['orderby'] = $metric;
+ $this->builder['order'] = strtoupper($direction);
+ return $this;
+ }
+
+ public function orderByFavourites(string $direction = 'DESC'): self
+ {
+ if (!$this->favouritesTable) {
+ return $this->orderBy('created_at');
+ }
+ $this->builder['orderby'] = 'favourites_count';
+ $this->builder['order'] = strtoupper($direction);
+ return $this;
+ }
+
/**
* Set pagination
*
@@ -443,20 +501,20 @@
$data['created_at'] = current_time('mysql');
}
- $result = $this->wpdb->insert(
+ $result = self::$wpdb->insert(
$this->fullTableName,
$data,
$format
);
if ($result === false) {
- $this->logError('insert', $data);
+ $this->logError(self::$wpdb->last_error, 'insert', $data);
return false;
}
$this->cache->flush();
- return $this->wpdb->insert_id;
+ return self::$wpdb->insert_id;
}
/**
@@ -474,17 +532,15 @@
/**
* Find or create a record
- *
* @param array $searchData Data to search for
* @param array $createData Optional additional data for creation
- * @return array ['id' => int, 'created' => bool, 'record' => object]
+ * @return int|false The ID of the created record, or false
*
* @example
- * $result = CustomTable::for('favourites')->findOrCreate(
- * ['user_id' => 1, 'target_id' => 123],
- * ['type' => 'tattoo']
- * );
- * // Returns: ['id' => 456, 'created' => false, 'record' => object]
+ * $result = CustomTable::for('favourites')->findOrCreate(
+ * ['user_id' => 1, 'target_id' => 123],
+ * ['type' => 'tattoo']
+ * );
*/
public function findOrCreate(array $searchData, array $createData = []):int|false
{
@@ -550,10 +606,10 @@
(" . implode(',', $columns_escaped) . ")
VALUES " . implode(',', $placeholders);
- $result = $this->wpdb->query($this->wpdb->prepare($query, $values));
+ $result = self::$wpdb->query(self::$wpdb->prepare($query, $values));
if ($result === false) {
- $this->logError('bulkInsert', ['rows' => count($rows)]);
+ $this->logError(self::$wpdb->last_error, 'bulkInsert', ['rows' => count($rows)]);
return false;
}
@@ -577,37 +633,36 @@
public function get(array $where, string $output = OBJECT): object|array|null
{
return $this->cache->remember(
- $this->cache->generateKey(array_merge(['output' => $output], $where)),
+ array_merge(['output' => $output], $where),
function() use ($where, $output) {
$query = "SELECT * FROM {$this->fullTableName}";
$where = $this->buildWhereClause($where);
$query .= " WHERE {$where['sql']}";
$values = $where['values'];
- return $this->wpdb->get_row($this->wpdb->prepare($query, $values), $output);
+ return self::$wpdb->get_row(self::$wpdb->prepare($query, $values), $output);
}
);
}
/**
* Get multiple records
- *
* @param array $args Query arguments: where, orderby, order, limit, offset
+ * @param bool $itemsOnly
* @param string $output OBJECT, ARRAY_A, or ARRAY_N
* @return array
- *
* @example
- * $favs = $table->getMany([
- * 'where' => ['user_id' => 1],
- * 'orderby' => 'date_added',
- * 'order' => 'DESC',
- * 'per_page' => 20
- * ]);
+ * $favs = $table->getMany([
+ * 'where' => ['user_id' => 1],
+ * 'orderby' => 'date_added',
+ * 'order' => 'DESC',
+ * 'per_page' => 20
+ * ]);
*/
public function getMany(array $args = [], bool $itemsOnly = true, string $output = OBJECT): array
{
$items = $this->cache->remember(
- $this->cache->generateKey(array_merge($args, ['output' => $output])),
+ array_merge($args, ['output' => $output]),
function () use ($args, $output) {
$query = "SELECT * FROM {$this->fullTableName}";
$values = [];
@@ -622,7 +677,7 @@
// ORDER BY
if (!empty($args['orderby'])) {
- $orderby = sanitize_sql_orderby($args['orderby']);
+ $orderby = $this->resolveOrderby($args['orderby']);
$order = (!empty($args['order']) && strtoupper($args['order']) === 'ASC') ? 'ASC' : 'DESC';
$query .= " ORDER BY {$orderby} {$order}";
}
@@ -644,10 +699,10 @@
}
if (empty($values)) {
- return $this->wpdb->get_results($query, $output);
+ return self::$wpdb->get_results($query, $output);
}
- return $this->wpdb->get_results($this->wpdb->prepare($query, $values), $output);
+ return self::$wpdb->get_results(self::$wpdb->prepare($query, $values), $output);
}
);
@@ -664,11 +719,33 @@
];
}
+ protected function resolveOrderby(string $orderby):string
+ {
+ $orderby = strtolower($orderby);
+ if (in_array($orderby, ['upvotes', 'downvotes', 'karmic_score']) && $this->karmaTable) {
+ $tableName = $this->karmaTable->getFullTableName();
+ return match ($orderby) {
+ 'upvotes' => "(SELECT COUNT(*) FROM {$tableName} k WHERE k.item_id = {$this->fullTableName}.id AND k.vote = 'up')",
+ 'downvotes' => "(SELECT COUNT(*) FROM {$tableName} k WHERE k.item_id = {$this->fullTableName}.id AND k.vote = 'down')",
+ 'karmic_score' => "(SELECT COALESCE(SUM(CASE WHEN k.vote = 'up' THEN 1 WHEN k.vote = 'down' THEN -1 ELSE 0 END), 0) FROM {$tableName} k WHERE k.item_id = {$this->fullTableName}.id)",
+ };
+ }
+
+ if ($orderby === 'favourites_count' && $this->favouritesTable) {
+ $tableName = $this->favouritesTable->getFullTableName();
+ return "(SELECT COUNT(*) FROM {$tableName} f WHERE f.target_id = {$this->fullTableName}.id)";
+ }
+
+ return sanitize_sql_orderby($orderby);
+ }
/**
* Get a specific column value from all matches
* @param string $column
* @param array $where
+ * @param string|null $orderby
+ * @param string|null $order
+ * @param int|null $limit
* @return array
*/
public function pluck(string $column, array $where = [], ?string $orderby = null, ?string $order = null, ?int $limit = null): array
@@ -676,15 +753,15 @@
$key = array_merge($where, ['column' => $column, 'orderby' => $orderby, 'order' => $order, 'limit' => $limit]);
return $this->cache->remember(
- $this->cache->generateKey($key),
+ $key,
function() use ($column, $where, $orderby, $order, $limit) {
if (!empty($this->columns) && !array_key_exists($column, $this->columns)) {
- $this->logError('pluck', ['reason' => "Column '{$column}' not in definition"]);
+ $this->logError("Column '{$column}' not in definition", 'pluck');
return [];
}
$args = ['where' => $where];
if ($orderby) {
- $args['orderby'] = $orderby;
+ $args['orderby'] = $this->resolveOrderby($orderby);
}
if ($order) {
$args['order'] = $order;
@@ -708,7 +785,7 @@
public function count(array $where = []): int
{
return $this->cache->remember(
- $this->cache->generateKey(array_merge(['source' => 'count'], $where)),
+ array_merge(['source' => 'count'], $where),
function() use ($where) {
$query = "SELECT COUNT(*) FROM {$this->fullTableName}";
$values = [];
@@ -720,10 +797,10 @@
}
if (empty($values)) {
- return (int) $this->wpdb->get_var($query);
+ return (int) self::$wpdb->get_var($query);
}
- return (int) $this->wpdb->get_var($this->wpdb->prepare($query, $values));
+ return (int) self::$wpdb->get_var(self::$wpdb->prepare($query, $values));
}
);
@@ -766,7 +843,7 @@
$data['updated_at'] = current_time('mysql');
}
- $result = $this->wpdb->update(
+ $result = self::$wpdb->update(
$this->fullTableName,
$data,
$where,
@@ -775,7 +852,7 @@
);
if ($result === false) {
- $this->logError('update', ['data' => $data, 'where' => $where]);
+ $this->logError(self::$wpdb->last_error,'update', ['data' => $data, 'where' => $where]);
}
$this->cache->flush();
@@ -798,14 +875,14 @@
*/
public function delete(array $where, ?array $where_format = null): int|false
{
- $result = $this->wpdb->delete(
+ $result = self::$wpdb->delete(
$this->fullTableName,
$where,
$where_format
);
if ($result === false) {
- $this->logError('delete', ['where' => $where]);
+ $this->logError(self::$wpdb->last_error, 'delete', ['where' => $where]);
}
$this->cache->flush();
@@ -821,7 +898,7 @@
*
* @param string $query SQL query (use {table} as placeholder)
* @param array $values Values for prepare()
- * @return mixed Query result
+ * @return int|bool|\mysqli_result|null Query result
*
* @example
* $results = $table->query(
@@ -829,15 +906,15 @@
* [1, 'pending', 'active']
* );
*/
- public function query(string $query, array $values = []): mixed
+ public function query(string $query, array $values = []): int|bool|null|\mysqli_result
{
$query = str_replace('{table}', $this->fullTableName, $query);
if (empty($values)) {
- return $this->wpdb->query($query);
+ return self::$wpdb->query($query);
}
- return $this->wpdb->query($this->wpdb->prepare($query, $values));
+ return self::$wpdb->query(self::$wpdb->prepare($query, $values));
}
/**
@@ -853,10 +930,10 @@
$query = str_replace('{table}', $this->fullTableName, $query);
if (empty($values)) {
- return $this->wpdb->get_results($query, $output);
+ return self::$wpdb->get_results($query, $output);
}
- return $this->wpdb->get_results($this->wpdb->prepare($query, $values), $output);
+ return self::$wpdb->get_results(self::$wpdb->prepare($query, $values), $output);
}
/**
@@ -864,17 +941,17 @@
*
* @param string $query SQL query (use {table} as placeholder)
* @param array $values Values for prepare()
- * @return mixed
+ * @return string|null
*/
- public function queryVar(string $query, array $values = []): mixed
+ public function queryVar(string $query, array $values = []): ?string
{
$query = str_replace('{table}', $this->fullTableName, $query);
if (empty($values)) {
- return $this->wpdb->get_var($query);
+ return self::$wpdb->get_var($query);
}
- return $this->wpdb->get_var($this->wpdb->prepare($query, $values));
+ return self::$wpdb->get_var(self::$wpdb->prepare($query, $values));
}
// =========================================================================
@@ -886,7 +963,7 @@
*/
public function startTransaction(): void
{
- $this->wpdb->query('START TRANSACTION');
+ self::$wpdb->query('START TRANSACTION');
}
/**
@@ -894,7 +971,7 @@
*/
public function commit(): void
{
- $this->wpdb->query('COMMIT');
+ self::$wpdb->query('COMMIT');
}
/**
@@ -902,7 +979,7 @@
*/
public function rollback(): void
{
- $this->wpdb->query('ROLLBACK');
+ self::$wpdb->query('ROLLBACK');
}
/**
@@ -929,7 +1006,7 @@
return $result;
} catch (Exception $e) {
$this->rollback();
- $this->logError('transaction', ['error' => $e->getMessage()]);
+ $this->logError(self::$wpdb->last_error,'transaction', ['error' => $e->getMessage()]);
return false;
}
}
@@ -951,7 +1028,7 @@
*/
public function getInsertId(): int
{
- return $this->wpdb->insert_id;
+ return self::$wpdb->insert_id;
}
/**
@@ -959,7 +1036,7 @@
*/
public function getLastError(): string
{
- return $this->wpdb->last_error;
+ return self::$wpdb->last_error;
}
/**
@@ -967,7 +1044,7 @@
*/
public function getAffectedRows(): int
{
- return $this->wpdb->rows_affected;
+ return self::$wpdb->rows_affected;
}
public function getUserIDType():string
@@ -1005,16 +1082,16 @@
// PRIVATE HELPERS
// =========================================================================
private function getColumnType(string $table, string $column):string|false {
- $tableExists = $this->wpdb->get_var(
- $this->wpdb->prepare("SHOW TABLES LIKE %s", $table)
+ $tableExists = self::$wpdb->get_var(
+ self::$wpdb->prepare("SHOW TABLES LIKE %s", $table)
);
if (!$tableExists) {
error_log("[CustomTable] Table {$table} does not exist for getColumnType");
return 'bigint(20) unsigned'; // safe fallback
}
- $result = $this->wpdb->get_row(
- $this->wpdb->prepare(
+ $result = self::$wpdb->get_row(
+ self::$wpdb->prepare(
"SELECT COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
@@ -1033,7 +1110,7 @@
private function tableExists():bool
{
- return !is_null($this->wpdb->get_var($this->wpdb->prepare("SHOW TABLES LIKE %s", $this->fullTableName)));
+ return !is_null(self::$wpdb->get_var(self::$wpdb->prepare("SHOW TABLES LIKE %s", $this->fullTableName)));
}
/**
* Build WHERE clause from associative array
@@ -1045,16 +1122,34 @@
foreach ($where as $column => $value) {
$column_safe = esc_sql($column);
+ // OR grouping: ['OR' => [ [...cond1], [...cond2] ]]
+ if ($column === 'OR' && is_array($value)) {
+ $orConditions = [];
+ foreach ($value as $orWhere) {
+ $orClause = $this->buildWhereClause($orWhere);
+ $orConditions[] = $orClause['sql'];
+ $values = array_merge($values, $orClause['values']);
+ }
+ $conditions[] = '(' . implode(' OR ', $orConditions) . ')';
+ continue;
+ }
+
if ($value === null) {
$conditions[] = "`{$column_safe}` IS NULL";
} elseif (is_array($value) && count($value) === 2 && is_string($value[0])) {
[$operator, $operand] = $value;
$operator = strtoupper(trim($operator));
- if (in_array($operator, ['IN', 'NOT IN']) && is_array($operand)) {
+ if ($operator === 'IS NOT NULL') {
+ $conditions[] = "`{$column_safe}` IS NOT NULL";
+ } elseif (in_array($operator, ['IN', 'NOT IN']) && is_array($operand)) {
$placeholders = implode(',', array_map([$this, 'getPlaceholder'], $operand));
$conditions[] = "`{$column_safe}` {$operator} ({$placeholders})";
$values = array_merge($values, $operand);
+ } elseif (in_array($operator, ['BETWEEN', 'NOT BETWEEN']) && is_array($operand) && count($operand) === 2) {
+ $conditions[] = "`{$column_safe}` {$operator} " . $this->getPlaceholder($operand[0]) . " AND " . $this->getPlaceholder($operand[1]);
+ $values[] = $operand[0];
+ $values[] = $operand[1];
} else {
// <, >, <=, >=, !=, LIKE, etc.
$conditions[] = "`{$column_safe}` {$operator} " . $this->getPlaceholder($operand);
@@ -1091,27 +1186,16 @@
static $cache = [];
if (!isset($cache[$this->tableName])) {
- $columns = $this->wpdb->get_col("DESCRIBE {$this->fullTableName}");
+ $columns = self::$wpdb->get_col("DESCRIBE {$this->fullTableName}");
$cache[$this->tableName] = array_flip($columns);
}
return isset($cache[$this->tableName][$column]);
}
- /**
- * Log database errors
- */
- private function logError(string $operation, array $context = []): void
+ public function sum(string $column, array $where):float
{
- if (function_exists('JVB')) {
- JVB()->error()->log(
- $this->tableName,
- "CustomTable {$operation} failed: " . $this->wpdb->last_error,
- $context,
- 'error'
- );
- } else {
- error_log("[CustomTable:{$this->tableName}] {$operation} failed: " . $this->wpdb->last_error);
- }
+ $rows = $this->pluck($column, $where);
+ return array_sum($rows);
}
}
--
Gitblit v1.10.0