mlsimport / includes / standalone / class-mlsimport-standalone-query.php

class-mlsimport-standalone-query.php in MLSImport: IDX Plugin & MLS Plugin for Real Estate Listings trunk, at includes/standalone/class-mlsimport-standalone-query.php

476 lines 18.6 KB

Raw Download Zip

1	<?php
2	/**
3	* Standalone (theme_id 990) listings WHERE-clause builder.
4	*
5	* Translates consumer filter params into a parameterized WHERE fragment for the
6	* mlsimport_listings table plus the args array for $wpdb->prepare(). Pure: only
7	* placeholders touch the SQL, values go in args. No WordPress, no DB.
8	*
9	* @package Mlsimport
10	*/
11
12	if ( ! defined( 'ABSPATH' ) ) {
13	exit;
14	}
15
16	/**
17	* Builds parameterized WHERE clauses for the standalone listings query.
18	*/
19	class Mlsimport_Standalone_Query {
20
21	/**
22	* Numeric range filters: param => [ column-with-operator placeholder, cast ].
23	* Evaluated in this order, so the produced clause order is deterministic.
24	*/
25	private const NUMERIC = array(
26	'price_min' => array( 'price >= %d', 'int' ),
27	'price_max' => array( 'price <= %d', 'int' ),
28	'beds' => array( 'bedrooms >= %d', 'int' ),
29	'baths' => array( 'bathrooms >= %f', 'float' ),
30	'sqft_min' => array( 'living_area >= %d', 'int' ),
31	'sqft_max' => array( 'living_area <= %d', 'int' ),
32	'lot_min' => array( 'lot_size >= %d', 'int' ),
33	'lot_max' => array( 'lot_size <= %d', 'int' ),
34	'year_min' => array( 'year_built >= %d', 'int' ),
35	'year_max' => array( 'year_built <= %d', 'int' ),
36	'hoa_max' => array( 'hoa_fee <= %d', 'int' ),
37	'dom_max' => array( 'days_on_market <= %d', 'int' ),
38	'garage_min' => array( 'garage_spaces >= %d', 'int' ),
39	'stories' => array( 'stories >= %d', 'int' ),
40	);
41
42	/**
43	* Exact-match string filters: param => column. Evaluated after numerics.
44	* listing_id is the public MLS number; the column's collation makes the match
45	* case-insensitive, so "tb8541851" finds "TB8541851".
46	*/
47	private const EQUALITY = array(
48	'subdivision' => 'subdivision',
49	'listing_id' => 'listing_id',
50	);
51
52	/**
53	* Date lower-bound filters: param => "column >= %s". The value binds as a
54	* string in MySQL DATETIME form (the column's format).
55	*/
56	private const DATE_MIN = array(
57	'list_date_min' => 'list_date >= %s',
58	);
59
60	/**
61	* Columns the result set may be sorted by. orderby values outside this set
62	* are ignored (orderby cannot be a bound parameter, so it must be whitelisted).
63	*/
64	private const SORTABLE = array(
65	'price',
66	'bedrooms',
67	'bathrooms',
68	'living_area',
69	'lot_size',
70	'year_built',
71	'list_date',
72	'days_on_market',
73	'modification_timestamp',
74	);
75
76	/**
77	* Friendly sort tokens => (column, direction). This is the vocabulary every
78	* surface speaks: the Design Settings "Order by" option, the results toolbar's
79	* Sort select, and the page blocks' sort control. A column direction is not
80	* expressible as a bare column name, which is why "Price" alone cannot mean
81	* anything useful — the token carries both halves. Raw column names still pass
82	* through order_clause unchanged (SORTABLE guards them), so pre-existing saved
83	* values keep working.
84	*/
85	public const SORT_TOKENS = array(
86	'price_high' => array( 'price', 'desc' ),
87	'price_low' => array( 'price', 'asc' ),
88	'newest' => array( 'list_date', 'desc' ),
89	'oldest' => array( 'list_date', 'asc' ),
90	'newest_edited' => array( 'modification_timestamp', 'desc' ),
91	'oldest_edited' => array( 'modification_timestamp', 'asc' ),
92	'beds_high' => array( 'bedrooms', 'desc' ),
93	'beds_low' => array( 'bedrooms', 'asc' ),
94	'baths_high' => array( 'bathrooms', 'desc' ),
95	'baths_low' => array( 'bathrooms', 'asc' ),
96	);
97
98	/**
99	* Multi-value IN filters: param => column. A scalar is treated as one value.
100	*/
101	private const IN_LIST = array(
102	'status' => 'status',
103	'property_type' => 'property_type',
104	'listing_type' => 'listing_type',
105	'city' => 'city',
106	'state' => 'state',
107	'zip' => 'zip',
108	);
109
110	/**
111	* Build a WHERE fragment + prepare() args from consumer filter params.
112	*
113	* @param array $params Filter params.
114	* @return array{where:string,args:array} Parameterized clause and its args.
115	*/
116	public static function build_where( array $params ): array {
117	// Accumulate one placeholder clause per active filter, with its bound value
118	// pushed to $args in lockstep so clause order and arg order always agree.
119	$clauses = array();
120	$args = array();
121
122	// Numeric range filters: emit the fixed "column OP %d/%f" clause and bind the
123	// value cast to the type the column expects (int or float).
124	foreach ( self::NUMERIC as $param => $spec ) {
125	// Skip any range bound the consumer did not submit.
126	if ( ! isset( $params[ $param ] ) ) {
127	continue;
128	}
129	// $spec is [ clause-with-placeholder, cast ]; the clause SQL is fixed.
130	list( $sql, $cast ) = $spec;
131	$clauses[] = $sql;
132	// Bind the value coerced to the declared type.
133	$args[] = 'int' === $cast ? (int) $params[ $param ] : (float) $params[ $param ];
134	}
135
136	// Exact-match string filters: "column = %s" with the value bound as a string.
137	foreach ( self::EQUALITY as $param => $column ) {
138	// Skip filters not submitted.
139	if ( ! isset( $params[ $param ] ) ) {
140	continue;
141	}
142	$clauses[] = $column . ' = %s';
143	$args[] = (string) $params[ $param ];
144	}
145
146	foreach ( self::DATE_MIN as $param => $sql ) {
147	$value = isset( $params[ $param ] ) ? trim( (string) $params[ $param ] ) : '';
148	// Only a real Y-m-d date may enter a DATETIME comparison. The inspector
149	// preset is a free-text box, so it can hold anything; a non-date like 'r'
150	// would make MySQL raise "Incorrect DATETIME value" and dump the query on
151	// the page. The live path guards identically (includes/live/live-params.php).
152	if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $value ) ) {
153	continue;
154	}
155	$clauses[] = $sql;
156	$args[] = $value;
157	}
158
159	// Multi-value IN filters: "column IN (%s, %s, ...)" with one placeholder per
160	// submitted value. A scalar is normalized to a one-element list.
161	foreach ( self::IN_LIST as $param => $column ) {
162	// Skip filters not submitted.
163	if ( ! isset( $params[ $param ] ) ) {
164	continue;
165	}
166	// Coerce a scalar or array of values into a flat, re-indexed list.
167	$values = array_values( (array) $params[ $param ] );
168	// An empty list contributes nothing.
169	if ( empty( $values ) ) {
170	continue;
171	}
172	// Build the IN clause with exactly count($values) %s placeholders.
173	$clauses[] = $column . ' IN (' . implode( ', ', array_fill( 0, count( $values ), '%s' ) ) . ')';
174	// Bind each value (as a string) in the same order as the placeholders.
175	foreach ( $values as $value ) {
176	$args[] = (string) $value;
177	}
178	}
179
180	// Full-text keyword search against the FULLTEXT search_text column, only when
181	// a non-blank query was supplied. Bound as one %s in BOOLEAN mode.
182	if ( isset( $params['keywords'] ) && '' !== trim( (string) $params['keywords'] ) ) {
183	$clauses[] = 'MATCH(search_text) AGAINST (%s IN BOOLEAN MODE)';
184	$args[] = (string) $params['keywords'];
185	}
186
187	// Bounding-box (viewport) filter: applied only when all four corners are present,
188	// so a partial box can't produce a half-open range.
189	$bbox = array( 'lat_min', 'lat_max', 'lng_min', 'lng_max' );
190	if ( count( array_intersect_key( $params, array_flip( $bbox ) ) ) === count( $bbox ) ) {
191	$clauses[] = 'latitude BETWEEN %f AND %f AND longitude BETWEEN %f AND %f';
192	$args[] = (float) $params['lat_min'];
193	$args[] = (float) $params['lat_max'];
194	$args[] = (float) $params['lng_min'];
195	$args[] = (float) $params['lng_max'];
196	}
197
198	// Draw-on-map polygon search: keep only listings whose point falls inside the
199	// drawn shape. The polygon's own bounding box (indexed lat/lng range) prunes
200	// first, then ST_Contains refines — the canonical two-step geo filter.
201	if ( isset( $params['polygon'] ) ) {
202	$polygon = self::polygon_clause( (string) $params['polygon'] );
203	if ( null !== $polygon ) {
204	$clauses[] = $polygon['sql'];
205	$args = array_merge( $args, $polygon['args'] );
206	}
207	}
208
209	// Internal post-id restriction (Saved Search daily alerts, ADR-0018): keep only
210	// the given listings, on top of every other filter. It is NOT a visitor filter —
211	// the request whitelist (Shortcodes::FILTER_ATTS) does not carry it, so it can
212	// only be set by plugin code. Present-but-empty means "no listing qualifies"
213	// and must match nothing; dropping the clause would match everything instead.
214	if ( isset( $params['post_ids'] ) ) {
215	$post_ids = array_values( array_filter( array_map( 'intval', (array) $params['post_ids'] ) ) );
216	if ( empty( $post_ids ) ) {
217	$clauses[] = '1=0';
218	} else {
219	// One %d placeholder per id, bound in the same order.
220	$clauses[] = 'L.post_id IN (' . implode( ', ', array_fill( 0, count( $post_ids ), '%d' ) ) . ')';
221	$args = array_merge( $args, $post_ids );
222	}
223	}
224
225	// No active filters — emit the always-true placeholder so callers can always
226	// splice "WHERE {where}" without special-casing an empty filter set.
227	if ( empty( $clauses ) ) {
228	return array(
229	'where' => '1=1',
230	'args' => $args,
231	);
232	}
233
234	// AND every collected clause together into the final fragment.
235	return array(
236	'where' => implode( ' AND ', $clauses ),
237	'args' => $args,
238	);
239	}
240
241	/**
242	* Translate a drawn polygon into a parameterized point-in-polygon clause.
243	*
244	* Input is the front-end's compact ring: "lng lat,lng lat,..." (one vertex per
245	* comma group, space-separated). Every coordinate is cast to float, so the WKT
246	* string we assemble can only ever contain numbers — it then binds as a single
247	* %s placeholder, leaving no path for injection. Fewer than three valid vertices
248	* yields null (not a polygon). The ring is auto-closed for ST_GeomFromText.
249	*
250	* @param string $raw Compact "lng lat,lng lat,..." ring from the map.
251	* @return array{sql:string,args:array}\|null Clause + args, or null when invalid.
252	*/
253	private static function polygon_clause( string $raw ): ?array {
254	// Parse the compact ring: split on commas into vertices, then each vertex on
255	// whitespace into its two coordinates. Only well-formed numeric pairs are kept.
256	$points = array();
257	foreach ( explode( ',', $raw ) as $pair ) {
258	$xy = preg_split( '/\s+/', trim( $pair ) );
259	// A valid vertex is exactly two numeric tokens; cast both to float.
260	if ( is_array( $xy ) && 2 === count( $xy ) && is_numeric( $xy[0] ) && is_numeric( $xy[1] ) ) {
261	$points[] = array( (float) $xy[0], (float) $xy[1] ); // [ lng, lat ].
262	}
263	}
264	// Fewer than three vertices cannot describe a polygon.
265	if ( count( $points ) < 3 ) {
266	return null;
267	}
268
269	// Close the ring (POLYGON requires first vertex == last).
270	if ( $points[0] !== end( $points ) ) {
271	$points[] = $points[0];
272	}
273
274	// Split the vertices into their longitude/latitude columns for the bounding box.
275	$lngs = array_column( $points, 0 );
276	$lats = array_column( $points, 1 );
277	// Assemble the WKT POLYGON literal from the (numeric-only) vertices.
278	$wkt = 'POLYGON((' . implode( ',', array_map(
279	static function ( $p ) {
280	return $p[0] . ' ' . $p[1];
281	},
282	$points
283	) ) . '))';
284
285	return array(
286	'sql' => 'longitude BETWEEN %f AND %f AND latitude BETWEEN %f AND %f AND ST_Contains(ST_GeomFromText(%s), POINT(longitude, latitude))',
287	'args' => array( min( $lngs ), max( $lngs ), min( $lats ), max( $lats ), $wkt ),
288	);
289	}
290
291	/**
292	* The tiebreaker that makes every ordering a TOTAL one.
293	*
294	* A sort column alone is not a total order: rows that tie on it have no defined
295	* relative order, and MySQL is free to return that tied group differently for each
296	* LIMIT/OFFSET query. Paginating such a sort duplicates and drops rows — the same
297	* listing comes back on page 1 AND page 2 while its tie-partner appears on neither,
298	* so a visitor sees some properties twice and some never at all. post_id is unique,
299	* so appending it makes the order total and every page a real slice.
300	*/
301	private const TIEBREAK = 'L.post_id DESC';
302
303	/**
304	* Build a safe ordering fragment, always ending in the unique-column tiebreaker so
305	* the result is a TOTAL order and LIMIT/OFFSET paging is stable. orderby must be a
306	* whitelisted column (it cannot be a bound parameter); anything else falls back to
307	* the tiebreaker alone — an unordered paginated grid is non-deterministic in exactly
308	* the same way a tied one is, so "no sort chosen" still needs a defined order.
309	*
310	* @param array $params Filter params (orderby, order).
311	* @return string Ordering fragment (never empty).
312	*/
313	public static function order_clause( array $params ): string {
314	// No sort requested by the visitor/block: fall back to the site's configured
315	// default order. This is the single point every surface funnels through, so
316	// the setting applies to the archive, taxonomy archives, AJAX repaints and
317	// page blocks alike without each one re-reading the option.
318	$orderby = isset( $params['orderby'] ) && ! is_array( $params['orderby'] ) ? (string) $params['orderby'] : '';
319	// Featured listings lead the list ONLY when nobody picked a sort (issue #288).
320	// An explicit sort always wins: a featured $2M house on top of a "price low to
321	// high" list would look broken. Decided here, before the site default below
322	// fills $orderby in, because the site default is not a visitor's choice.
323	$featured_first = '' === $orderby && self::featured_first();
324	if ( '' === $orderby ) {
325	$orderby = self::default_sort();
326	}
327
328	// A friendly token carries its own direction and wins over any order param;
329	// a raw column name still takes its direction from order (default DESC).
330	if ( isset( self::SORT_TOKENS[ $orderby ] ) ) {
331	list( $orderby, $params['order'] ) = self::SORT_TOKENS[ $orderby ];
332	}
333
334	$order = '';
335	if ( in_array( $orderby, self::SORTABLE, true ) ) {
336	$dir = isset( $params['order'] ) && 'asc' === strtolower( (string) $params['order'] ) ? 'ASC' : 'DESC';
337	$order = $orderby . ' ' . $dir;
338	}
339
340	// Advanced hook: a callback may rewrite the ordering, but the result is
341	// re-validated against the column whitelist below so SQL stays safe.
342	if ( function_exists( 'apply_filters' ) ) {
343	/** Filter the ORDER BY fragment (re-validated after). @since 6.3 */
344	$order = (string) apply_filters( 'mlsimport_listings_order', $order, $params );
345	}
346
347	$order = self::validate_order( $order );
348
349	// Final shape: [featured DESC,] <configured sort>, <tiebreaker>. Inside the
350	// featured group (and after it) the normal sort order still applies.
351	$order = '' === $order ? self::TIEBREAK : $order . ', ' . self::TIEBREAK;
352
353	return $featured_first ? 'L.featured DESC, ' . $order : $order;
354	}
355
356	/**
357	* Whether lists with no chosen sort put featured listings first: the Design
358	* Settings → General → "Show featured listings first" option (default yes).
359	*
360	* @return bool
361	*/
362	public static function featured_first(): bool {
363	$on = ! function_exists( 'mlsimport_standalone_option' ) \|\| 'no' !== mlsimport_standalone_option( 'featured_first', 'yes' );
364
365	if ( function_exists( 'apply_filters' ) ) {
366	/** Filter whether featured listings lead an unsorted list. @since 7.2.2 */
367	$on = (bool) apply_filters( 'mlsimport_featured_first', $on );
368	}
369
370	return $on;
371	}
372
373	/**
374	* Resolve query sort params to the friendly token used by the public toolbar.
375	*
376	* The query contract intentionally accepts both modern tokens (``newest``)
377	* and legacy block attributes (``orderby=list_date`` + ``order=desc``).
378	* The toolbar select can only submit tokens, so a raw saved pair must be
379	* translated before rendering; otherwise the browser falls back to its empty
380	* Default option and the next AJAX page loses the saved ordering (issue #314).
381	* A raw pair with no equivalent public option returns an empty token rather
382	* than inventing a value the select and request handler do not understand.
383	*
384	* @param array $params Filter params (orderby, order).
385	* @return string Friendly sort token, or '' when no public option represents it.
386	*/
387	public static function sort_token( array $params ): string {
388	$orderby = isset( $params['orderby'] ) && ! is_array( $params['orderby'] ) ? (string) $params['orderby'] : '';
389	if ( '' === $orderby ) {
390	// Nobody chose a sort. With "featured first" on, the select must show its
391	// empty Default option: whatever it shows is sent back as an EXPLICIT sort
392	// on the next AJAX repaint, and an explicit sort drops featured listings
393	// off the top (page 2, any filter change). The empty value is skipped by
394	// the JS, so the server keeps applying featured + the configured order.
395	return self::featured_first() ? '' : self::default_sort();
396	}
397	if ( isset( self::SORT_TOKENS[ $orderby ] ) ) {
398	return $orderby;
399	}
400
401	$direction = isset( $params['order'] ) && ! is_array( $params['order'] ) && 'asc' === strtolower( (string) $params['order'] ) ? 'asc' : 'desc';
402	foreach ( self::SORT_TOKENS as $token => $sort ) {
403	if ( $orderby === $sort[0] && $direction === $sort[1] ) {
404	return $token;
405	}
406	}
407
408	return '';
409	}
410
411	/**
412	* The sort token configured in Design Settings → General → "Order by", or ''
413	* when set to Default (no ordering beyond the tiebreaker). Also read by the
414	* results toolbar so the Sort select shows the configured order as selected.
415	*
416	* @return string Sort token, or '' for none.
417	*/
418	public static function default_sort(): string {
419	if ( ! function_exists( 'mlsimport_standalone_option' ) ) {
420	return '';
421	}
422	$token = (string) mlsimport_standalone_option( 'order_by', '' );
423
424	return isset( self::SORT_TOKENS[ $token ] ) ? $token : '';
425	}
426
427	/**
428	* Whitelist-validate a "<column> <ASC\|DESC>" fragment; anything else yields ''.
429	*
430	* @param string $order Candidate ordering fragment.
431	* @return string
432	*/
433	private static function validate_order( string $order ): string {
434	$order = trim( $order );
435	if ( '' === $order ) {
436	return '';
437	}
438	$parts = preg_split( '/\s+/', $order );
439	$column = $parts[0];
440	$dir = isset( $parts[1] ) ? strtoupper( $parts[1] ) : 'DESC';
441	if ( count( $parts ) > 2 \|\| ! in_array( $column, self::SORTABLE, true ) \|\| ! in_array( $dir, array( 'ASC', 'DESC' ), true ) ) {
442	return '';
443	}
444	return $column . ' ' . $dir;
445	}
446
447	/**
448	* Build a "LIMIT %d OFFSET %d" fragment + args from limit/page. No limit
449	* (limit absent or <= 0) yields an empty fragment (the caller returns all rows).
450	*
451	* @param array $params Filter params (limit, page).
452	* @return array{sql:string,args:array}
453	*/
454	public static function limit_clause( array $params ): array {
455	$limit = isset( $params['limit'] ) ? (int) $params['limit'] : 0;
456	if ( function_exists( 'apply_filters' ) ) {
457	/** Filter the page size (results per page). @since 6.3 */
458	$limit = (int) apply_filters( 'mlsimport_listings_per_page', $limit, $params );
459	}
460	if ( $limit <= 0 ) {
461	return array(
462	'sql' => '',
463	'args' => array(),
464	);
465	}
466
467	$page = isset( $params['page'] ) ? max( 1, (int) $params['page'] ) : 1;
468	$offset = ( $page - 1 ) * $limit;
469
470	return array(
471	'sql' => 'LIMIT %d OFFSET %d',
472	'args' => array( $limit, $offset ),
473	);
474	}
475	}
476