woocommerce-pos / includes / Sync / Product_Search.php

Product_Search.php in WCPOS – Point of Sale (POS) plugin for WooCommerce 1.10.19, at includes/Sync/Product_Search.php

229 lines 7.6 KB

Raw Download Zip

1	<?php
2	/**
3	* Shared product search SQL filters.
4	*
5	* @package WCPOS\WooCommercePOS\Sync
6	*/
7
8	namespace WCPOS\WooCommercePOS\Sync;
9
10	/**
11	* Keeps v1 and v2 product search fields identical.
12	*/
13	final class Product_Search {
14
15	/**
16	* Search product titles, SKUs, and the configured barcode field.
17	*
18	* @param string $search Search SQL.
19	* @param array $q Query variables.
20	* @param array $rule Declared search carriers and cap.
21	* @return string
22	*/
23	public static function posts_search( string $search, array $q, array $rule ): string {
24	global $wpdb;
25	$phrase = $q['wcpos_search_phrase'] ?? null;
26	if ( empty( $search ) && null === $phrase ) {
27	return $search;
28	}
29	$terms = null !== $phrase
30	? Collection_Rules::search_terms( $phrase )
31	: (array) $q['search_terms'];
32	if ( null !== $phrase && array() === $terms ) {
33	return ' AND 1=0 ';
34	}
35	// Like WP_Query::parse_search() and WooCommerce's search_products(), collapse over-long lists to the phrase.
36	if ( null !== $phrase && $rule['term_cap'] < \count( $terms ) ) {
37	$terms = array( $phrase );
38	}
39	$n = ! empty( $q['exact'] ) ? '' : '%';
40	$meta_fields = $rule['posts']['meta'];
41	$meta_placeholders = implode( ', ', array_fill( 0, \count( $meta_fields ), '%s' ) );
42	$search_conditions = array();
43	foreach ( $terms as $term ) {
44	$term = $n . $wpdb->esc_like( $term ) . $n;
45	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names come from $wpdb; $meta_placeholders is a generated list of %s placeholders, and the keys themselves are passed to prepare() as arguments.
46	$search_conditions[] = $wpdb->prepare(
47	"( {$wpdb->posts}.post_title LIKE %s OR EXISTS (
48	SELECT 1 FROM {$wpdb->postmeta} AS wcpos_search_meta WHERE wcpos_search_meta.post_id = {$wpdb->posts}.ID AND wcpos_search_meta.meta_key IN ($meta_placeholders) AND wcpos_search_meta.meta_value LIKE %s
49	) )",
50	array_merge( array( $term ), $meta_fields, array( $term ) )
51	);
52	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
53	}
54
55	if ( ! empty( $search_conditions ) ) {
56	$search = ' AND (' . implode( ' AND ', $search_conditions ) . ') ';
57	if ( ! is_user_logged_in() ) {
58	$search .= " AND ($wpdb->posts.post_password = '') ";
59	}
60	}
61	return $search;
62	}
63
64	/**
65	* Join product meta while searching.
66	*
67	* @param string $join JOIN SQL.
68	* @param array $q Query variables.
69	* @return string
70	*/
71	public static function posts_join( string $join, array $q ): string {
72	global $wpdb;
73	if ( self::is_searching( $q ) && false === strpos( $join, 'pm1' ) ) {
74	$join .= " LEFT JOIN {$wpdb->postmeta} pm1 ON {$wpdb->posts}.ID = pm1.post_id ";
75	}
76	return $join;
77	}
78
79	/**
80	* Group product search results after joining meta.
81	*
82	* @param string $groupby GROUP BY SQL.
83	* @param array $q Query variables.
84	* @return string
85	*/
86	public static function posts_groupby( string $groupby, array $q ): string {
87	global $wpdb;
88	if ( self::is_searching( $q ) ) {
89	$groupby = "{$wpdb->posts}.ID";
90	}
91	return $groupby;
92	}
93
94	/**
95	* Rank exact SKU or barcode matches ahead of substring matches.
96	*
97	* @param string $orderby ORDER BY SQL.
98	* @param array $q Query variables.
99	* @param array $rule Declared search carriers and cap.
100	* @return string
101	*/
102	public static function posts_orderby( string $orderby, array $q, array $rule ): string {
103	global $wpdb;
104	if ( ! self::is_searching( $q ) ) {
105	return $orderby;
106	}
107	$keys = $rule['posts']['meta'];
108	return $wpdb->prepare(
109	'MIN(CASE WHEN pm1.meta_key IN (' . implode( ', ', array_fill( 0, count( $keys ), '%s' ) ) . ') AND pm1.meta_value = %s THEN 0 ELSE 1 END) ASC',
110	array_merge( $keys, array( trim( (string) ( $q['wcpos_search_phrase'] ?? $q['s'] ) ) ) )
111	) . ', ' . ( '' === trim( $orderby ) ? "{$wpdb->posts}.ID DESC" : $orderby );
112	}
113
114	/**
115	* Direct variations use literal terms with over-cap collapse and the existing EXISTS SQL.
116	*
117	* @param string $search Search SQL.
118	* @param array $q Query variables.
119	* @param array $rule Declared search carriers and cap.
120	* @return string
121	*/
122	public static function variation_posts_search( string $search, array $q, array $rule ) {
123	global $wpdb;
124
125	$phrase = $q['wcpos_search_phrase'] ?? null;
126	if ( empty( $search ) && null === $phrase ) {
127	return $search; // skip processing - no search term in query.
128	}
129	$search_terms = null !== $phrase
130	? Collection_Rules::search_terms( $phrase )
131	: (array) $q['search_terms'];
132	if ( null !== $phrase && array() === $search_terms ) {
133	return ' AND 1=0 ';
134	}
135	if ( null !== $phrase && $rule['term_cap'] < \count( $search_terms ) ) {
136	$search_terms = array( $phrase );
137	}
138
139	$n = ! empty( $q['exact'] ) ? '' : '%';
140
141	// Meta fields to search.
142	$meta_fields = $rule['posts']['meta'];
143
144	$meta_placeholders = implode( ', ', array_fill( 0, \count( $meta_fields ), '%s' ) );
145	$search_conditions = array();
146
147	foreach ( $search_terms as $term ) {
148	$term = $n . $wpdb->esc_like( $term ) . $n;
149
150	// Search in meta fields.
151	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names come from $wpdb; $meta_placeholders is a generated list of %s placeholders, and the keys themselves are passed to prepare() as arguments.
152	$search_conditions[] = $wpdb->prepare(
153	"EXISTS (
154	SELECT 1 FROM {$wpdb->postmeta} AS wcpos_search_meta WHERE wcpos_search_meta.post_id = {$wpdb->posts}.ID AND wcpos_search_meta.meta_key IN ($meta_placeholders) AND wcpos_search_meta.meta_value LIKE %s
155	)",
156	array_merge( $meta_fields, array( $term ) )
157	);
158	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
159	}
160
161	if ( ! empty( $search_conditions ) ) {
162	$search = ' AND (' . implode( ' AND ', $search_conditions ) . ') ';
163	if ( ! is_user_logged_in() ) {
164	$search .= " AND ($wpdb->posts.post_password = '') ";
165	}
166	}
167
168	return $search;
169	}
170
171	/**
172	* Variation search uses only meta carriers; an exact SKU lookup takes precedence.
173	*
174	* @param array $args Query arguments.
175	* @param string $search Search text.
176	* @param string $sku Exact SKU lookup.
177	* @param array $rule Declared search carriers and cap.
178	* @return array
179	*/
180	public static function variation_args( array $args, string $search, string $sku, array $rule ): array {
181	if ( '' !== $sku ) {
182	unset( $args['s'] );
183	}
184	if ( '' !== $search && '' === $sku ) {
185	unset( $args['s'] );
186	$args['wcpos_variation_search'] = true;
187	$carriers = array( 'relation' => 'AND' );
188	foreach ( Collection_Rules::search_terms( trim( $search ) ) as $term ) {
189	$term_carriers = array( 'relation' => 'OR' );
190	foreach ( $rule['posts']['meta'] as $key ) {
191	$term_carriers[] = array(
192	'key' => $key,
193	'value' => $term,
194	'compare' => 'LIKE',
195	);
196	}
197	$carriers[] = $term_carriers;
198	}
199	if ( 1 < \count( $carriers ) ) {
200	$args['meta_query'] = empty( $args['meta_query'] ) ? array() : $args['meta_query'];
201	$args['meta_query'][] = $carriers;
202	}
203	}
204	return $args;
205	}
206
207	/**
208	* De-duplicate matching variation meta rows.
209	*
210	* @param string $groupby GROUP BY SQL.
211	* @param array $q Query variables.
212	* @return string
213	*/
214	public static function variation_groupby( string $groupby, array $q ): string {
215	global $wpdb;
216	return ! empty( $q['wcpos_variation_search'] ) ? "{$wpdb->posts}.ID" : $groupby;
217	}
218
219	/**
220	* Whether the query carries a search term. Not empty(): the literal term "0" is a search.
221	*
222	* @param array $q Query variables.
223	* @return bool
224	*/
225	private static function is_searching( array $q ): bool {
226	return '' !== (string) ( $q['wcpos_search_phrase'] ?? $q['s'] ?? '' );
227	}
228	}
229