| 1 |
<?php |
| 2 |
|
| 3 |
/** |
| 4 |
* ProductQuery — focused queries for product analytics data. |
| 5 |
* |
| 6 |
* @package Disco |
| 7 |
* @subpackage Disco\App\Analytics\Queries |
| 8 |
* @since 1.3.37 |
| 9 |
*/ |
| 10 |
|
| 11 |
namespace Disco\App\Analytics\Queries; |
| 12 |
|
| 13 |
/** |
| 14 |
* Handles all single-purpose SQL queries related to products sold in campaign-linked orders. |
| 15 |
* |
| 16 |
* Each method does exactly ONE job. |
| 17 |
* No business logic — raw DB results only. |
| 18 |
*/ |
| 19 |
class ProductQuery extends BaseQuery { |
| 20 |
|
| 21 |
/** |
| 22 |
* Returns a paginated product list for the admin table. |
| 23 |
* |
| 24 |
* Filters: date range, status, campaign, customer, order. |
| 25 |
* Categories and campaign names resolved via GROUP_CONCAT. |
| 26 |
* Variation attribute labels resolved via batch PHP lookup — no per-row subquery. |
| 27 |
* |
| 28 |
* @param array $args date_from, date_to, status, campaign_id, customer_id, |
| 29 |
* order_id, orderby, order, per_page, page. |
| 30 |
* @return array { total: int, pages: int, rows: array } |
| 31 |
*/ |
| 32 |
public function get_products_for_table( array $args ): array { |
| 33 |
$context = $this->build_list_context( $args ); |
| 34 |
$pagination = $this->resolve_pagination( $args ); |
| 35 |
$sort = $this->resolve_sort( $args, array( 'revenue', 'orders_count', 'customers_count', 'total_quantity' ), 'revenue' ); |
| 36 |
|
| 37 |
$total = $this->run_count( $this->build_count_sql( $context ), $context['params'] ); |
| 38 |
|
| 39 |
if ( 0 === $total ) { |
| 40 |
return $this->format_list_result( 0, $pagination['per_page'], array() ); |
| 41 |
} |
| 42 |
|
| 43 |
$rows = $this->fetch_product_rows( $context, $sort, $pagination ); |
| 44 |
$rows = $this->resolve_unit_prices( $rows, $context['tables'] ); |
| 45 |
$rows = $this->resolve_variation_names( $rows, $context['tables'] ); |
| 46 |
$rows = $this->resolve_categories( $rows ); |
| 47 |
$rows = $this->resolve_campaign_names( $rows, $context['tables'] ); |
| 48 |
|
| 49 |
return $this->format_list_result( $total, $pagination['per_page'], $rows ); |
| 50 |
} |
| 51 |
|
| 52 |
/** |
| 53 |
* Builds WHERE conditions and params for the product list filters. |
| 54 |
* |
| 55 |
* Handles common filters plus product_id and search (numeric search matches |
| 56 |
* the product ID, text search matches the product title). |
| 57 |
* |
| 58 |
* @param array $args date_from, date_to, status, campaign_id, customer_id, |
| 59 |
* order_id, product_id, search. |
| 60 |
* @return array { tables, clauses, id_col, where, params } |
| 61 |
*/ |
| 62 |
private function build_list_context( array $args ): array { |
| 63 |
global $wpdb; |
| 64 |
|
| 65 |
$tables = $this->get_tables(); |
| 66 |
$clauses = $this->get_order_clauses( $tables ); |
| 67 |
$common = $this->build_common_conditions( $args, $clauses, $tables ); |
| 68 |
$conditions = array_merge( |
| 69 |
array( 'order_meta.meta_key = %s', $this->get_campaign_dedup_condition( $tables ) ), |
| 70 |
$common['conditions'] |
| 71 |
); |
| 72 |
$params = array_merge( array( 'disco_campaign' ), $common['params'] ); |
| 73 |
|
| 74 |
if ( ! empty( $args['product_id'] ) ) { |
| 75 |
$conditions[] = 'product_lookup.product_id = %d'; |
| 76 |
$params[] = (int) $args['product_id']; |
| 77 |
} |
| 78 |
|
| 79 |
$search = $args['search'] ?? ''; |
| 80 |
|
| 81 |
if ( is_numeric( $search ) && '' !== $search ) { |
| 82 |
$conditions[] = 'product_lookup.product_id = %d'; |
| 83 |
$params[] = (int) $search; |
| 84 |
} elseif ( ! empty( $search ) ) { |
| 85 |
$conditions[] = 'LOWER(product.post_title) LIKE LOWER(%s)'; |
| 86 |
$params[] = '%' . $wpdb->esc_like( $search ) . '%'; |
| 87 |
} |
| 88 |
|
| 89 |
return array( |
| 90 |
'tables' => $tables, |
| 91 |
'clauses' => $clauses, |
| 92 |
'id_col' => $tables['order_id_col'], |
| 93 |
'where' => 'WHERE ' . implode( ' AND ', $conditions ), |
| 94 |
'params' => $params, |
| 95 |
); |
| 96 |
} |
| 97 |
|
| 98 |
/** |
| 99 |
* Builds the COUNT(DISTINCT product/variation) SQL for the current filters. |
| 100 |
* |
| 101 |
* Reads from the wc_order_product_lookup table — one row per line item with |
| 102 |
* product_id / variation_id as real columns, no order_itemmeta self-joins. |
| 103 |
* |
| 104 |
* @param array $context From build_list_context(). |
| 105 |
* @return string COUNT SQL with placeholders matching context params. |
| 106 |
*/ |
| 107 |
private function build_count_sql( array $context ): string { |
| 108 |
$tables = $context['tables']; |
| 109 |
$clauses = $context['clauses']; |
| 110 |
$order_id_column = $context['id_col']; |
| 111 |
|
| 112 |
return "SELECT COUNT(DISTINCT COALESCE(NULLIF(product_lookup.variation_id, 0), product_lookup.product_id)) FROM {$tables['order_meta']} order_meta JOIN {$tables['orders']} o ON o.ID = order_meta.{$order_id_column} AND {$clauses['status_where']} JOIN {$tables['product_lookup']} product_lookup ON product_lookup.order_id = order_meta.{$order_id_column} LEFT JOIN {$tables['posts']} product ON product.ID = product_lookup.product_id {$context['where']}"; |
| 113 |
} |
| 114 |
|
| 115 |
/** |
| 116 |
* Fetches the paginated per-product aggregate rows. |
| 117 |
* |
| 118 |
* Reads from the wc_order_product_lookup table — WooCommerce keeps one row |
| 119 |
* per line item with product_id, variation_id, product_qty, and |
| 120 |
* product_net_revenue (= the item's _line_total) as real indexed columns, |
| 121 |
* replacing four order_itemmeta self-joins. |
| 122 |
* |
| 123 |
* Groups by variation ID when present, parent product ID otherwise. |
| 124 |
* Campaign IDs are concatenated raw; names resolved later in PHP. |
| 125 |
* |
| 126 |
* @param array $context From build_list_context(). |
| 127 |
* @param array $sort From resolve_sort(). |
| 128 |
* @param array $pagination From resolve_pagination(). |
| 129 |
* @return array Aggregate row objects. |
| 130 |
*/ |
| 131 |
private function fetch_product_rows( array $context, array $sort, array $pagination ): array { |
| 132 |
$tables = $context['tables']; |
| 133 |
$clauses = $context['clauses']; |
| 134 |
$order_id_column = $context['id_col']; |
| 135 |
|
| 136 |
$rows_sql = "SELECT |
| 137 |
COALESCE(NULLIF(product_lookup.variation_id, 0), product_lookup.product_id) AS product_id, |
| 138 |
product_lookup.product_id AS parent_product_id, |
| 139 |
MAX(product.post_title) AS parent_title, |
| 140 |
COUNT(DISTINCT order_meta.{$order_id_column}) AS orders_count, |
| 141 |
COUNT(DISTINCT |
| 142 |
CASE WHEN o.billing_email != '' |
| 143 |
THEN o.billing_email END) AS customers_count, |
| 144 |
SUM(product_lookup.product_net_revenue) AS revenue, |
| 145 |
SUM(product_lookup.product_qty) AS total_quantity, |
| 146 |
GROUP_CONCAT( |
| 147 |
DISTINCT CAST(order_meta.meta_value AS UNSIGNED) |
| 148 |
ORDER BY CAST(order_meta.meta_value AS UNSIGNED) |
| 149 |
SEPARATOR '||' |
| 150 |
) AS campaign_ids_raw |
| 151 |
FROM {$tables['order_meta']} order_meta |
| 152 |
JOIN {$tables['orders']} o ON o.ID = order_meta.{$order_id_column} AND {$clauses['status_where']} |
| 153 |
JOIN {$tables['product_lookup']} product_lookup ON product_lookup.order_id = order_meta.{$order_id_column} |
| 154 |
LEFT JOIN {$tables['posts']} product ON product.ID = product_lookup.product_id |
| 155 |
{$clauses['customer_join']} |
| 156 |
{$context['where']} |
| 157 |
GROUP BY COALESCE(NULLIF(product_lookup.variation_id, 0), product_lookup.product_id) |
| 158 |
ORDER BY {$sort['orderby']} {$sort['direction']} |
| 159 |
LIMIT %d OFFSET %d"; |
| 160 |
|
| 161 |
$params = array_merge( $context['params'], array( $pagination['per_page'], $pagination['offset'] ) ); |
| 162 |
|
| 163 |
return $this->run_rows( $rows_sql, $params ); |
| 164 |
} |
| 165 |
|
| 166 |
// ========================================================================= |
| 167 |
// Row post-processing (batch lookups, no per-row queries) |
| 168 |
// ========================================================================= |
| 169 |
|
| 170 |
/** |
| 171 |
* Resolves unit_price for each row via one batch postmeta lookup. |
| 172 |
* |
| 173 |
* Replaces the two _regular_price LEFT JOINs that were previously inline in |
| 174 |
* the SQL: variation price wins when set and non-empty, parent price is the |
| 175 |
* fallback, null when neither exists — same semantics as the old COALESCE. |
| 176 |
* |
| 177 |
* @param array $rows Raw query result rows (stdClass objects). |
| 178 |
* @param array $tables Table name map from get_tables(). |
| 179 |
* @return array Rows with unit_price set on every item. |
| 180 |
*/ |
| 181 |
private function resolve_unit_prices( array $rows, array $tables ): array { |
| 182 |
$ids = array(); |
| 183 |
|
| 184 |
foreach ( $rows as $row ) { |
| 185 |
$ids[ (int) $row->product_id ] = true; |
| 186 |
$ids[ (int) $row->parent_product_id ] = true; |
| 187 |
} |
| 188 |
|
| 189 |
$price_by_id = $this->fetch_regular_prices( array_keys( $ids ), $tables ); |
| 190 |
|
| 191 |
foreach ( $rows as &$row ) { |
| 192 |
$variation_price = $price_by_id[ (int) $row->product_id ] ?? ''; |
| 193 |
|
| 194 |
if ( '' !== $variation_price ) { |
| 195 |
$row->unit_price = $variation_price; |
| 196 |
} else { |
| 197 |
$row->unit_price = $price_by_id[ (int) $row->parent_product_id ] ?? null; |
| 198 |
} |
| 199 |
} |
| 200 |
|
| 201 |
unset( $row ); |
| 202 |
|
| 203 |
return $rows; |
| 204 |
} |
| 205 |
|
| 206 |
/** |
| 207 |
* Fetches _regular_price for a set of product/variation IDs in one query. |
| 208 |
* |
| 209 |
* @param array<int> $ids Product and variation post IDs. |
| 210 |
* @param array $tables Table name map from get_tables(). |
| 211 |
* @return array Map of post_id => price string. |
| 212 |
*/ |
| 213 |
private function fetch_regular_prices( array $ids, array $tables ): array { |
| 214 |
if ( empty( $ids ) ) { |
| 215 |
return array(); |
| 216 |
} |
| 217 |
|
| 218 |
$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) ); |
| 219 |
|
| 220 |
$price_rows = $this->run_rows( |
| 221 |
"SELECT post_id, meta_value FROM {$tables['postmeta']} WHERE post_id IN ({$placeholders}) AND meta_key = '_regular_price'", |
| 222 |
$ids |
| 223 |
); |
| 224 |
|
| 225 |
$price_by_id = array(); |
| 226 |
|
| 227 |
foreach ( $price_rows as $price_row ) { |
| 228 |
$price_by_id[ (int) $price_row->post_id ] = $price_row->meta_value; |
| 229 |
} |
| 230 |
|
| 231 |
return $price_by_id; |
| 232 |
} |
| 233 |
|
| 234 |
/** |
| 235 |
* Resolves product_name for each row via a batch postmeta + terms lookup. |
| 236 |
* |
| 237 |
* For simple products: product_name = parent_title. |
| 238 |
* For variations: product_name = "Parent - Attr1, Attr2", with term names |
| 239 |
* replacing raw attribute slugs where a matching term exists. |
| 240 |
* |
| 241 |
* Replaces the per-row correlated subquery that was previously inline in the SQL, |
| 242 |
* reducing query count from N+1 to 2 extra queries regardless of result size. |
| 243 |
* |
| 244 |
* @param array $rows Raw query result rows (stdClass objects). |
| 245 |
* @param array $tables Table name map from get_tables(). |
| 246 |
* @return array Rows with product_name set on every item. |
| 247 |
*/ |
| 248 |
private function resolve_variation_names( array $rows, array $tables ): array { |
| 249 |
$variation_ids = $this->collect_variation_ids( $rows ); |
| 250 |
$attr_by_variation = $this->fetch_variation_attributes( $variation_ids, $tables ); |
| 251 |
$term_name_by_slug = $this->fetch_term_names( $attr_by_variation, $tables ); |
| 252 |
|
| 253 |
return $this->apply_variation_names( $rows, $attr_by_variation, $term_name_by_slug ); |
| 254 |
} |
| 255 |
|
| 256 |
/** |
| 257 |
* Collects variation IDs from the result rows. |
| 258 |
* |
| 259 |
* The product_id equals parent_product_id for simple products; differs for variations. |
| 260 |
* |
| 261 |
* @param array $rows Raw query result rows. |
| 262 |
* @return array<int> Variation IDs. |
| 263 |
*/ |
| 264 |
private function collect_variation_ids( array $rows ): array { |
| 265 |
$variation_ids = array(); |
| 266 |
|
| 267 |
foreach ( $rows as $row ) { |
| 268 |
if ( (int) $row->product_id === (int) $row->parent_product_id ) { |
| 269 |
continue; |
| 270 |
} |
| 271 |
|
| 272 |
$variation_ids[ (int) $row->product_id ] = true; |
| 273 |
} |
| 274 |
|
| 275 |
return array_keys( $variation_ids ); |
| 276 |
} |
| 277 |
|
| 278 |
/** |
| 279 |
* Fetches attribute slugs for a set of variation IDs in one query. |
| 280 |
* |
| 281 |
* @param array<int> $variation_ids Variation post IDs. |
| 282 |
* @param array $tables Table name map from get_tables(). |
| 283 |
* @return array Map of variation_id => string[] attribute slugs. |
| 284 |
*/ |
| 285 |
private function fetch_variation_attributes( array $variation_ids, array $tables ): array { |
| 286 |
if ( empty( $variation_ids ) ) { |
| 287 |
return array(); |
| 288 |
} |
| 289 |
|
| 290 |
$placeholders = implode( ',', array_fill( 0, count( $variation_ids ), '%d' ) ); |
| 291 |
|
| 292 |
$attr_rows = $this->run_rows( |
| 293 |
"SELECT post_id, meta_value FROM {$tables['postmeta']} WHERE post_id IN ({$placeholders}) AND meta_key LIKE 'attribute_%%' AND meta_value != '' ORDER BY post_id, meta_key", |
| 294 |
$variation_ids |
| 295 |
); |
| 296 |
|
| 297 |
$attr_by_variation = array(); |
| 298 |
|
| 299 |
foreach ( $attr_rows as $attr ) { |
| 300 |
$attr_by_variation[ (int) $attr->post_id ][] = $attr->meta_value; |
| 301 |
} |
| 302 |
|
| 303 |
return $attr_by_variation; |
| 304 |
} |
| 305 |
|
| 306 |
/** |
| 307 |
* Fetches term names for every distinct attribute slug in one query. |
| 308 |
* |
| 309 |
* @param array $attr_by_variation Map of variation_id => attribute slugs. |
| 310 |
* @param array $tables Table name map from get_tables(). |
| 311 |
* @return array Map of slug => term name. |
| 312 |
*/ |
| 313 |
private function fetch_term_names( array $attr_by_variation, array $tables ): array { |
| 314 |
$slugs = array(); |
| 315 |
|
| 316 |
foreach ( $attr_by_variation as $attrs ) { |
| 317 |
foreach ( $attrs as $slug ) { |
| 318 |
$slugs[ $slug ] = true; |
| 319 |
} |
| 320 |
} |
| 321 |
|
| 322 |
if ( empty( $slugs ) ) { |
| 323 |
return array(); |
| 324 |
} |
| 325 |
|
| 326 |
$placeholders = implode( ',', array_fill( 0, count( $slugs ), '%s' ) ); |
| 327 |
$term_rows = $this->run_rows( |
| 328 |
"SELECT slug, name FROM {$tables['terms']} WHERE slug IN ({$placeholders})", |
| 329 |
array_keys( $slugs ) |
| 330 |
); |
| 331 |
|
| 332 |
$term_name_by_slug = array(); |
| 333 |
|
| 334 |
foreach ( $term_rows as $term ) { |
| 335 |
$term_name_by_slug[ $term->slug ] = $term->name; |
| 336 |
} |
| 337 |
|
| 338 |
return $term_name_by_slug; |
| 339 |
} |
| 340 |
|
| 341 |
/** |
| 342 |
* Sets product_name on every row using the batch-fetched attribute data. |
| 343 |
* |
| 344 |
* @param array $rows Raw query result rows. |
| 345 |
* @param array $attr_by_variation Map of variation_id => attribute slugs. |
| 346 |
* @param array $term_name_by_slug Map of slug => term name. |
| 347 |
* @return array Rows with product_name set on every item. |
| 348 |
*/ |
| 349 |
private function apply_variation_names( array $rows, array $attr_by_variation, array $term_name_by_slug ): array { |
| 350 |
foreach ( $rows as &$row ) { |
| 351 |
$variation_id = 0; |
| 352 |
|
| 353 |
if ( (int) $row->product_id !== (int) $row->parent_product_id ) { |
| 354 |
$variation_id = (int) $row->product_id; |
| 355 |
} |
| 356 |
|
| 357 |
if ( $variation_id > 0 && ! empty( $attr_by_variation[ $variation_id ] ) ) { |
| 358 |
$attr_labels = array_map( |
| 359 |
function ( $slug ) use ( $term_name_by_slug ) { |
| 360 |
return $term_name_by_slug[ $slug ] ?? $slug; |
| 361 |
}, |
| 362 |
$attr_by_variation[ $variation_id ] |
| 363 |
); |
| 364 |
$row->product_name = ( $row->parent_title ?? '' ) . ' - ' . implode( ', ', $attr_labels ); |
| 365 |
} else { |
| 366 |
$row->product_name = $row->parent_title ?? ''; |
| 367 |
} |
| 368 |
} |
| 369 |
|
| 370 |
unset( $row ); |
| 371 |
|
| 372 |
return $rows; |
| 373 |
} |
| 374 |
|
| 375 |
/** |
| 376 |
* Resolves campaigns_raw for each row via a single batch_campaign_meta() call. |
| 377 |
* |
| 378 |
* Replaces LEFT JOIN campaigns + JSON_EXTRACT inside GROUP_CONCAT which executed |
| 379 |
* once per order row during the GROUP BY scan. Collects all distinct campaign IDs |
| 380 |
* across all page rows, fetches names in one query, rebuilds "id:name||..." format. |
| 381 |
* |
| 382 |
* @param array $rows Raw query result rows (stdClass objects with campaign_ids_raw). |
| 383 |
* @param array $tables Table name map from get_tables(). |
| 384 |
* @return array Rows with campaigns_raw set on every item. |
| 385 |
*/ |
| 386 |
// phpcs:ignore SlevomatCodingStandard.Complexity.Cognitive.ComplexityTooHigh |
| 387 |
private function resolve_campaign_names( array $rows, array $tables ): array { |
| 388 |
$all_ids = array(); |
| 389 |
|
| 390 |
foreach ( $rows as $row ) { |
| 391 |
if ( empty( $row->campaign_ids_raw ) ) { |
| 392 |
continue; |
| 393 |
} |
| 394 |
|
| 395 |
foreach ( explode( '||', $row->campaign_ids_raw ) as $id ) { |
| 396 |
$id = (int) $id; |
| 397 |
|
| 398 |
if ( $id <= 0 ) { |
| 399 |
continue; |
| 400 |
} |
| 401 |
|
| 402 |
$all_ids[ $id ] = true; |
| 403 |
} |
| 404 |
} |
| 405 |
|
| 406 |
$campaign_map = $this->batch_campaign_meta( array_keys( $all_ids ), $tables ); |
| 407 |
|
| 408 |
foreach ( $rows as &$row ) { |
| 409 |
if ( empty( $row->campaign_ids_raw ) ) { |
| 410 |
$row->campaigns_raw = ''; |
| 411 |
|
| 412 |
continue; |
| 413 |
} |
| 414 |
|
| 415 |
$parts = array(); |
| 416 |
|
| 417 |
foreach ( explode( '||', $row->campaign_ids_raw ) as $id ) { |
| 418 |
$id = (int) $id; |
| 419 |
$parts[] = $id . ':' . ( $campaign_map[ $id ]['name'] ?? 'Unknown' ); |
| 420 |
} |
| 421 |
|
| 422 |
// @phpstan-ignore-next-line |
| 423 |
$row->campaigns_raw = implode( '||', $parts ); |
| 424 |
} |
| 425 |
|
| 426 |
unset( $row ); |
| 427 |
|
| 428 |
return $rows; |
| 429 |
} |
| 430 |
|
| 431 |
/** |
| 432 |
* Resolves categories_raw for each row via a single wp_get_object_terms() call. |
| 433 |
* |
| 434 |
* Replaces the per-row correlated subquery that fetched from term_relationships, |
| 435 |
* term_taxonomy, and terms tables — reducing those N subquery executions to 1 query. |
| 436 |
* |
| 437 |
* Sets $row->categories_raw in the same "id:name||id2:name2" format that |
| 438 |
* ProductService already parses, so the service layer needs no changes. |
| 439 |
* |
| 440 |
* @param array $rows Raw query result rows (stdClass objects). |
| 441 |
* @return array Rows with categories_raw set on every item. |
| 442 |
*/ |
| 443 |
private function resolve_categories( array $rows ): array { |
| 444 |
if ( empty( $rows ) ) { |
| 445 |
return $rows; |
| 446 |
} |
| 447 |
|
| 448 |
$parent_ids = array_unique( |
| 449 |
array_map( |
| 450 |
function ( $row ) { |
| 451 |
return (int) $row->parent_product_id; |
| 452 |
}, |
| 453 |
$rows |
| 454 |
) |
| 455 |
); |
| 456 |
|
| 457 |
$terms = wp_get_object_terms( $parent_ids, 'product_cat', array( 'fields' => 'all_with_object_id' ) ); |
| 458 |
|
| 459 |
$terms_by_product = array(); |
| 460 |
|
| 461 |
if ( ! is_wp_error( $terms ) ) { |
| 462 |
foreach ( $terms as $term ) { |
| 463 |
/** @var object{object_id: int, term_id: int, name: string} $term */ |
| 464 |
$terms_by_product[ (int) $term->object_id ][] = $term->term_id . ':' . $term->name; |
| 465 |
} |
| 466 |
} |
| 467 |
|
| 468 |
foreach ( $rows as &$row ) { |
| 469 |
$pid = (int) $row->parent_product_id; |
| 470 |
|
| 471 |
if ( ! empty( $terms_by_product[ $pid ] ) ) { |
| 472 |
$row->categories_raw = implode( '||', $terms_by_product[ $pid ] ); |
| 473 |
} else { |
| 474 |
$row->categories_raw = ''; |
| 475 |
} |
| 476 |
} |
| 477 |
|
| 478 |
unset( $row ); |
| 479 |
|
| 480 |
return $rows; |
| 481 |
} |
| 482 |
|
| 483 |
} |
| 484 |
|
