N`). Kept apart from * $where_parts so it is always ANDed, whatever filter_logic says. Not a * pagination cursor: it narrows the whole result set, count included. */ private $min_id = null; /** * @var string Filter logic mode: 'and' (intersection) or 'or' (union) */ private $filter_logic = 'and'; /** * @var string Catalog-visibility filter mode. * * 'all' (default): apply NO visibility restriction — a bulk CONTENT tool * must surface every product, including catalog-hidden ones. 'visible': * exclude products carrying the `exclude-from-catalog` term (WooCommerce * catalog visibility "Hidden" or "Search results only"). 'hidden': return * ONLY those catalog-hidden products. */ private $catalog_visibility = 'all'; /** * @var array Additional SQL WHERE clauses applied by the optimized query path. */ private $where_parts = []; /** * @var array Negative filters that can be replayed through legacy fallbacks if SQL optimization fails. */ private $negative_filters = []; /** * Whether a description/short-description/tag-presence filter narrowed * this builder's results: contentFilters() (its predicate lives ONLY in * the custom-SQL $where_parts path, which WP_Query/getArgs() cannot * reproduce, so getProducts() must fail CLOSED rather than fall back to * an unfiltered WP_Query result when the custom SQL fails), or * descriptions()/tags() restricting via restrictToProductIds(). All three * set it so an external caller such as * ProductEditorController::resolve_selection() refuses its "select all" * consistently, whichever method actually applied the restriction. * * @var bool */ private $has_content_filters = false; /** * @var array|null AI-processed meta presence filter for wc_get_products(). */ private $ai_processed_filter = null; /** * @var bool|null Cached wc_product_meta_lookup table availability. */ private static $product_meta_lookup_exists = null; /** * Canonical default product statuses the tools list. Single source of truth * for the PHP-level defaults (clear() args + buildStatusWherePart() fallback * + the WP_Query listing default). * * NOTE: the inline `post_status IN ('publish', 'draft', 'pending', 'private')` * SQL clauses are deliberately kept as literals — the security-first phpcs * config forbids interpolating even a controlled value into raw SQL — so any * change here MUST be mirrored in those clauses. * * @var string[] */ private const DEFAULT_PRODUCT_STATUSES = ['publish', 'draft', 'pending', 'private']; /** * ProductFilterBuilder constructor. */ public function __construct() { $this->clear(); } /** * Enable cursor-based pagination for large datasets. * More efficient than offset pagination for large tables. * * @param int|null $after_id Product ID to start after (cursor) * @param int $limit Number of products to fetch * @return ProductFilterBuilder */ public function cursorPagination(?int $after_id, int $limit = 50): ProductFilterBuilder { $this->cursor_after = $after_id; $this->cursor_limit = max(1, min($limit, 500)); $this->args['paginate'] = false; $this->args['limit'] = $this->cursor_limit; return $this; } /** * Restricts the result to products with an ID above the given one, for the count, * the list and the cache key alike. A non-positive value clears the restriction. * * @param int $id Exclusive lower bound on the product ID. * @return ProductFilterBuilder */ public function idAfter(int $id): ProductFilterBuilder { $this->min_id = $id > 0 ? $id : null; return $this; } /** * Sets the page number and the number of items per page * * @param int $page Page number * @param int $perPage Number of items per page * @return ProductFilterBuilder */ public function pagination($page, $perPage = 10) { $this->args['page'] = max(1, absint($page)); $this->args['limit'] = max(1, absint($perPage)); return $this; } /** * Adds a text search filter (title, SKU or product category name) * * @param string $search Arama terimi * @return ProductFilterBuilder */ public function search($search) { if (is_scalar($search) && '' !== (string) $search) { global $wpdb; $search_term = sanitize_text_field($search); $search_query = $wpdb->prepare( "SELECT DISTINCT p.ID FROM {$wpdb->posts} p LEFT JOIN {$wpdb->postmeta} pm ON (p.ID = pm.post_id AND pm.meta_key = '_sku') WHERE p.post_type = 'product' AND p.post_status IN ('publish', 'draft', 'pending', 'private') AND ( p.post_title LIKE %s OR pm.meta_value LIKE %s OR EXISTS ( SELECT 1 FROM {$this->getTableName('term_relationships', 'term_relationships')} tr INNER JOIN {$this->getTableName('term_taxonomy', 'term_taxonomy')} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'product_cat' INNER JOIN {$this->getTableName('terms', 'terms')} t ON t.term_id = tt.term_id WHERE tr.object_id = p.ID AND t.name LIKE %s ) )", '%' . $wpdb->esc_like($search_term) . '%', '%' . $wpdb->esc_like($search_term) . '%', '%' . $wpdb->esc_like($search_term) . '%' ); $product_ids = $wpdb->get_col($search_query); $this->restrictToProductIds($product_ids); } return $this; } /** * Adds a product status filter (publish, draft, pending, private) * * @param string|array $status Product status * @return ProductFilterBuilder */ public function status($status) { if (!empty($status)) { $this->args['status'] = is_array($status) ? array_map('sanitize_text_field', $status) : sanitize_text_field($status); } return $this; } /** * Adds a stock status filter * Adds the result to filtered_product_ids with SQL - intersects with the other filters * * @param string $stockStatus Stok durumu (instock, outofstock, onbackorder) * @return ProductFilterBuilder */ public function stockStatus($stockStatus) { if (empty($stockStatus)) { return $this; } $allowed = ['instock', 'outofstock', 'onbackorder']; $sanitized = sanitize_text_field($stockStatus); if (!in_array($sanitized, $allowed, true)) { return $this; } global $wpdb; $query = $wpdb->prepare( "SELECT DISTINCT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id WHERE p.post_type = 'product' AND p.post_status IN ('publish', 'draft', 'pending', 'private') AND pm.meta_key = '_stock_status' AND pm.meta_value = %s", $sanitized ); $product_ids = $wpdb->get_col($query); $this->restrictToProductIds($product_ids); return $this; } /** * Adds category filter. * * Resolves slugs to term IDs via get_terms() which respects * Polylang's terms_clauses filter for multilingual compatibility. * This also removes the wp_terms JOIN, improving query performance. * * @param string|array $category Category slug or array of slugs. * @return ProductFilterBuilder */ public function category($category) { if (empty($category)) { return $this; } $slugs = is_array($category) ? $category : [$category]; $slugs = array_map('sanitize_text_field', $slugs); if (empty($slugs)) { return $this; } $term_ids = $this->resolveTermSlugsToIds($slugs, 'product_cat'); if (empty($term_ids)) { $this->restrictToProductIds([]); return $this; } $term_ids = CategoryTermExpander::withDescendants($term_ids, 'product_cat'); $this->filterByTermIds($term_ids, 'product_cat'); return $this; } /** * Adds tag filter (by specific tag slugs). * * Resolves slugs to term IDs via get_terms() which respects * Polylang's terms_clauses filter for multilingual compatibility. * * @param string|array $tag Tag slug or array of slugs. * @return ProductFilterBuilder */ public function tag($tag) { if (empty($tag)) { return $this; } $slugs = is_array($tag) ? $tag : [$tag]; $slugs = array_map('sanitize_text_field', $slugs); if (empty($slugs)) { return $this; } $term_ids = $this->resolveTermSlugsToIds($slugs, 'product_tag'); if (empty($term_ids)) { $this->restrictToProductIds([]); return $this; } $this->filterByTermIds($term_ids, 'product_tag'); return $this; } /** * Resolves taxonomy term slugs to term IDs via WordPress get_terms() API. * * Delegates Polylang-specific adjustments (language bypass, translation expansion) * to PolylangTermHandler for multilingual compatibility. * * @param array $slugs Array of term slugs to resolve. * @param string $taxonomy Taxonomy name (e.g., 'product_cat', 'product_tag'). * @return int[] Array of resolved term IDs. */ private function resolveTermSlugsToIds(array $slugs, string $taxonomy): array { $args = [ 'taxonomy' => $taxonomy, 'slug' => $slugs, 'fields' => 'ids', 'hide_empty' => false, ]; PolylangTermHandler::adjustTermQueryArgs($args); $terms = get_terms($args); if (is_wp_error($terms) || empty($terms)) { return []; } $term_ids = array_map('intval', $terms); return PolylangTermHandler::expandTermIdsWithTranslations($term_ids); } /** * Filters products by taxonomy term IDs. * * Uses term_id matching instead of slug matching, eliminating the need * for a wp_terms table JOIN and improving query performance. * * @param int[] $term_ids Array of term IDs to filter by. * @param string $taxonomy Taxonomy name (e.g., 'product_cat', 'product_tag'). * @return void */ private function filterByTermIds(array $term_ids, string $taxonomy): void { global $wpdb; $placeholders = implode(',', array_fill(0, count($term_ids), '%d')); $sql = "SELECT DISTINCT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_type = 'product' AND p.post_status IN ('publish', 'draft', 'pending', 'private') AND tt.taxonomy = %s AND tt.term_id IN ($placeholders)"; $args = array_merge([$taxonomy], $term_ids); $query = $wpdb->prepare($sql, ...$args); $product_ids = $wpdb->get_col($query); $this->restrictToProductIds($product_ids); } /** * Filters by published-date window (post_date), inclusive on both ends. * * @param string $after 'Y-m-d' (or any MySQL-parseable datetime) lower bound, or '' for none. * @param string $before Upper bound, or '' for none. * @return ProductFilterBuilder */ public function dateRange(string $after, string $before) { $after = trim($after); $before = trim($before); if ('' === $after && '' === $before) { return $this; } global $wpdb; $conditions = []; $values = []; if ('' !== $after) { $conditions[] = 'p.post_date >= %s'; $values[] = $after; } if ('' !== $before) { if (1 === preg_match('/^\d{4}-\d{2}-\d{2}$/', $before)) { $before .= ' 23:59:59'; } $conditions[] = 'p.post_date <= %s'; $values[] = $before; } $range_clause = implode(' AND ', $conditions); $query = $wpdb->prepare( "SELECT DISTINCT p.ID FROM {$wpdb->posts} p WHERE p.post_type = 'product' AND p.post_status IN ('publish', 'draft', 'pending', 'private') AND {$range_clause}", ...$values ); $product_ids = $wpdb->get_col($query); $this->restrictToProductIds($product_ids); return $this; } /** * Filters by stock quantity range (_stock meta), inclusive on both ends. * * @param int|null $min Minimum stock quantity, or null for no lower bound. * @param int|null $max Maximum stock quantity, or null for no upper bound. * @return ProductFilterBuilder */ public function stockQuantity($min, $max) { if ($min === null && $max === null) { return $this; } global $wpdb; $conditions = []; $values = []; if ($min !== null) { $conditions[] = 'CAST(pm.meta_value AS SIGNED) >= %d'; $values[] = (int) $min; } if ($max !== null) { $conditions[] = 'CAST(pm.meta_value AS SIGNED) <= %d'; $values[] = (int) $max; } $range_clause = implode(' AND ', $conditions); $query = $wpdb->prepare( "SELECT DISTINCT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id WHERE p.post_type = 'product' AND p.post_status IN ('publish', 'draft', 'pending', 'private') AND pm.meta_key = '_stock' AND pm.meta_value IS NOT NULL AND pm.meta_value != '' AND {$range_clause}", ...$values ); $product_ids = $wpdb->get_col($query); $this->restrictToProductIds($product_ids); return $this; } /** * Filters by price range (_price meta, the effective/current price), * inclusive on both ends. * * @param float|null $min Minimum price, or null for no lower bound. * @param float|null $max Maximum price, or null for no upper bound. * @return ProductFilterBuilder */ public function priceRange($min, $max) { if ($min === null && $max === null) { return $this; } global $wpdb; $conditions = []; $values = []; if ($min !== null) { $conditions[] = 'CAST(pm.meta_value AS DECIMAL(10,2)) >= %f'; $values[] = (float) $min; } if ($max !== null) { $conditions[] = 'CAST(pm.meta_value AS DECIMAL(10,2)) <= %f'; $values[] = (float) $max; } $range_clause = implode(' AND ', $conditions); $query = $wpdb->prepare( "SELECT DISTINCT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id WHERE p.post_type = 'product' AND p.post_status IN ('publish', 'draft', 'pending', 'private') AND pm.meta_key = '_price' AND pm.meta_value IS NOT NULL AND pm.meta_value != '' AND {$range_clause}", ...$values ); $product_ids = $wpdb->get_col($query); $this->restrictToProductIds($product_ids); return $this; } /** * Filters by description status * * @param bool|null $hasDescription Whether the full description exists * @param bool|null $hasShortDescription Whether the short description exists * @return ProductFilterBuilder */ public function descriptions($hasDescription = null, $hasShortDescription = null) { if ($hasDescription === null && $hasShortDescription === null) { return $this; } global $wpdb; $conditions = ["p.post_type = 'product'", "p.post_status IN ('publish', 'draft', 'pending', 'private')"]; if ($hasDescription === true) { $conditions[] = "p.post_content IS NOT NULL AND TRIM(p.post_content) != ''"; } elseif ($hasDescription === false) { $conditions[] = "(p.post_content IS NULL OR TRIM(p.post_content) = '')"; } if ($hasShortDescription === true) { $conditions[] = "p.post_excerpt IS NOT NULL AND TRIM(p.post_excerpt) != ''"; } elseif ($hasShortDescription === false) { $conditions[] = "(p.post_excerpt IS NULL OR TRIM(p.post_excerpt) = '')"; } $where_clause = implode(' AND ', $conditions); $product_ids = $wpdb->get_col("SELECT ID FROM {$wpdb->posts} p WHERE {$where_clause}"); $this->restrictToProductIds($product_ids); $this->has_content_filters = true; return $this; } /** * Filters by tag status (with/without) * * @param bool|null $hasTags Whether the product has tags * @return ProductFilterBuilder */ public function tags($hasTags = null) { if ($hasTags === null) { return $this; } global $wpdb; if ($hasTags) { $product_ids = $wpdb->get_col(" SELECT DISTINCT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_type = 'product' AND tt.taxonomy = 'product_tag' AND p.post_status IN ('publish', 'draft', 'pending', 'private') "); } else { $this->addNegativeFilterWherePart( 'no_tags', "NOT EXISTS ( SELECT 1 FROM {$this->getTableName('term_relationships', 'term_relationships')} tr INNER JOIN {$this->getTableName('term_taxonomy', 'term_taxonomy')} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tr.object_id = p.ID AND tt.taxonomy = 'product_tag' )" ); return $this; } $this->restrictToProductIds($product_ids); $this->has_content_filters = true; return $this; } public function contentFilters(array $filters) { $parts = []; foreach ($filters as $filter) { $where_part = $this->buildContentFilterWherePart(sanitize_text_field($filter)); if ($where_part !== '') { $parts[] = '(' . $where_part . ')'; } } if (!empty($parts)) { $this->where_parts[] = '(' . implode(' OR ', $parts) . ')'; $this->has_content_filters = true; } return $this; } public static function getContentFilterWherePart(string $filter, string $product_alias = 'p'): string { $alias = preg_replace('/[^A-Za-z0-9_]/', '', $product_alias); if (!is_string($alias) || $alias === '') { $alias = 'p'; } $content = $alias . '.post_content'; $excerpt = $alias . '.post_excerpt'; switch ($filter) { case 'with_both': return "{$content} IS NOT NULL AND TRIM({$content}) != '' AND {$excerpt} IS NOT NULL AND TRIM({$excerpt}) != ''"; case 'with_full': return "{$content} IS NOT NULL AND TRIM({$content}) != '' AND ({$excerpt} IS NULL OR TRIM({$excerpt}) = '')"; case 'with_short': return "({$content} IS NULL OR TRIM({$content}) = '') AND {$excerpt} IS NOT NULL AND TRIM({$excerpt}) != ''"; case 'without_both': return "({$content} IS NULL OR TRIM({$content}) = '') AND ({$excerpt} IS NULL OR TRIM({$excerpt}) = '')"; case 'without_full': return "{$content} IS NULL OR TRIM({$content}) = ''"; case 'without_short': return "{$excerpt} IS NULL OR TRIM({$excerpt}) = ''"; case 'with_tags': return "EXISTS ( SELECT 1 FROM " . self::getStaticTableName('term_relationships', 'term_relationships') . " tr INNER JOIN " . self::getStaticTableName('term_taxonomy', 'term_taxonomy') . " tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tr.object_id = {$alias}.ID AND tt.taxonomy = 'product_tag' )"; case 'without_tags': return "NOT EXISTS ( SELECT 1 FROM " . self::getStaticTableName('term_relationships', 'term_relationships') . " tr INNER JOIN " . self::getStaticTableName('term_taxonomy', 'term_taxonomy') . " tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tr.object_id = {$alias}.ID AND tt.taxonomy = 'product_tag' )"; case 'without_seo_meta': return self::getSeoMetaGapWherePart($alias); case 'without_faq': return self::getFaqGapWherePart($alias); } return ''; } /** * Products with no custom SEO description in the active SEO plugin. * * Fails closed ("0 = 1") when no supported SEO plugin is active or its * storage is missing, so a filter that cannot be evaluated never selects * the whole catalog. * * @param string $alias Sanitized products table alias. * @return string SQL condition. */ private static function getSeoMetaGapWherePart(string $alias): string { global $wpdb; $source = SeoPluginDetector::get_description_gap_source(); if ($source === null) { return '0 = 1'; } if ($source['type'] === 'aioseo') { $table = $source['table']; return "NOT EXISTS ( SELECT 1 FROM {$table} aioseo WHERE aioseo.post_id = {$alias}.ID AND aioseo.description IS NOT NULL AND TRIM(aioseo.description) != '' )"; } $meta_key_condition = $wpdb->prepare('seo_pm.meta_key = %s', $source['key']); return "NOT EXISTS ( SELECT 1 FROM " . self::getStaticTableName('postmeta', 'postmeta') . " seo_pm WHERE seo_pm.post_id = {$alias}.ID AND {$meta_key_condition} AND seo_pm.meta_value IS NOT NULL AND TRIM(seo_pm.meta_value) != '' )"; } /** * Products with no saved FAQ: no _aipt_faq_data row, or a value of '' or '[]'. * * @param string $alias Sanitized products table alias. * @return string SQL condition. */ private static function getFaqGapWherePart(string $alias): string { return "NOT EXISTS ( SELECT 1 FROM " . self::getStaticTableName('postmeta', 'postmeta') . " faq_pm WHERE faq_pm.post_id = {$alias}.ID AND faq_pm.meta_key = '_aipt_faq_data' AND faq_pm.meta_value IS NOT NULL AND TRIM(faq_pm.meta_value) NOT IN ('', '[]') )"; } /** * Counts the products that match the filters applied so far, with the same * SQL the listing path uses. Callers must apply only content filters or * none; ID-restricting filters are honored too. * * @return int|null Null when the query failed, so a caller never mistakes a failure for zero. */ public function countProducts(): ?int { global $wpdb; $wpdb->last_error = ''; $count = $wpdb->get_var($this->buildFilteredProductCountQuery()); if (!empty($wpdb->last_error) || $count === null) { return null; } return (int) $count; } private function buildContentFilterWherePart(string $filter): string { return self::getContentFilterWherePart($filter, 'p'); } /** * Filters by featured image status * * @param bool $hasImage Whether the product has a featured image * @return ProductFilterBuilder */ public function image($hasImage) { if ($hasImage === null) { return $this; } global $wpdb; if ($hasImage) { $product_ids = $wpdb->get_col(" SELECT DISTINCT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id WHERE p.post_type = 'product' AND p.post_status IN ('publish', 'draft', 'pending', 'private') AND pm.meta_key = '_thumbnail_id' AND pm.meta_value IS NOT NULL AND pm.meta_value != '' AND pm.meta_value != '0' "); } else { $this->addNegativeFilterWherePart( 'no_image', "NOT EXISTS ( SELECT 1 FROM {$this->getTableName('postmeta', 'postmeta')} pm WHERE pm.post_id = p.ID AND pm.meta_key = '_thumbnail_id' AND pm.meta_value IS NOT NULL AND pm.meta_value != '' AND pm.meta_value != '0' )" ); return $this; } $this->restrictToProductIds($product_ids); return $this; } /** * Filters by gallery image status * * @param bool $hasGallery Whether the product has gallery images * @return ProductFilterBuilder */ public function gallery($hasGallery) { if ($hasGallery === null) { return $this; } global $wpdb; if ($hasGallery) { $product_ids = $wpdb->get_col(" SELECT DISTINCT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id WHERE p.post_type = 'product' AND p.post_status IN ('publish', 'draft', 'pending', 'private') AND pm.meta_key = '_product_image_gallery' AND pm.meta_value IS NOT NULL AND TRIM(pm.meta_value) != '' "); } else { $this->addNegativeFilterWherePart( 'no_gallery', "NOT EXISTS ( SELECT 1 FROM {$this->getTableName('postmeta', 'postmeta')} pm WHERE pm.post_id = p.ID AND pm.meta_key = '_product_image_gallery' AND pm.meta_value IS NOT NULL AND TRIM(pm.meta_value) != '' )" ); return $this; } $this->restrictToProductIds($product_ids); return $this; } /** * Filters by SKU status * * @param bool $hasSku Whether the product has a SKU * @return ProductFilterBuilder */ public function sku($hasSku) { if ($hasSku === null) { return $this; } global $wpdb; if ($hasSku) { $product_ids = $wpdb->get_col(" SELECT DISTINCT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id WHERE p.post_type = 'product' AND p.post_status IN ('publish', 'draft', 'pending', 'private') AND pm.meta_key = '_sku' AND pm.meta_value IS NOT NULL AND TRIM(pm.meta_value) != '' "); } else { $this->addNegativeFilterWherePart( 'no_sku', "NOT EXISTS ( SELECT 1 FROM {$this->getTableName('postmeta', 'postmeta')} pm WHERE pm.post_id = p.ID AND pm.meta_key = '_sku' AND pm.meta_value IS NOT NULL AND TRIM(pm.meta_value) != '' )" ); return $this; } $this->restrictToProductIds($product_ids); return $this; } /** * Filters by product type (simple, variable, grouped, external) * * @param string|array $types Product type or types * @return ProductFilterBuilder */ public function productType($types) { if (empty($types)) { return $this; } global $wpdb; $types = is_array($types) ? $types : [$types]; $types = array_map('sanitize_text_field', $types); $allowed_types = ['simple', 'variable', 'grouped', 'external']; $types = array_intersect($types, $allowed_types); if (empty($types)) { return $this; } $placeholders = implode(',', array_fill(0, count($types), '%s')); $sql = "SELECT DISTINCT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id WHERE p.post_type = 'product' AND p.post_status IN ('publish', 'draft', 'pending', 'private') AND tt.taxonomy = 'product_type' AND t.slug IN ($placeholders)"; $query = $wpdb->prepare($sql, ...$types); $product_ids = $wpdb->get_col($query); $this->restrictToProductIds($product_ids); return $this; } /** * Filters by featured status * * @param bool $isFeatured Whether the product is featured * @return ProductFilterBuilder */ public function featured($isFeatured) { if ($isFeatured === null) { return $this; } global $wpdb; if ($isFeatured) { $product_ids = $wpdb->get_col(" SELECT DISTINCT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id WHERE p.post_type = 'product' AND p.post_status IN ('publish', 'draft', 'pending', 'private') AND tt.taxonomy = 'product_visibility' AND t.slug = 'featured' "); } else { $this->addNegativeFilterWherePart( 'not_featured', "NOT EXISTS ( SELECT 1 FROM {$this->getTableName('term_relationships', 'term_relationships')} tr INNER JOIN {$this->getTableName('term_taxonomy', 'term_taxonomy')} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id INNER JOIN {$this->getTableName('terms', 'terms')} t ON tt.term_id = t.term_id WHERE tr.object_id = p.ID AND tt.taxonomy = 'product_visibility' AND t.slug = 'featured' )" ); return $this; } $this->restrictToProductIds($product_ids); return $this; } /** * Resolves the IDs of on-sale products across every status in the builder's scope. * * wc_get_product_ids_on_sale() only lists published products (and hides * out-of-stock ones when WooCommerce is set to), so drafts, pending and * private products would match neither on-sale nor not-on-sale. The lookup * table's `onsale` flag is the same source the not-on-sale branch uses. * * @return int[] Product IDs. */ private function getOnSaleProductIds(): array { if (!$this->isProductMetaLookupAvailable()) { return \function_exists('wc_get_product_ids_on_sale') ? array_map('intval', (array) wc_get_product_ids_on_sale()) : []; } global $wpdb; $lookup = $this->getProductMetaLookupTableName(); $ids = $wpdb->get_col("SELECT DISTINCT product_id FROM {$lookup} WHERE onsale = 1"); return array_map('intval', $ids ?: []); } /** * Filters by on-sale status. * * @param bool $isOnSale Whether to filter for on-sale products. * @return ProductFilterBuilder */ public function onSale($isOnSale) { if ($isOnSale === null) { return $this; } if ($isOnSale) { $this->restrictToProductIds($this->getOnSaleProductIds()); } else { if ($this->isProductMetaLookupAvailable()) { $lookup = $this->getProductMetaLookupTableName(); $this->addNegativeFilterWherePart( 'not_on_sale', "NOT EXISTS ( SELECT 1 FROM {$lookup} pml_sale WHERE pml_sale.product_id = p.ID AND pml_sale.onsale = 1 )" ); return $this; } $this->addNegativeFilterWherePart( 'not_on_sale', "NOT EXISTS ( SELECT 1 FROM {$this->getTableName('postmeta', 'postmeta')} pm WHERE pm.post_id = p.ID AND pm.meta_key = '_sale_price' AND pm.meta_value IS NOT NULL AND TRIM(pm.meta_value) != '' AND CAST(pm.meta_value AS DECIMAL(10,2)) > 0 )" ); return $this; } return $this; } /** * Filters by AI content-marking meta presence. * * @param string|null $type Content type, or null for the rollup marker. * @param string $mode Presence mode: exists or not_exists. * @return self Fluent interface. */ public function aiProcessed(?string $type, string $mode): self { $mode = strtolower(sanitize_text_field($mode)); if (!in_array($mode, ['exists', 'not_exists'], true)) { return $this; } $key = ($type === null) ? MetaKeys::ROLLUP : MetaKeys::keyForType(sanitize_text_field($type)); if ($key === null) { return $this; } $this->ai_processed_filter = [ 'key' => $key, 'mode' => $mode, ]; $this->addAiProcessedWherePart($key, $mode); return $this; } /** * Sets the ordering parameters * * @param string $orderby Order field (date, title, price, id, menu_order) * @param string $order Order direction (ASC or DESC) * @return ProductFilterBuilder */ public function orderBy($orderby, $order = 'DESC') { $order = strtoupper(sanitize_text_field($order)); $this->args['order'] = in_array($order, ['ASC', 'DESC'], true) ? $order : 'DESC'; $orderby = sanitize_text_field($orderby); if ($orderby === 'price' || $orderby === 'regular-price') { $this->price_sort_direction = $this->args['order']; $this->price_sort_regular = $orderby === 'regular-price'; $this->args['orderby'] = 'date'; } else { $this->price_sort_direction = null; $this->price_sort_regular = false; $allowed_orderby = ['date', 'title', 'id', 'name', 'modified', 'menu_order']; $this->args['orderby'] = in_array($orderby, $allowed_orderby, true) ? $orderby : 'date'; } return $this; } /** * Sets the filter logic mode (AND or OR) * * @param string $logic 'and' for intersection (default), 'or' for union * @return ProductFilterBuilder */ public function filterLogic($logic) { $logic = strtolower(sanitize_text_field($logic)); $this->filter_logic = in_array($logic, ['and', 'or'], true) ? $logic : 'and'; return $this; } /** * Sets the catalog-visibility filter mode. * * Historically the builder ALWAYS excluded products carrying the * `exclude-from-catalog` product_visibility term (WooCommerce catalog * visibility "Hidden" or "Search results only"), which silently hid those * products from the bulk CONTENT generators even though they exist. The * default is now 'all' (no visibility restriction); callers opt into the * legacy behaviour with 'visible', or target the hidden set with 'hidden'. * * @param string $visibility 'all' (default), 'visible', or 'hidden'. * @return ProductFilterBuilder */ public function catalogVisibility($visibility) { $allowed = ['all', 'visible', 'hidden']; $normalized = strtolower(sanitize_text_field((string) $visibility)); $this->catalog_visibility = in_array($normalized, $allowed, true) ? $normalized : 'all'; return $this; } /** * Clears all parameters and returns to the default state * * @return ProductFilterBuilder */ public function clear() { $this->args = [ 'limit' => 10, 'page' => 1, 'status' => self::DEFAULT_PRODUCT_STATUSES, 'orderby' => 'date', 'order' => 'DESC', 'return' => 'ids', 'paginate' => true ]; $this->tax_query = []; $this->filtered_product_ids = null; $this->price_sort_direction = null; $this->price_sort_regular = false; $this->price_sort_failed = false; $this->cursor_after = null; $this->cursor_limit = null; $this->min_id = null; $this->filter_logic = 'and'; $this->catalog_visibility = 'all'; $this->where_parts = []; $this->negative_filters = []; $this->has_content_filters = false; $this->ai_processed_filter = null; return $this; } /** * Returns the wc_get_products() arguments that were built * * @return array wc_get_products() arguments */ public function getArgs() { $args = $this->args; $tax_queries = []; if (!empty($this->tax_query)) { foreach ($this->tax_query as $query) { $tax_queries[] = $query; } } if ($this->catalog_visibility !== 'all' && function_exists('wc_get_product_visibility_term_ids')) { $product_visibility_terms = \wc_get_product_visibility_term_ids(); if (!empty($product_visibility_terms['exclude-from-catalog'])) { $tax_queries[] = [ 'taxonomy' => 'product_visibility', 'field' => 'term_taxonomy_id', 'terms' => [$product_visibility_terms['exclude-from-catalog']], 'operator' => ($this->catalog_visibility === 'hidden') ? 'IN' : 'NOT IN', ]; } } if (!empty($tax_queries)) { $args['tax_query'] = array_merge(['relation' => 'AND'], $tax_queries); } if ($this->filtered_product_ids !== null) { $filtered_ids = $this->filtered_product_ids; if ($this->price_sort_direction !== null && !empty($filtered_ids)) { $filtered_ids = $this->sortProductIdsByPrice($filtered_ids, $this->price_sort_direction); $args['orderby'] = 'post__in'; unset($args['order']); } $args['include'] = !empty($filtered_ids) ? $filtered_ids : [0]; } elseif ($this->price_sort_direction !== null) { $direction = strtoupper($this->price_sort_direction) === 'DESC' ? 'DESC' : 'ASC'; $price_meta_key = $this->price_sort_regular ? '_regular_price' : '_price'; $args['orderby'] = 'meta_value_num'; $args['meta_key'] = $price_meta_key; $args['order'] = $direction; $price_meta_query = [ [ 'key' => $price_meta_key, 'compare' => 'EXISTS', ], ]; if ( isset( $args['meta_query'] ) && is_array( $args['meta_query'] ) ) { $args['meta_query'] = array_merge( $args['meta_query'], $price_meta_query ); } else { $args['meta_query'] = $price_meta_query; } } if ($this->ai_processed_filter !== null) { $this->appendMetaQueryClause( $args, [ 'key' => $this->ai_processed_filter['key'], 'compare' => ($this->ai_processed_filter['mode'] === 'exists') ? 'EXISTS' : 'NOT EXISTS', ] ); } if ($this->cursor_after !== null) { if ($this->filtered_product_ids !== null) { sort($this->filtered_product_ids, SORT_NUMERIC); $this->filtered_product_ids = array_filter( $this->filtered_product_ids, fn($id) => $id > $this->cursor_after ); $args['include'] = !empty($this->filtered_product_ids) ? array_slice($this->filtered_product_ids, 0, $this->cursor_limit ?? 50) : [0]; } else { global $wpdb; $product_ids = $wpdb->get_col($wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_type = 'product' AND post_status IN ('publish', 'draft', 'pending', 'private') AND ID > %d ORDER BY ID ASC LIMIT %d", $this->cursor_after, $this->cursor_limit ?? 50 )); $args['include'] = !empty($product_ids) ? array_map('intval', $product_ids) : [0]; } $args['orderby'] = 'ID'; $args['order'] = 'ASC'; } return $args; } /** * Determines whether filters require the optimized direct SQL path. * * @return bool */ private function shouldUseCustomSqlQuery(): bool { return !empty($this->where_parts) || $this->min_id !== null || ($this->price_sort_direction !== null && $this->isProductMetaLookupAvailable()); } /** * Whether any negative "NOT EXISTS" predicate (no_tags / no_image / * no_gallery / no_sku / not_featured / not_on_sale) has been applied. * * These live in the private custom-SQL $where_parts path and are NOT * reflected by getArgs(), so a caller that consumes only getArgs() (e.g. a * lean keyset ID query) would silently ignore them and over-match. Callers * that cannot honor the custom-SQL path can probe this after applying * filters and refuse rather than return a wrong set. * * @return bool */ public function hasNegativeFilters(): bool { return !empty($this->negative_filters); } /** * Whether a content (description/tag) filter was applied. Like negative * filters these live ONLY in the custom-SQL $where_parts path and are NOT * reproduced by getArgs(), so a keyset selection runner that consumes only * getArgs() must probe this and refuse rather than silently over-select. * An idAfter() restriction counts as well: it also exists only in the custom SQL. * * @return bool */ public function hasContentFilters(): bool { return $this->has_content_filters || $this->min_id !== null; } /** * Loads filtered products through a direct SQL query with paginated IDs. * * @return array|null Product result or null when the legacy wc_get_products() path should be used. */ private function getProductsWithCustomSql(): ?array { global $wpdb; try { $limit = max(1, absint($this->args['limit'] ?? 10)); $page = max(1, absint($this->args['page'] ?? 1)); $offset = ($page - 1) * $limit; if ($this->cursor_limit !== null) { $limit = max(1, absint($this->cursor_limit)); $offset = 0; } $ids_query = $this->buildFilteredProductIdsQuery($limit, $offset); $count_query = $this->buildFilteredProductCountQuery(); $product_ids = $wpdb->get_col($ids_query); if (!empty($wpdb->last_error)) { throw new \RuntimeException($wpdb->last_error); } $total = (int) $wpdb->get_var($count_query); if (!empty($wpdb->last_error)) { throw new \RuntimeException($wpdb->last_error); } $product_ids = array_map('intval', $product_ids ?: []); $this->primeProductCaches($product_ids); return [ 'products' => $this->formatProductsForResponse($product_ids), 'total' => $total, 'total_pages' => $limit > 0 ? (int) ceil($total / $limit) : 0, ]; } catch (\Throwable $e) { $this->logFallbackError('Custom product filter SQL failed: ' . $e->getMessage()); return null; } } /** * Builds the paginated product ID SQL used by the optimized query path. * * @param int $limit Page size. * @param int $offset SQL offset. * @return string SQL query. */ private function buildFilteredProductIdsQuery($limit = 0, $offset = 0): string { $limit = max(0, absint($limit)); $offset = max(0, absint($offset)); $join_parts = []; $order_clause = $this->buildOrderClause($join_parts); $select = 'SELECT DISTINCT p.ID'; if ($this->price_sort_direction !== null) { $select .= ', ' . $this->buildPriceSortKeySql($this->price_sort_direction) . ' AS aipt_price_sort'; } $sql = $this->buildFilteredProductBaseQuery($select, $join_parts); $sql .= $order_clause; if ($limit > 0) { global $wpdb; $sql .= $wpdb->prepare(' LIMIT %d OFFSET %d', $limit, $offset); } return $sql; } /** * Builds the count SQL used by the optimized query path. * * @return string SQL query. */ private function buildFilteredProductCountQuery(): string { return $this->buildFilteredProductBaseQuery('SELECT COUNT(DISTINCT p.ID)'); } /** * Builds the shared SELECT/FROM/WHERE portion for optimized product filtering. * * @param string $select_clause SELECT expression. * @param array $join_parts SQL JOIN clauses. * @return string SQL query without ORDER/LIMIT. */ private function buildFilteredProductBaseQuery(string $select_clause, array $join_parts = []): string { $posts = $this->getTableName('posts', 'posts'); $where_parts = $this->buildBaseWhereParts(); $filter_group = []; foreach ($this->where_parts as $where_part) { $filter_group[] = '(' . $where_part . ')'; } $filtered_ids_where = $this->buildFilteredProductIdsWherePart(); if ($filtered_ids_where !== '') { $filter_group[] = $filtered_ids_where; } if (!empty($filter_group)) { $glue = ($this->filter_logic === 'or') ? ' OR ' : ' AND '; $where_parts[] = '(' . implode($glue, $filter_group) . ')'; } if ($this->cursor_after !== null) { global $wpdb; $where_parts[] = $wpdb->prepare('p.ID > %d', absint($this->cursor_after)); } $joins = !empty($join_parts) ? ' ' . implode(' ', $join_parts) : ''; return $select_clause . " FROM {$posts} p{$joins} WHERE " . implode(' AND ', $where_parts); } /** * Builds base product type/status conditions for direct SQL filtering. * * @return array SQL WHERE clauses. */ private function buildBaseWhereParts(): array { $parts = [ "p.post_type = 'product'", $this->buildStatusWherePart(), ]; if ($this->min_id !== null) { global $wpdb; $parts[] = $wpdb->prepare('p.ID > %d', $this->min_id); } $visibility = $this->buildCatalogVisibilityWherePart(); if ($visibility !== '') { $parts[] = $visibility; } return $parts; } /** * Builds the catalog-visibility WHERE fragment for the custom SQL path, * mirroring the wc_get_products() tax_query (field=term_taxonomy_id). * Returns '' for the default 'all' mode, when WooCommerce is unavailable, * or when the exclude-from-catalog term does not exist. Uses NOT IN for * 'visible' and IN for 'hidden'. * * @return string SQL WHERE clause (or empty string). */ private function buildCatalogVisibilityWherePart(): string { if ($this->catalog_visibility === 'all') { return ''; } if (!function_exists('wc_get_product_visibility_term_ids')) { return ''; } $terms = \wc_get_product_visibility_term_ids(); $exclude_ttid = isset($terms['exclude-from-catalog']) ? (int) $terms['exclude-from-catalog'] : 0; if ($exclude_ttid <= 0) { return ''; } global $wpdb; $operator = ($this->catalog_visibility === 'hidden') ? 'IN' : 'NOT IN'; $where = $wpdb->prepare( "p.ID {$operator} (SELECT tr.object_id FROM {$wpdb->term_relationships} tr WHERE tr.term_taxonomy_id = %d)", $exclude_ttid ); return $where; } /** * Builds the post_status SQL condition from the current builder args. * * @return string SQL WHERE clause. */ private function buildStatusWherePart(): string { global $wpdb; $statuses = $this->args['status'] ?? self::DEFAULT_PRODUCT_STATUSES; $statuses = is_array($statuses) ? $statuses : [$statuses]; $statuses = array_values(array_filter(array_map('sanitize_text_field', $statuses))); if (empty($statuses)) { $statuses = self::DEFAULT_PRODUCT_STATUSES; } if (count($statuses) === 1) { return $wpdb->prepare('p.post_status = %s', $statuses[0]); } $placeholders = implode(',', array_fill(0, count($statuses), '%s')); return $wpdb->prepare("p.post_status IN ({$placeholders})", ...$statuses); } /** * Builds a safe ID restriction for filters that still use the legacy ID list path. * * @return string SQL WHERE clause or empty string. */ private function buildFilteredProductIdsWherePart(): string { if ($this->filtered_product_ids === null) { return ''; } $filtered_ids = array_values(array_unique(array_map('intval', $this->filtered_product_ids))); if (empty($filtered_ids)) { return '1 = 0'; } return 'p.ID IN (' . implode(',', $filtered_ids) . ')'; } /** * Builds the ORDER BY clause for optimized product filtering. * * @return string SQL ORDER BY clause. */ private function buildOrderClause(array &$join_parts = []): string { if ($this->price_sort_direction !== null) { $direction = strtoupper($this->price_sort_direction) === 'DESC' ? 'DESC' : 'ASC'; if (!$this->price_sort_regular && $this->isProductMetaLookupAvailable()) { $join_parts[] = "LEFT JOIN {$this->getProductMetaLookupTableName()} pml ON pml.product_id = p.ID"; } return " ORDER BY aipt_price_sort IS NULL, aipt_price_sort {$direction}, p.ID {$direction}"; } $direction = strtoupper($this->args['order'] ?? 'DESC') === 'ASC' ? 'ASC' : 'DESC'; $orderby = $this->args['orderby'] ?? 'date'; $columns = [ 'title' => 'p.post_title', 'name' => 'p.post_title', 'id' => 'p.ID', 'ID' => 'p.ID', 'modified' => 'p.post_modified', 'menu_order' => 'p.menu_order', 'date' => 'p.post_date', ]; $column = $columns[$orderby] ?? 'p.post_date'; return " ORDER BY {$column} {$direction}, p.ID {$direction}"; } /** * Adds an optimized NOT EXISTS filter clause. * * @param string $filter_key Filter identifier for fallback diagnostics. * @param string $where_part SQL WHERE clause. * @return void */ private function addNegativeFilterWherePart(string $filter_key, string $where_part): void { $this->where_parts[] = trim($where_part); $this->negative_filters[$filter_key] = true; } /** * Adds the AI-processed meta presence clause to the optimized SQL filter group. * * @param string $key Meta key to check. * @param string $mode Presence mode: exists or not_exists. * @return void */ private function addAiProcessedWherePart(string $key, string $mode): void { global $wpdb; $operator = ($mode === 'exists') ? 'EXISTS' : 'NOT EXISTS'; $where_part = $wpdb->prepare( "{$operator} ( SELECT 1 FROM {$this->getTableName('postmeta', 'postmeta')} pm_ai WHERE pm_ai.post_id = p.ID AND pm_ai.meta_key = %s )", $key ); $this->where_parts[] = trim($where_part); if ($mode === 'not_exists') { $this->negative_filters['ai_processed'] = true; } } /** * Appends a meta query clause without clobbering an existing relation. * * @param array $args wc_get_products() arguments. * @param array $clause Meta query clause. * @return void */ private function appendMetaQueryClause(array &$args, array $clause): void { if (isset($args['meta_query']) && is_array($args['meta_query'])) { $args['meta_query'][] = $clause; return; } $args['meta_query'] = [$clause]; } /** * Checks whether WooCommerce's product meta lookup table can be used safely. * * @return bool True when the lookup table is available. */ private function isProductMetaLookupAvailable(): bool { if (self::$product_meta_lookup_exists !== null) { return self::$product_meta_lookup_exists; } if (defined('WC_VERSION') && version_compare((string) constant('WC_VERSION'), '3.6.0', '<')) { self::$product_meta_lookup_exists = false; return false; } global $wpdb; try { $table = $this->getProductMetaLookupTableName(); if (!method_exists($wpdb, 'get_var') || !method_exists($wpdb, 'prepare')) { self::$product_meta_lookup_exists = false; return false; } $found_table = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table)); self::$product_meta_lookup_exists = $found_table === $table; } catch (\Throwable $e) { $this->logFallbackError('wc_product_meta_lookup detection failed: ' . $e->getMessage()); self::$product_meta_lookup_exists = false; } return self::$product_meta_lookup_exists; } /** * Gets the WooCommerce product meta lookup table name. * * @return string Table name. */ private function getProductMetaLookupTableName(): string { return $this->getTableName('wc_product_meta_lookup', 'wc_product_meta_lookup'); } /** * Gets a wpdb table name with a prefix fallback for lightweight test spies. * * @param string $property wpdb table property. * @param string $fallback_suffix Table suffix without prefix. * @return string Table name. */ private function getTableName(string $property, string $fallback_suffix): string { global $wpdb; if (isset($wpdb->{$property}) && is_string($wpdb->{$property}) && $wpdb->{$property} !== '') { return $wpdb->{$property}; } $prefix = isset($wpdb->prefix) && is_string($wpdb->prefix) ? $wpdb->prefix : ''; return $prefix . $fallback_suffix; } private static function getStaticTableName(string $property, string $fallback_suffix): string { global $wpdb; if (isset($wpdb->{$property}) && is_string($wpdb->{$property}) && $wpdb->{$property} !== '') { return $wpdb->{$property}; } $prefix = isset($wpdb->prefix) && is_string($wpdb->prefix) ? $wpdb->prefix : ''; return $prefix . $fallback_suffix; } /** * Sorts product IDs by price at the database level * * This method is called when price sorting is active to ensure correct * sorting across all pages, not just the current page. * * @param array $product_ids Product IDs to sort * @param string $direction Sort direction (ASC or DESC) * @return array Sorted product IDs */ private function sortProductIdsByPrice($product_ids, $direction = 'ASC') { global $wpdb; if (empty($product_ids)) { return []; } $direction = strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC'; $product_ids = array_values(array_unique(array_map('intval', $product_ids))); $placeholders = implode(',', array_fill(0, count($product_ids), '%d')); $lookup_join = !$this->price_sort_regular && $this->isProductMetaLookupAvailable() ? "LEFT JOIN {$this->getProductMetaLookupTableName()} pml ON pml.product_id = p.ID" : ''; $price_key = $this->buildPriceSortKeySql($direction); $query = $wpdb->prepare( "SELECT p.ID, {$price_key} AS aipt_price_sort FROM {$this->getTableName('posts', 'posts')} p {$lookup_join} WHERE p.ID IN ({$placeholders}) ORDER BY aipt_price_sort IS NULL, aipt_price_sort {$direction}, p.ID {$direction}", ...$product_ids ); $sorted_ids = $wpdb->get_col($query); $failed = empty($sorted_ids) && !empty($wpdb->last_error); $this->price_sort_failed = $failed; if ($failed) { return $product_ids; } return array_map('intval', $sorted_ids ?: []); } /** * SQL expression for a product's active price, as WooCommerce sorts it: the lookup * row (min for ascending, max for descending) with the _price meta as fallback for * products that have no lookup row. Expects the `p` alias and, when the lookup table * is available, the `pml` alias. * * @param string $direction Sort direction (ASC or DESC). * @return string SQL expression. */ private function buildPriceSortKeySql(string $direction): string { $is_desc = strtoupper($direction) === 'DESC'; $aggregate = $is_desc ? 'MAX' : 'MIN'; $postmeta = $this->getTableName('postmeta', 'postmeta'); $meta_key = $this->price_sort_regular ? '_regular_price' : '_price'; $meta_price = "(SELECT {$aggregate}(CAST(pm_price.meta_value AS DECIMAL(10,2))) FROM {$postmeta} pm_price WHERE pm_price.post_id = p.ID AND pm_price.meta_key = '{$meta_key}' AND pm_price.meta_value <> '')"; if ($this->price_sort_regular || !$this->isProductMetaLookupAvailable()) { return $meta_price; } $lookup_column = $is_desc ? 'pml.max_price' : 'pml.min_price'; return "COALESCE({$lookup_column}, {$meta_price})"; } /** * Restrict to specific product IDs with filter logic support. * * @param array|null $product_ids New filtered product IDs to combine. * @return self Fluent interface. */ private function restrictToProductIds( $product_ids ) { if ( $product_ids === null ) { return $this; } $product_ids = array_map( 'intval', $product_ids ); if ( $this->filtered_product_ids === null ) { $this->filtered_product_ids = $product_ids; } elseif ( $this->filter_logic === 'or' ) { $this->filtered_product_ids = array_values( array_unique( array_merge( $this->filtered_product_ids, $product_ids ) ) ); } else { $this->filtered_product_ids = array_values( array_intersect( $this->filtered_product_ids, $product_ids ) ); } return $this; } /** * Invalidate every cached product-filter result by bumping the group's * `last_changed` version. Call after any write that can change which * products a filter matches (status, category, tag, SKU, price, etc.); the * persistent object cache has no portable per-group flush, so this * version-bump is the standard WP invalidation pattern. * * @return void */ public static function flush_cache(): void { wp_cache_set('last_changed', microtime(), 'aipt_products'); /** * Fires after the product filter cache was invalidated. */ do_action('aipt_product_filter_cache_flushed'); } /** * Applies the filters and fetches the products * * @return array Products, total product count and total page count */ public function getProducts() { try { $args = $this->getArgs(); $user_id = get_current_user_id(); $last_changed = wp_cache_get_last_changed('aipt_products'); $cache_key = 'aipt_products_' . $user_id . '_' . $last_changed . '_' . md5( wp_json_encode($args) . ($this->price_sort_direction ?? '') . ($this->price_sort_regular ? 'regular' : '') . wp_json_encode($this->where_parts) . $this->filter_logic . $this->catalog_visibility . ($this->min_id ?? '') ); $cached_result = wp_cache_get($cache_key, 'aipt_products'); if ($cached_result !== false) { return $cached_result; } if (isset($args['include']) && $args['include'] === [0]) { $empty_result = [ 'products' => [], 'total' => 0, 'total_pages' => 0, 'message' => 'No products found matching the selected filters.' ]; $this->cacheResult($cache_key, $empty_result); return $empty_result; } if ($this->shouldUseCustomSqlQuery()) { $custom_result = $this->getProductsWithCustomSql(); if ($custom_result !== null) { $this->cacheResult($cache_key, $custom_result); return $custom_result; } if ($this->hasNegativeFilters() || $this->hasContentFilters()) { error_log( '[AIPT] ProductFilterBuilder: custom SQL path failed with non-portable ' . 'where-part filter(s) active (negative and/or content) — refusing the ' . 'WP_Query fallback to avoid returning wrong rows.' ); return [ 'products' => [], 'total' => 0, 'total_pages' => 0, 'error' => 'Could not apply the selected filters. Please try again.', ]; } error_log( '[AIPT] ProductFilterBuilder: custom SQL path returned null; falling back to WP_Query (portable filters only, no negative predicates).' ); } $results = $this->queryProductsViaWpQuery($args); $products = []; if ($results && isset($results->products)) { $product_ids_for_page = array_map('intval', $results->products); $this->primeProductCaches($product_ids_for_page); $products = $this->formatProductsForResponse($product_ids_for_page); } $total = isset($results->total) ? (int) $results->total : 0; $max_num_pages = isset($results->max_num_pages) ? (int) $results->max_num_pages : 0; $result = [ 'products' => $products, 'total' => $total, 'total_pages' => $max_num_pages ]; $this->cacheResult($cache_key, $result); return $result; } catch (\Throwable $e) { if (defined('WP_DEBUG') && WP_DEBUG) { } return [ 'products' => [], 'total' => 0, 'total_pages' => 0, 'error' => 'An error occurred while searching products. Please try again or contact support if the issue persists.' ]; } } /** * Caches a filter result for 5 minutes, except when the price sort degraded * to an unsorted id list: the next request should retry the sort. * * @param string $cache_key Cache key. * @param array $result Result to cache. */ private function cacheResult(string $cache_key, array $result): void { if ($this->price_sort_failed) { return; } wp_cache_set($cache_key, $result, 'aipt_products', 300); } /** * Run the product listing as a raw WP_Query on post_type=product, mirroring * the WooCommerce admin Products list + Bear Bulk Editor, and return the same * shape wc_get_products( paginate ) does so getProducts() stays unchanged. * * The builder pre-resolves category/tag/search/negative filters into an * explicit `include` allow-list, so the only args reaching here are status, * ordering, pagination, an optional include list, and (for price sort / * catalog visibility) meta_query/tax_query — each maps 1:1 onto WP_Query. * * @param array $args wc_get_products-style args from getArgs(). * @return object { products: int[], total: int, max_num_pages: int } */ private function queryProductsViaWpQuery(array $args): object { $orderby = isset($args['orderby']) ? (string) $args['orderby'] : 'date'; $orderby = ['id' => 'ID', 'name' => 'title'][$orderby] ?? $orderby; $wp_args = [ 'post_type' => 'product', 'post_status' => isset($args['status']) ? $args['status'] : self::DEFAULT_PRODUCT_STATUSES, 'posts_per_page' => isset($args['limit']) ? (int) $args['limit'] : 20, 'paged' => isset($args['page']) ? max(1, (int) $args['page']) : 1, 'orderby' => $orderby, 'order' => isset($args['order']) ? $args['order'] : 'DESC', 'fields' => 'ids', 'no_found_rows' => false, 'ignore_sticky_posts' => true, ]; if (isset($args['include'])) { $wp_args['post__in'] = !empty($args['include']) ? $args['include'] : [0]; } if (isset($args['tax_query'])) { $wp_args['tax_query'] = $args['tax_query']; } if (isset($args['meta_query'])) { $wp_args['meta_query'] = $args['meta_query']; } if (isset($args['meta_key'])) { $wp_args['meta_key'] = $args['meta_key']; } $query = new \WP_Query($wp_args); return (object) [ 'products' => array_map('intval', $query->posts), 'total' => (int) $query->found_posts, 'max_num_pages' => (int) $query->max_num_pages, ]; } /** * Primes post, meta, taxonomy, and thumbnail-related caches for a product page. * * @param array $product_ids Product IDs for the current page. * @return void */ private function primeProductCaches(array $product_ids): void { $product_ids = array_values(array_filter(array_map('intval', $product_ids))); if (empty($product_ids)) { return; } \update_meta_cache('post', $product_ids); $term_cache_function = 'update_object_term_cache'; if (\function_exists($term_cache_function)) { \call_user_func($term_cache_function, $product_ids, 'product'); } $post_cache_function = '_prime_post_caches'; if (\function_exists($post_cache_function)) { \call_user_func($post_cache_function, $product_ids, true, true); } } /** * Formats product IDs into the response shape expected by the admin UI. * * @param array $product_ids Product IDs. * @return array Formatted products. */ private function formatProductsForResponse(array $product_ids): array { $products = []; foreach ($product_ids as $product_id) { $product = \wc_get_product($product_id); if (!$product) { continue; } $categories = \wc_get_product_terms($product_id, 'product_cat', ['fields' => 'names']); $categories = \is_wp_error($categories) ? [] : $categories; $decoded_categories = array_map([self::class, 'decodeHtmlEntities'], $categories); $tags = \wc_get_product_terms($product_id, 'product_tag', ['fields' => 'names']); $tags = \is_wp_error($tags) ? [] : $tags; $decoded_tags = array_map([self::class, 'decodeHtmlEntities'], $tags); $description = (string) $product->get_description(); $short_description = (string) $product->get_short_description(); $has_description = !empty(trim($description)); $has_short_description = !empty(trim($short_description)); $image_id = $product->get_image_id(); $image_url = $image_id ? \wp_get_attachment_image_url($image_id, 'thumbnail') : \wc_placeholder_img_src('thumbnail'); $price = $product->get_price(); $numeric_price = $price !== '' ? (float) $price : 0; $date_created = $product->get_date_created(); $date_modified = $product->get_date_modified(); $products[] = [ 'id' => $product_id, 'name' => $product->get_name(), 'slug' => rawurldecode((string) \get_post_field('post_name', $product_id)), 'status' => $product->get_status(), 'stock_status' => $product->get_stock_status(), 'categories' => $decoded_categories, 'tags' => $decoded_tags, 'hasDescription' => $has_description, 'hasShortDescription' => $has_short_description, 'description' => $description, 'shortDescription' => $short_description, 'image' => $image_url, 'dateCreated' => $date_created ? $date_created->date('Y-m-d H:i:s') : '', 'dateModified' => $date_modified ? $date_modified->date('Y-m-d H:i:s') : '', 'price' => $price, 'numeric_price' => $numeric_price, 'sku' => $product->get_sku() ]; } return $products; } /** * Logs fallback-only errors without interrupting production requests. * * @param string $message Error message. * @return void */ private function logFallbackError(string $message): void { if (defined('WP_DEBUG') && WP_DEBUG) { } } /** * HTML entities decode helper * * @param string $string * @return string */ private static function decodeHtmlEntities($string) { return html_entity_decode($string, ENT_QUOTES, 'UTF-8'); } }