QueryFilters.php — WooCommerce 10.9.0

woocommerce / src / Blocks / QueryFilters.php

woocommerce / src / Blocks Last commit date

AI 1 year ago AIContent 1 year ago Assets 3 weeks ago BlockTypes 1 week ago Domain 1 month ago Images 1 year ago Integrations 2 years ago Patterns 6 months ago Payments 5 months ago Registry 2 years ago SharedStores 1 month ago Shipping 7 months ago Templates 1 month ago Utils 3 weeks ago Assets.php 2 years ago AssetsController.php 3 weeks ago BlockPatterns.php 7 months ago BlockTemplatesController.php 7 months ago BlockTemplatesRegistry.php 10 months ago BlockTypesController.php 3 weeks ago DependencyDetection.php 5 months ago InboxNotifications.php 2 years ago Installer.php 1 month ago Library.php 2 years ago Options.php 2 years ago Package.php 1 year ago QueryFilters.php 7 months ago TemplateOptions.php 1 year ago

QueryFilters.php

528 lines

1	<?php
2	declare( strict_types = 1 );
3
4	namespace Automattic\WooCommerce\Blocks;
5
6	use WC_Tax;
7	use Automattic\WooCommerce\Internal\ProductAttributesLookup\LookupDataStore;
8
9	/**
10	* Process the query data for filtering purposes.
11	*/
12	final class QueryFilters {
13	/**
14	* Initialization method.
15	*
16	* @internal
17	*/
18	public function init() {}
19
20	/**
21	* Filter the posts clauses of the main query to support global filters.
22	*
23	* @param array $args Query args.
24	* @param \WP_Query $wp_query WP_Query object.
25	* @return array
26	*/
27	public function main_query_filter( $args, $wp_query ) {
28	if (
29	! $wp_query->is_main_query() \|\|
30	'product_query' !== $wp_query->get( 'wc_query' )
31	) {
32	return $args;
33	}
34
35	if ( $wp_query->get( 'filter_stock_status' ) ) {
36	$args = $this->stock_filter_clauses( $args, $wp_query );
37	}
38
39	return $args;
40	}
41
42	/**
43	* Add conditional query clauses based on the filter params in query vars.
44	*
45	* @param array $args Query args.
46	* @param \WP_Query $wp_query WP_Query object.
47	* @return array
48	*/
49	public function add_query_clauses( $args, $wp_query ) {
50	$args = $this->stock_filter_clauses( $args, $wp_query );
51	$args = $this->price_filter_clauses( $args, $wp_query );
52	$args = $this->attribute_filter_clauses( $args, $wp_query );
53
54	return $args;
55	}
56
57	/**
58	* Get price data for current products.
59	*
60	* @param array $query_vars The WP_Query arguments.
61	* @return object
62	*/
63	public function get_filtered_price( $query_vars ) {
64	global $wpdb;
65
66	add_filter( 'posts_clauses', array( $this, 'add_query_clauses' ), 10, 2 );
67	add_filter( 'posts_pre_query', '__return_empty_array' );
68
69	$query_vars['no_found_rows'] = true;
70	$query_vars['posts_per_page'] = -1;
71	$query_vars['fields'] = 'ids';
72	$query = new \WP_Query();
73	$query->query( $query_vars );
74	$product_query_sql = $query->request;
75
76	remove_filter( 'posts_clauses', array( $this, 'add_query_clauses' ), 10 );
77	remove_filter( 'posts_pre_query', '__return_empty_array' );
78
79	$price_filter_sql = "
80	SELECT min( min_price ) as min_price, MAX( max_price ) as max_price
81	FROM {$wpdb->wc_product_meta_lookup}
82	WHERE product_id IN ( {$product_query_sql} )
83	";
84
85	return $wpdb->get_row( $price_filter_sql ); // phpcs:ignore
86	}
87
88	/**
89	* Get stock status counts for the current products.
90	*
91	* @param array $query_vars The WP_Query arguments.
92	* @return array status=>count pairs.
93	*/
94	public function get_stock_status_counts( $query_vars ) {
95	global $wpdb;
96	$stock_status_options = array_map( 'esc_sql', array_keys( wc_get_product_stock_status_options() ) );
97
98	add_filter( 'posts_clauses', array( $this, 'add_query_clauses' ), 10, 2 );
99	add_filter( 'posts_pre_query', '__return_empty_array' );
100
101	$query_vars['no_found_rows'] = true;
102	$query_vars['posts_per_page'] = -1;
103	$query_vars['fields'] = 'ids';
104	$query = new \WP_Query();
105	$result = $query->query( $query_vars );
106	$product_query_sql = $query->request;
107
108	remove_filter( 'posts_clauses', array( $this, 'add_query_clauses' ), 10 );
109	remove_filter( 'posts_pre_query', '__return_empty_array' );
110
111	$stock_status_counts = array();
112
113	foreach ( $stock_status_options as $status ) {
114	$stock_status_count_sql = $this->generate_stock_status_count_query( $status, $product_query_sql, $stock_status_options );
115
116	$result = $wpdb->get_row( $stock_status_count_sql ); // phpcs:ignore
117	$stock_status_counts[ $status ] = $result->status_count;
118	}
119
120	return $stock_status_counts;
121	}
122
123	/**
124	* Get rating counts for the current products.
125	*
126	* @param array $query_vars The WP_Query arguments.
127	* @return array rating=>count pairs.
128	*/
129	public function get_rating_counts( $query_vars ) {
130	global $wpdb;
131
132	add_filter( 'posts_clauses', array( $this, 'add_query_clauses' ), 10, 2 );
133	add_filter( 'posts_pre_query', '__return_empty_array' );
134
135	$query_vars['no_found_rows'] = true;
136	$query_vars['posts_per_page'] = -1;
137	$query_vars['fields'] = 'ids';
138	$query = new \WP_Query();
139	$query->query( $query_vars );
140	$product_query_sql = $query->request;
141
142	remove_filter( 'posts_clauses', array( $this, 'add_query_clauses' ), 10 );
143	remove_filter( 'posts_pre_query', '__return_empty_array' );
144
145	$rating_count_sql = "
146	SELECT COUNT( DISTINCT product_id ) as product_count, ROUND( average_rating, 0 ) as rounded_average_rating
147	FROM {$wpdb->wc_product_meta_lookup}
148	WHERE product_id IN ( {$product_query_sql} )
149	AND average_rating > 0
150	GROUP BY rounded_average_rating
151	ORDER BY rounded_average_rating DESC
152	";
153
154	$results = $wpdb->get_results( $rating_count_sql ); // phpcs:ignore
155
156	return array_map( 'absint', wp_list_pluck( $results, 'product_count', 'rounded_average_rating' ) );
157	}
158
159	/**
160	* Get attribute counts for the current products.
161	*
162	* @param array $query_vars The WP_Query arguments.
163	* @param string $attribute_to_count Attribute taxonomy name.
164	* @return array termId=>count pairs.
165	*/
166	public function get_attribute_counts( $query_vars, $attribute_to_count ) {
167	global $wpdb;
168
169	add_filter( 'posts_clauses', array( $this, 'add_query_clauses' ), 10, 2 );
170	add_filter( 'posts_pre_query', '__return_empty_array' );
171
172	$query_vars['no_found_rows'] = true;
173	$query_vars['posts_per_page'] = -1;
174	$query_vars['fields'] = 'ids';
175	$query = new \WP_Query();
176	$result = $query->query( $query_vars );
177	$product_query_sql = $query->request;
178
179	remove_filter( 'posts_clauses', array( $this, 'add_query_clauses' ), 10 );
180	remove_filter( 'posts_pre_query', '__return_empty_array' );
181
182	$attributes_to_count = esc_sql( wc_sanitize_taxonomy_name( $attribute_to_count ) );
183
184	$attribute_count_sql = "SELECT COUNT(DISTINCT posts.ID) as term_count, terms.term_id as term_count_id
185	FROM {$wpdb->posts} AS posts
186	INNER JOIN {$wpdb->term_relationships} AS term_relationships ON posts.ID = term_relationships.object_id
187	INNER JOIN {$wpdb->term_taxonomy} AS term_taxonomy ON term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id
188	INNER JOIN {$wpdb->terms} AS terms ON term_taxonomy.term_id = terms.term_id
189	WHERE posts.ID IN ( {$product_query_sql} )
190	AND term_taxonomy.taxonomy IN ('{$attributes_to_count}')
191	AND posts.post_status = 'publish'
192	AND posts.post_type = 'product'
193	GROUP BY terms.term_id
194	ORDER BY terms.name ASC";
195
196	$results = $wpdb->get_results( $attribute_count_sql ); // phpcs:ignore
197
198	return array_map( 'absint', wp_list_pluck( $results, 'term_count', 'term_count_id' ) );
199	}
200
201	/**
202	* Add query clauses for stock filter.
203	*
204	* @param array $args Query args.
205	* @param \WP_Query $wp_query WP_Query object.
206	* @return array
207	*/
208	private function stock_filter_clauses( $args, $wp_query ) {
209	if ( ! $wp_query->get( 'filter_stock_status' ) ) {
210	return $args;
211	}
212
213	$args['join'] = $this->append_product_sorting_table_join( $args['join'] );
214	$args['where'] .= ' AND wc_product_meta_lookup.stock_status IN (\'' . implode( '\',\'', array_map( 'esc_sql', explode( ',', $wp_query->get( 'filter_stock_status' ) ) ) ) . '\')';
215
216	return $args;
217	}
218
219	/**
220	* Add query clauses for price filter.
221	*
222	* @param array $args Query args.
223	* @param \WP_Query $wp_query WP_Query object.
224	* @return array
225	*/
226	private function price_filter_clauses( $args, $wp_query ) {
227	if ( ! $wp_query->get( 'min_price' ) && ! $wp_query->get( 'max_price' ) ) {
228	return $args;
229	}
230
231	global $wpdb;
232
233	$adjust_for_taxes = $this->adjust_price_filters_for_displayed_taxes();
234	$args['join'] = $this->append_product_sorting_table_join( $args['join'] );
235
236	if ( $wp_query->get( 'min_price' ) ) {
237	$min_price_filter = intval( $wp_query->get( 'min_price' ) );
238
239	if ( $adjust_for_taxes ) {
240	$args['where'] .= $this->get_price_filter_query_for_displayed_taxes( $min_price_filter, 'max_price', '>=' );
241	} else {
242	$args['where'] .= $wpdb->prepare( ' AND wc_product_meta_lookup.max_price >= %f ', $min_price_filter );
243	}
244	}
245
246	if ( $wp_query->get( 'max_price' ) ) {
247	$max_price_filter = intval( $wp_query->get( 'max_price' ) );
248
249	if ( $adjust_for_taxes ) {
250	$args['where'] .= $this->get_price_filter_query_for_displayed_taxes( $max_price_filter, 'min_price', '<=' );
251	} else {
252	$args['where'] .= $wpdb->prepare( ' AND wc_product_meta_lookup.min_price <= %f ', $max_price_filter );
253	}
254	}
255
256	return $args;
257	}
258
259	/**
260	* Join wc_product_meta_lookup to posts if not already joined.
261	*
262	* @param string $sql SQL join.
263	* @return string
264	*/
265	private function append_product_sorting_table_join( $sql ) {
266	global $wpdb;
267
268	if ( ! strstr( $sql, 'wc_product_meta_lookup' ) ) {
269	$sql .= " LEFT JOIN {$wpdb->wc_product_meta_lookup} wc_product_meta_lookup ON $wpdb->posts.ID = wc_product_meta_lookup.product_id ";
270	}
271	return $sql;
272	}
273
274	/**
275	* Generate calculate query by stock status.
276	*
277	* @param string $status status to calculate.
278	* @param string $product_query_sql product query for current filter state.
279	* @param array $stock_status_options available stock status options.
280	*
281	* @return false\|string
282	*/
283	private function generate_stock_status_count_query( $status, $product_query_sql, $stock_status_options ) {
284	if ( ! in_array( $status, $stock_status_options, true ) ) {
285	return false;
286	}
287	global $wpdb;
288	$status = esc_sql( $status );
289	return "
290	SELECT COUNT( DISTINCT posts.ID ) as status_count
291	FROM {$wpdb->posts} as posts
292	INNER JOIN {$wpdb->postmeta} as postmeta ON posts.ID = postmeta.post_id
293	AND postmeta.meta_key = '_stock_status'
294	AND postmeta.meta_value = '{$status}'
295	WHERE posts.ID IN ( {$product_query_sql} )
296	";
297	}
298
299	/**
300	* Get query for price filters when dealing with displayed taxes.
301	*
302	* @param float $price_filter Price filter to apply.
303	* @param string $column Price being filtered (min or max).
304	* @param string $operator Comparison operator for column.
305	* @return string Constructed query.
306	*/
307	private function get_price_filter_query_for_displayed_taxes( $price_filter, $column = 'min_price', $operator = '>=' ) {
308	global $wpdb;
309
310	// Select only used tax classes to avoid unwanted calculations.
311	$product_tax_classes = array_filter( $wpdb->get_col( "SELECT DISTINCT tax_class FROM {$wpdb->wc_product_meta_lookup};" ) );
312
313	if ( empty( $product_tax_classes ) ) {
314	return '';
315	}
316
317	$or_queries = array();
318
319	// We need to adjust the filter for each possible tax class and combine the queries into one.
320	foreach ( $product_tax_classes as $tax_class ) {
321	$adjusted_price_filter = $this->adjust_price_filter_for_tax_class( $price_filter, $tax_class );
322	$or_queries[] = $wpdb->prepare(
323	'( wc_product_meta_lookup.tax_class = %s AND wc_product_meta_lookup.`' . esc_sql( $column ) . '` ' . esc_sql( $operator ) . ' %f )',
324	$tax_class,
325	$adjusted_price_filter
326	);
327	}
328
329	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
330	return $wpdb->prepare(
331	' AND (
332	wc_product_meta_lookup.tax_status = "taxable" AND ( 0=1 OR ' . implode( ' OR ', $or_queries ) . ')
333	OR ( wc_product_meta_lookup.tax_status != "taxable" AND wc_product_meta_lookup.`' . esc_sql( $column ) . '` ' . esc_sql( $operator ) . ' %f )
334	) ',
335	$price_filter
336	);
337	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
338	}
339
340	/**
341	* If price filters need adjustment to work with displayed taxes, this returns true.
342	*
343	* This logic is used when prices are stored in the database differently to how they are being displayed, with regards
344	* to taxes.
345	*
346	* @return boolean
347	*/
348	private function adjust_price_filters_for_displayed_taxes() {
349	$display = get_option( 'woocommerce_tax_display_shop' );
350	$database = wc_prices_include_tax() ? 'incl' : 'excl';
351
352	return $display !== $database;
353	}
354
355	/**
356	* Adjusts a price filter based on a tax class and whether or not the amount includes or excludes taxes.
357	*
358	* This calculation logic is based on `wc_get_price_excluding_tax` and `wc_get_price_including_tax` in core.
359	*
360	* @param float $price_filter Price filter amount as entered.
361	* @param string $tax_class Tax class for adjustment.
362	* @return float
363	*/
364	private function adjust_price_filter_for_tax_class( $price_filter, $tax_class ) {
365	$tax_display = get_option( 'woocommerce_tax_display_shop' );
366	$tax_rates = WC_Tax::get_rates( $tax_class );
367	$base_tax_rates = WC_Tax::get_base_tax_rates( $tax_class );
368
369	// If prices are shown incl. tax, we want to remove the taxes from the filter amount to match prices stored excl. tax.
370	if ( 'incl' === $tax_display ) {
371	/**
372	* Filters if taxes should be removed from locations outside the store base location.
373	*
374	* The woocommerce_adjust_non_base_location_prices filter can stop base taxes being taken off when dealing
375	* with out of base locations. e.g. If a product costs 10 including tax, all users will pay 10
376	* regardless of location and taxes.
377	*
378	* @since 2.6.0
379	*
380	* @internal Matches filter name in WooCommerce core.
381	*
382	* @param boolean $adjust_non_base_location_prices True by default.
383	* @return boolean
384	*/
385	$taxes = apply_filters( 'woocommerce_adjust_non_base_location_prices', true ) ? WC_Tax::calc_tax( $price_filter, $base_tax_rates, true ) : WC_Tax::calc_tax( $price_filter, $tax_rates, true );
386	return $price_filter - array_sum( $taxes );
387	}
388
389	// If prices are shown excl. tax, add taxes to match the prices stored in the DB.
390	$taxes = WC_Tax::calc_tax( $price_filter, $tax_rates, false );
391
392	return $price_filter + array_sum( $taxes );
393	}
394
395	/**
396	* Get attribute lookup table name.
397	*
398	* @return string
399	*/
400	private function get_lookup_table_name() {
401	return wc_get_container()->get( LookupDataStore::class )->get_lookup_table_name();
402	}
403
404	/**
405	* Add query clauses for attribute filter.
406	*
407	* @param array $args Query args.
408	* @param \WP_Query $wp_query WP_Query object.
409	* @return array
410	*/
411	private function attribute_filter_clauses( $args, $wp_query ) {
412	$chosen_attributes = $this->get_chosen_attributes( $wp_query->query_vars );
413
414	if ( empty( $chosen_attributes ) ) {
415	return $args;
416	}
417
418	global $wpdb;
419
420	// The extra derived table ("SELECT product_or_parent_id FROM") is needed for performance
421	// (causes the filtering subquery to be executed only once).
422	$clause_root = " {$wpdb->posts}.ID IN ( SELECT product_or_parent_id FROM (";
423	if ( 'yes' === get_option( 'woocommerce_hide_out_of_stock_items' ) ) {
424	$in_stock_clause = ' AND in_stock = 1';
425	} else {
426	$in_stock_clause = '';
427	}
428
429	$attribute_ids_for_and_filtering = array();
430
431	foreach ( $chosen_attributes as $taxonomy => $data ) {
432	$all_terms = get_terms(
433	array(
434	'taxonomy' => $taxonomy,
435	'hide_empty' => false,
436	)
437	);
438	$term_ids_by_slug = wp_list_pluck( $all_terms, 'term_id', 'slug' );
439	$term_ids_to_filter_by = array_values( array_intersect_key( $term_ids_by_slug, array_flip( $data['terms'] ) ) );
440	$term_ids_to_filter_by = array_map( 'absint', $term_ids_to_filter_by );
441	$term_ids_to_filter_by_list = '(' . join( ',', $term_ids_to_filter_by ) . ')';
442	$is_and_query = 'and' === $data['query_type'];
443
444	$count = count( $term_ids_to_filter_by );
445
446	if ( 0 !== $count ) {
447	if ( $is_and_query && $count > 1 ) {
448	$attribute_ids_for_and_filtering = array_merge( $attribute_ids_for_and_filtering, $term_ids_to_filter_by );
449	} else {
450	$clauses[] = "
451	{$clause_root}
452	SELECT product_or_parent_id
453	FROM {$this->get_lookup_table_name()} lt
454	WHERE term_id in {$term_ids_to_filter_by_list}
455	{$in_stock_clause}
456	)";
457	}
458	}
459	}
460
461	if ( ! empty( $attribute_ids_for_and_filtering ) ) {
462	$count = count( $attribute_ids_for_and_filtering );
463	$term_ids_to_filter_by_list = '(' . join( ',', $attribute_ids_for_and_filtering ) . ')';
464	$clauses[] = "
465	{$clause_root}
466	SELECT product_or_parent_id
467	FROM {$this->get_lookup_table_name()} lt
468	WHERE is_variation_attribute=0
469	{$in_stock_clause}
470	AND term_id in {$term_ids_to_filter_by_list}
471	GROUP BY product_id
472	HAVING COUNT(product_id)={$count}
473	UNION
474	SELECT product_or_parent_id
475	FROM {$this->get_lookup_table_name()} lt
476	WHERE is_variation_attribute=1
477	{$in_stock_clause}
478	AND term_id in {$term_ids_to_filter_by_list}
479	)";
480	}
481
482	if ( ! empty( $clauses ) ) {
483	// "temp" is needed because the extra derived tables require an alias.
484	$args['where'] .= ' AND (' . join( ' temp ) AND ', $clauses ) . ' temp ))';
485	} elseif ( ! empty( $chosen_attributes ) ) {
486	$args['where'] .= ' AND 1=0';
487	}
488
489	return $args;
490	}
491
492	/**
493	* Get an array of attributes and terms selected from query arguments.
494	*
495	* @param array $query_vars The WP_Query arguments.
496	* @return array
497	*/
498	private function get_chosen_attributes( $query_vars ) {
499	$chosen_attributes = array();
500
501	if ( empty( $query_vars ) ) {
502	return $chosen_attributes;
503	}
504
505	foreach ( $query_vars as $key => $value ) {
506	if ( 0 === strpos( $key, 'filter_' ) ) {
507	if ( ! is_string( $value ) ) {
508	continue;
509	}
510
511	$attribute = wc_sanitize_taxonomy_name( str_replace( 'filter_', '', $key ) );
512	$taxonomy = wc_attribute_taxonomy_name( $attribute );
513	$filter_terms = ! empty( $value ) ? explode( ',', wc_clean( wp_unslash( $value ) ) ) : array();
514
515	if ( empty( $filter_terms ) \|\| ! taxonomy_exists( $taxonomy ) \|\| ! wc_attribute_taxonomy_id_by_name( $attribute ) ) {
516	continue;
517	}
518
519	$query_type = ! empty( $query_vars[ 'query_type_' . $attribute ] ) && in_array( $query_vars[ 'query_type_' . $attribute ], array( 'and', 'or' ), true ) ? wc_clean( wp_unslash( $query_vars[ 'query_type_' . $attribute ] ) ) : '';
520	$chosen_attributes[ $taxonomy ]['terms'] = array_map( 'sanitize_title', $filter_terms ); // Ensures correct encoding.
521	$chosen_attributes[ $taxonomy ]['query_type'] = $query_type ? $query_type : 'and';
522	}
523	}
524
525	return $chosen_attributes;
526	}
527	}
528