From ccf5e61a075c35d24befc175994b5cb8957ca322 Mon Sep 17 00:00:00 2001
From: Jake Vanderwerf <get@jakevanderwerf.ca>
Date: Wed, 26 Aug 2026 21:45:53 +0000
Subject: [PATCH] =Extra JVB() calls replaced, minor tweaks
---
inc/managers/CustomTable.php | 160 ++++++++++++++++++++++++++++++++++++++++++++--------
1 files changed, 134 insertions(+), 26 deletions(-)
diff --git a/inc/managers/CustomTable.php b/inc/managers/CustomTable.php
index e344576..bbe261f 100644
--- a/inc/managers/CustomTable.php
+++ b/inc/managers/CustomTable.php
@@ -38,6 +38,8 @@
protected static string $termIDType;
protected static string $postIDType;
protected static string $postTable;
+ protected ?CustomTable $karmaTable = null;
+ protected ?CustomTable $favouritesTable = null;
protected Cache $cache;
@@ -49,15 +51,16 @@
*
* @example CustomTable::for('favourites')->insert($data);
*/
- public static function for(string $tableName): self
+ 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);
+ self::$instances[$tableName] = new self($tableName, $user, $auth, $useTransactions);
}
return self::$instances[$tableName];
}
+
public static function destroyInstance(string $tableName):void
{
if (isset(self::$instances[$tableName])) {
@@ -72,6 +75,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();
@@ -142,6 +154,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
@@ -184,7 +201,7 @@
if (is_array($key)) {
$value = $key['value'];
// Ensure value is wrapped in parentheses
- if (!str_starts_with(trim($value), '(')) {
+ if (!str_contains(trim($value), '(`')) {
$value = '(`' . $value . '`)';
}
$parts[] = $key['key'] . ' KEY ' . $value;
@@ -260,7 +277,7 @@
* @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 $useTransactions = false)
+ public function __construct(string $tableName, bool $user = false, bool $auth = false, bool $useTransactions = false)
{
global $wpdb;
$this->wpdb = $wpdb;
@@ -269,6 +286,12 @@
$this->useTransactions = $useTransactions;
$this->cache = Cache::for($tableName);
+ if ($user) {
+ $this->cache->user();
+ }
+ if ($auth) {
+ $this->cache->auth();
+ }
$usersStatus = $this->wpdb->get_row("SHOW TABLE STATUS LIKE '{$this->wpdb->users}'");
$parentCollation = $usersStatus->Collation ?? 'utf8mb4_general_ci';
@@ -311,17 +334,37 @@
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 LIMIT
+ * Set pagination
*
- * @param int $limit Number of records
- * @param int $offset Optional offset
+ * @param int $perPage Number of records per page
+ * @param int $page Page number (1-based)
* @return self
*/
- public function limit(int $limit, int $offset = 0): self
+ public function limit(int $perPage, int $page = 1): self
{
- $this->builder['limit'] = $limit;
- $this->builder['offset'] = $offset;
+ $this->builder['per_page'] = $perPage;
+ $this->builder['page'] = $page;
return $this;
}
@@ -570,7 +613,7 @@
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);
@@ -594,13 +637,13 @@
* 'where' => ['user_id' => 1],
* 'orderby' => 'date_added',
* 'order' => 'DESC',
- * 'limit' => 20
+ * 'per_page' => 20
* ]);
*/
- public function getMany(array $args = [], string $output = OBJECT): array
+ public function getMany(array $args = [], bool $itemsOnly = true, string $output = OBJECT): array
{
- return $this->cache->remember(
- $this->cache->generateKey(array_merge($args, ['output' => $output])),
+ $items = $this->cache->remember(
+ array_merge($args, ['output' => $output]),
function () use ($args, $output) {
$query = "SELECT * FROM {$this->fullTableName}";
$values = [];
@@ -615,16 +658,25 @@
// 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}";
}
// LIMIT
- if (!empty($args['limit'])) {
- $limit = absint($args['limit']);
- $offset = !empty($args['offset']) ? absint($args['offset']) : 0;
- $query .= " LIMIT {$offset}, {$limit}";
+ if (array_key_exists('limit', $args)) {
+ error_log('[CustomTable]::getMany deprecated key \'limit\' - use \'per_page\' instead. '.print_r($args, true));
+ $args['per_page'] = $args['limit'];
+ }
+ if (array_key_exists('offset', $args)) {
+ error_log('[CustomTable]::getMany deprecated key \'offset\' - use \'page\' instead. '.print_r($args, true));
+ $args['page'] = $args['offset'] / $args['limit'];
+ }
+ if (!empty($args['per_page'])) {
+ $perPage = absint($args['per_page']);
+ $page = !empty($args['page']) ? absint($args['page']) : 1;
+ $offset = ($page - 1) * $perPage;
+ $query .= " LIMIT {$offset}, {$perPage}";
}
if (empty($values)) {
@@ -635,8 +687,40 @@
}
);
+ if ($itemsOnly) {
+ return $items;
+ }
+ $page = max(1, $args['page'] ?? 1);
+ $perPage = $args['per_page']??false;
+ $total = $this->count($args['where']);
+ return [
+ 'items' => $items,
+ 'total' => $total,
+ 'has_more' => $perPage && ($page * $perPage) < $total,
+ ];
}
+ 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
@@ -648,7 +732,7 @@
$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"]);
@@ -656,13 +740,13 @@
}
$args = ['where' => $where];
if ($orderby) {
- $args['orderby'] = $orderby;
+ $args['orderby'] = $this->resolveOrderby($orderby);
}
if ($order) {
$args['order'] = $order;
}
if ($limit) {
- $args['limit'] = $limit;
+ $args['per_page'] = $limit;
}
return array_column($this->getMany($args), $column);
@@ -680,7 +764,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 = [];
@@ -1017,16 +1101,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);
@@ -1076,7 +1178,7 @@
private function logError(string $operation, array $context = []): void
{
if (function_exists('JVB')) {
- JVB()->error()->log(
+ Errors::getInstance()->log(
$this->tableName,
"CustomTable {$operation} failed: " . $this->wpdb->last_error,
$context,
@@ -1086,4 +1188,10 @@
error_log("[CustomTable:{$this->tableName}] {$operation} failed: " . $this->wpdb->last_error);
}
}
+
+ public function sum(string $column, array $where):float
+ {
+ $rows = $this->pluck($column, $where);
+ return array_sum($rows);
+ }
}
--
Gitblit v1.10.0