| 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 |
*/ |
| 45 |
private const EQUALITY = array( |
| 46 |
'subdivision' => 'subdivision', |
| 47 |
); |
| 48 |
|
| 49 |
/** |
| 50 |
* Date lower-bound filters: param => "column >= %s". The value binds as a |
| 51 |
* string in MySQL DATETIME form (the column's format). |
| 52 |
*/ |
| 53 |
private const DATE_MIN = array( |
| 54 |
'list_date_min' => 'list_date >= %s', |
| 55 |
); |
| 56 |
|
| 57 |
/** |
| 58 |
* Columns the result set may be sorted by. orderby values outside this set |
| 59 |
* are ignored (orderby cannot be a bound parameter, so it must be whitelisted). |
| 60 |
*/ |
| 61 |
private const SORTABLE = array( |
| 62 |
'price', |
| 63 |
'bedrooms', |
| 64 |
'bathrooms', |
| 65 |
'living_area', |
| 66 |
'lot_size', |
| 67 |
'year_built', |
| 68 |
'list_date', |
| 69 |
'days_on_market', |
| 70 |
'modification_timestamp', |
| 71 |
); |
| 72 |
|
| 73 |
/** |
| 74 |
* Friendly sort tokens => (column, direction). This is the vocabulary every |
| 75 |
* surface speaks: the Design Settings "Order by" option, the results toolbar's |
| 76 |
* Sort select, and the page blocks' sort control. A column direction is not |
| 77 |
* expressible as a bare column name, which is why "Price" alone cannot mean |
| 78 |
* anything useful — the token carries both halves. Raw column names still pass |
| 79 |
* through order_clause unchanged (SORTABLE guards them), so pre-existing saved |
| 80 |
* values keep working. |
| 81 |
*/ |
| 82 |
public const SORT_TOKENS = array( |
| 83 |
'price_high' => array( 'price', 'desc' ), |
| 84 |
'price_low' => array( 'price', 'asc' ), |
| 85 |
'newest' => array( 'list_date', 'desc' ), |
| 86 |
'oldest' => array( 'list_date', 'asc' ), |
| 87 |
'newest_edited' => array( 'modification_timestamp', 'desc' ), |
| 88 |
'oldest_edited' => array( 'modification_timestamp', 'asc' ), |
| 89 |
'beds_high' => array( 'bedrooms', 'desc' ), |
| 90 |
'beds_low' => array( 'bedrooms', 'asc' ), |
| 91 |
'baths_high' => array( 'bathrooms', 'desc' ), |
| 92 |
'baths_low' => array( 'bathrooms', 'asc' ), |
| 93 |
); |
| 94 |
|
| 95 |
/** |
| 96 |
* Multi-value IN filters: param => column. A scalar is treated as one value. |
| 97 |
*/ |
| 98 |
private const IN_LIST = array( |
| 99 |
'status' => 'status', |
| 100 |
'property_type' => 'property_type', |
| 101 |
'listing_type' => 'listing_type', |
| 102 |
'city' => 'city', |
| 103 |
'state' => 'state', |
| 104 |
'zip' => 'zip', |
| 105 |
); |
| 106 |
|
| 107 |
/** |
| 108 |
* Build a WHERE fragment + prepare() args from consumer filter params. |
| 109 |
* |
| 110 |
* @param array $params Filter params. |
| 111 |
* @return array{where:string,args:array} Parameterized clause and its args. |
| 112 |
*/ |
| 113 |
public static function build_where( array $params ): array { |
| 114 |
// Accumulate one placeholder clause per active filter, with its bound value |
| 115 |
// pushed to $args in lockstep so clause order and arg order always agree. |
| 116 |
$clauses = array(); |
| 117 |
$args = array(); |
| 118 |
|
| 119 |
// Numeric range filters: emit the fixed "column OP %d/%f" clause and bind the |
| 120 |
// value cast to the type the column expects (int or float). |
| 121 |
foreach ( self::NUMERIC as $param => $spec ) { |
| 122 |
// Skip any range bound the consumer did not submit. |
| 123 |
if ( ! isset( $params[ $param ] ) ) { |
| 124 |
continue; |
| 125 |
} |
| 126 |
// $spec is [ clause-with-placeholder, cast ]; the clause SQL is fixed. |
| 127 |
list( $sql, $cast ) = $spec; |
| 128 |
$clauses[] = $sql; |
| 129 |
// Bind the value coerced to the declared type. |
| 130 |
$args[] = 'int' === $cast ? (int) $params[ $param ] : (float) $params[ $param ]; |
| 131 |
} |
| 132 |
|
| 133 |
// Exact-match string filters: "column = %s" with the value bound as a string. |
| 134 |
foreach ( self::EQUALITY as $param => $column ) { |
| 135 |
// Skip filters not submitted. |
| 136 |
if ( ! isset( $params[ $param ] ) ) { |
| 137 |
continue; |
| 138 |
} |
| 139 |
$clauses[] = $column . ' = %s'; |
| 140 |
$args[] = (string) $params[ $param ]; |
| 141 |
} |
| 142 |
|
| 143 |
foreach ( self::DATE_MIN as $param => $sql ) { |
| 144 |
$value = isset( $params[ $param ] ) ? trim( (string) $params[ $param ] ) : ''; |
| 145 |
// Only a real Y-m-d date may enter a DATETIME comparison. The inspector |
| 146 |
// preset is a free-text box, so it can hold anything; a non-date like 'r' |
| 147 |
// would make MySQL raise "Incorrect DATETIME value" and dump the query on |
| 148 |
// the page. The live path guards identically (includes/live/live-params.php). |
| 149 |
if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $value ) ) { |
| 150 |
continue; |
| 151 |
} |
| 152 |
$clauses[] = $sql; |
| 153 |
$args[] = $value; |
| 154 |
} |
| 155 |
|
| 156 |
// Multi-value IN filters: "column IN (%s, %s, ...)" with one placeholder per |
| 157 |
// submitted value. A scalar is normalized to a one-element list. |
| 158 |
foreach ( self::IN_LIST as $param => $column ) { |
| 159 |
// Skip filters not submitted. |
| 160 |
if ( ! isset( $params[ $param ] ) ) { |
| 161 |
continue; |
| 162 |
} |
| 163 |
// Coerce a scalar or array of values into a flat, re-indexed list. |
| 164 |
$values = array_values( (array) $params[ $param ] ); |
| 165 |
// An empty list contributes nothing. |
| 166 |
if ( empty( $values ) ) { |
| 167 |
continue; |
| 168 |
} |
| 169 |
// Build the IN clause with exactly count($values) %s placeholders. |
| 170 |
$clauses[] = $column . ' IN (' . implode( ', ', array_fill( 0, count( $values ), '%s' ) ) . ')'; |
| 171 |
// Bind each value (as a string) in the same order as the placeholders. |
| 172 |
foreach ( $values as $value ) { |
| 173 |
$args[] = (string) $value; |
| 174 |
} |
| 175 |
} |
| 176 |
|
| 177 |
// Full-text keyword search against the FULLTEXT search_text column, only when |
| 178 |
// a non-blank query was supplied. Bound as one %s in BOOLEAN mode. |
| 179 |
if ( isset( $params['keywords'] ) && '' !== trim( (string) $params['keywords'] ) ) { |
| 180 |
$clauses[] = 'MATCH(search_text) AGAINST (%s IN BOOLEAN MODE)'; |
| 181 |
$args[] = (string) $params['keywords']; |
| 182 |
} |
| 183 |
|
| 184 |
// Bounding-box (viewport) filter: applied only when all four corners are present, |
| 185 |
// so a partial box can't produce a half-open range. |
| 186 |
$bbox = array( 'lat_min', 'lat_max', 'lng_min', 'lng_max' ); |
| 187 |
if ( count( array_intersect_key( $params, array_flip( $bbox ) ) ) === count( $bbox ) ) { |
| 188 |
$clauses[] = 'latitude BETWEEN %f AND %f AND longitude BETWEEN %f AND %f'; |
| 189 |
$args[] = (float) $params['lat_min']; |
| 190 |
$args[] = (float) $params['lat_max']; |
| 191 |
$args[] = (float) $params['lng_min']; |
| 192 |
$args[] = (float) $params['lng_max']; |
| 193 |
} |
| 194 |
|
| 195 |
// Draw-on-map polygon search: keep only listings whose point falls inside the |
| 196 |
// drawn shape. The polygon's own bounding box (indexed lat/lng range) prunes |
| 197 |
// first, then ST_Contains refines — the canonical two-step geo filter. |
| 198 |
if ( isset( $params['polygon'] ) ) { |
| 199 |
$polygon = self::polygon_clause( (string) $params['polygon'] ); |
| 200 |
if ( null !== $polygon ) { |
| 201 |
$clauses[] = $polygon['sql']; |
| 202 |
$args = array_merge( $args, $polygon['args'] ); |
| 203 |
} |
| 204 |
} |
| 205 |
|
| 206 |
// No active filters — emit the always-true placeholder so callers can always |
| 207 |
// splice "WHERE {where}" without special-casing an empty filter set. |
| 208 |
if ( empty( $clauses ) ) { |
| 209 |
return array( |
| 210 |
'where' => '1=1', |
| 211 |
'args' => $args, |
| 212 |
); |
| 213 |
} |
| 214 |
|
| 215 |
// AND every collected clause together into the final fragment. |
| 216 |
return array( |
| 217 |
'where' => implode( ' AND ', $clauses ), |
| 218 |
'args' => $args, |
| 219 |
); |
| 220 |
} |
| 221 |
|
| 222 |
/** |
| 223 |
* Translate a drawn polygon into a parameterized point-in-polygon clause. |
| 224 |
* |
| 225 |
* Input is the front-end's compact ring: "lng lat,lng lat,..." (one vertex per |
| 226 |
* comma group, space-separated). Every coordinate is cast to float, so the WKT |
| 227 |
* string we assemble can only ever contain numbers — it then binds as a single |
| 228 |
* %s placeholder, leaving no path for injection. Fewer than three valid vertices |
| 229 |
* yields null (not a polygon). The ring is auto-closed for ST_GeomFromText. |
| 230 |
* |
| 231 |
* @param string $raw Compact "lng lat,lng lat,..." ring from the map. |
| 232 |
* @return array{sql:string,args:array}|null Clause + args, or null when invalid. |
| 233 |
*/ |
| 234 |
private static function polygon_clause( string $raw ): ?array { |
| 235 |
// Parse the compact ring: split on commas into vertices, then each vertex on |
| 236 |
// whitespace into its two coordinates. Only well-formed numeric pairs are kept. |
| 237 |
$points = array(); |
| 238 |
foreach ( explode( ',', $raw ) as $pair ) { |
| 239 |
$xy = preg_split( '/\s+/', trim( $pair ) ); |
| 240 |
// A valid vertex is exactly two numeric tokens; cast both to float. |
| 241 |
if ( is_array( $xy ) && 2 === count( $xy ) && is_numeric( $xy[0] ) && is_numeric( $xy[1] ) ) { |
| 242 |
$points[] = array( (float) $xy[0], (float) $xy[1] ); // [ lng, lat ]. |
| 243 |
} |
| 244 |
} |
| 245 |
// Fewer than three vertices cannot describe a polygon. |
| 246 |
if ( count( $points ) < 3 ) { |
| 247 |
return null; |
| 248 |
} |
| 249 |
|
| 250 |
// Close the ring (POLYGON requires first vertex == last). |
| 251 |
if ( $points[0] !== end( $points ) ) { |
| 252 |
$points[] = $points[0]; |
| 253 |
} |
| 254 |
|
| 255 |
// Split the vertices into their longitude/latitude columns for the bounding box. |
| 256 |
$lngs = array_column( $points, 0 ); |
| 257 |
$lats = array_column( $points, 1 ); |
| 258 |
// Assemble the WKT POLYGON literal from the (numeric-only) vertices. |
| 259 |
$wkt = 'POLYGON((' . implode( ',', array_map( |
| 260 |
static function ( $p ) { |
| 261 |
return $p[0] . ' ' . $p[1]; |
| 262 |
}, |
| 263 |
$points |
| 264 |
) ) . '))'; |
| 265 |
|
| 266 |
return array( |
| 267 |
'sql' => 'longitude BETWEEN %f AND %f AND latitude BETWEEN %f AND %f AND ST_Contains(ST_GeomFromText(%s), POINT(longitude, latitude))', |
| 268 |
'args' => array( min( $lngs ), max( $lngs ), min( $lats ), max( $lats ), $wkt ), |
| 269 |
); |
| 270 |
} |
| 271 |
|
| 272 |
/** |
| 273 |
* The tiebreaker that makes every ordering a TOTAL one. |
| 274 |
* |
| 275 |
* A sort column alone is not a total order: rows that tie on it have no defined |
| 276 |
* relative order, and MySQL is free to return that tied group differently for each |
| 277 |
* LIMIT/OFFSET query. Paginating such a sort duplicates and drops rows — the same |
| 278 |
* listing comes back on page 1 AND page 2 while its tie-partner appears on neither, |
| 279 |
* so a visitor sees some properties twice and some never at all. post_id is unique, |
| 280 |
* so appending it makes the order total and every page a real slice. |
| 281 |
*/ |
| 282 |
private const TIEBREAK = 'L.post_id DESC'; |
| 283 |
|
| 284 |
/** |
| 285 |
* Build a safe ordering fragment, always ending in the unique-column tiebreaker so |
| 286 |
* the result is a TOTAL order and LIMIT/OFFSET paging is stable. orderby must be a |
| 287 |
* whitelisted column (it cannot be a bound parameter); anything else falls back to |
| 288 |
* the tiebreaker alone — an unordered paginated grid is non-deterministic in exactly |
| 289 |
* the same way a tied one is, so "no sort chosen" still needs a defined order. |
| 290 |
* |
| 291 |
* @param array $params Filter params (orderby, order). |
| 292 |
* @return string Ordering fragment (never empty). |
| 293 |
*/ |
| 294 |
public static function order_clause( array $params ): string { |
| 295 |
// No sort requested by the visitor/block: fall back to the site's configured |
| 296 |
// default order. This is the single point every surface funnels through, so |
| 297 |
// the setting applies to the archive, taxonomy archives, AJAX repaints and |
| 298 |
// page blocks alike without each one re-reading the option. |
| 299 |
$orderby = isset( $params['orderby'] ) && ! is_array( $params['orderby'] ) ? (string) $params['orderby'] : ''; |
| 300 |
if ( '' === $orderby ) { |
| 301 |
$orderby = self::default_sort(); |
| 302 |
} |
| 303 |
|
| 304 |
// A friendly token carries its own direction and wins over any order param; |
| 305 |
// a raw column name still takes its direction from order (default DESC). |
| 306 |
if ( isset( self::SORT_TOKENS[ $orderby ] ) ) { |
| 307 |
list( $orderby, $params['order'] ) = self::SORT_TOKENS[ $orderby ]; |
| 308 |
} |
| 309 |
|
| 310 |
$order = ''; |
| 311 |
if ( in_array( $orderby, self::SORTABLE, true ) ) { |
| 312 |
$dir = isset( $params['order'] ) && 'asc' === strtolower( (string) $params['order'] ) ? 'ASC' : 'DESC'; |
| 313 |
$order = $orderby . ' ' . $dir; |
| 314 |
} |
| 315 |
|
| 316 |
// Advanced hook: a callback may rewrite the ordering, but the result is |
| 317 |
// re-validated against the column whitelist below so SQL stays safe. |
| 318 |
if ( function_exists( 'apply_filters' ) ) { |
| 319 |
/** Filter the ORDER BY fragment (re-validated after). @since 6.3 */ |
| 320 |
$order = (string) apply_filters( 'mlsimport_listings_order', $order, $params ); |
| 321 |
} |
| 322 |
|
| 323 |
$order = self::validate_order( $order ); |
| 324 |
|
| 325 |
return '' === $order ? self::TIEBREAK : $order . ', ' . self::TIEBREAK; |
| 326 |
} |
| 327 |
|
| 328 |
/** |
| 329 |
* The sort token configured in Design Settings → General → "Order by", or '' |
| 330 |
* when set to Default (no ordering beyond the tiebreaker). Also read by the |
| 331 |
* results toolbar so the Sort select shows the configured order as selected. |
| 332 |
* |
| 333 |
* @return string Sort token, or '' for none. |
| 334 |
*/ |
| 335 |
public static function default_sort(): string { |
| 336 |
if ( ! function_exists( 'mlsimport_standalone_option' ) ) { |
| 337 |
return ''; |
| 338 |
} |
| 339 |
$token = (string) mlsimport_standalone_option( 'order_by', '' ); |
| 340 |
|
| 341 |
return isset( self::SORT_TOKENS[ $token ] ) ? $token : ''; |
| 342 |
} |
| 343 |
|
| 344 |
/** |
| 345 |
* Whitelist-validate a "<column> <ASC|DESC>" fragment; anything else yields ''. |
| 346 |
* |
| 347 |
* @param string $order Candidate ordering fragment. |
| 348 |
* @return string |
| 349 |
*/ |
| 350 |
private static function validate_order( string $order ): string { |
| 351 |
$order = trim( $order ); |
| 352 |
if ( '' === $order ) { |
| 353 |
return ''; |
| 354 |
} |
| 355 |
$parts = preg_split( '/\s+/', $order ); |
| 356 |
$column = $parts[0]; |
| 357 |
$dir = isset( $parts[1] ) ? strtoupper( $parts[1] ) : 'DESC'; |
| 358 |
if ( count( $parts ) > 2 || ! in_array( $column, self::SORTABLE, true ) || ! in_array( $dir, array( 'ASC', 'DESC' ), true ) ) { |
| 359 |
return ''; |
| 360 |
} |
| 361 |
return $column . ' ' . $dir; |
| 362 |
} |
| 363 |
|
| 364 |
/** |
| 365 |
* Build a "LIMIT %d OFFSET %d" fragment + args from limit/page. No limit |
| 366 |
* (limit absent or <= 0) yields an empty fragment (the caller returns all rows). |
| 367 |
* |
| 368 |
* @param array $params Filter params (limit, page). |
| 369 |
* @return array{sql:string,args:array} |
| 370 |
*/ |
| 371 |
public static function limit_clause( array $params ): array { |
| 372 |
$limit = isset( $params['limit'] ) ? (int) $params['limit'] : 0; |
| 373 |
if ( function_exists( 'apply_filters' ) ) { |
| 374 |
/** Filter the page size (results per page). @since 6.3 */ |
| 375 |
$limit = (int) apply_filters( 'mlsimport_listings_per_page', $limit, $params ); |
| 376 |
} |
| 377 |
if ( $limit <= 0 ) { |
| 378 |
return array( |
| 379 |
'sql' => '', |
| 380 |
'args' => array(), |
| 381 |
); |
| 382 |
} |
| 383 |
|
| 384 |
$page = isset( $params['page'] ) ? max( 1, (int) $params['page'] ) : 1; |
| 385 |
$offset = ( $page - 1 ) * $limit; |
| 386 |
|
| 387 |
return array( |
| 388 |
'sql' => 'LIMIT %d OFFSET %d', |
| 389 |
'args' => array( $limit, $offset ), |
| 390 |
); |
| 391 |
} |
| 392 |
} |
| 393 |
|
