98 * Constructor. |
98 * Constructor. |
99 * |
99 * |
100 * @since 3.2.0 |
100 * @since 3.2.0 |
101 * @since 4.2.0 Introduced support for naming query clauses by associative array keys. |
101 * @since 4.2.0 Introduced support for naming query clauses by associative array keys. |
102 * @since 5.1.0 Introduced $compare_key clause parameter, which enables LIKE key matches. |
102 * @since 5.1.0 Introduced $compare_key clause parameter, which enables LIKE key matches. |
|
103 * @since 5.3.0 Increased the number of operators available to $compare_key. Introduced $type_key, |
|
104 * which enables the $key to be cast to a new data type for comparisons. |
103 * |
105 * |
104 * @param array $meta_query { |
106 * @param array $meta_query { |
105 * Array of meta query clauses. When first-order clauses or sub-clauses use strings as |
107 * Array of meta query clauses. When first-order clauses or sub-clauses use strings as |
106 * their array keys, they may be referenced in the 'orderby' parameter of the parent query. |
108 * their array keys, they may be referenced in the 'orderby' parameter of the parent query. |
107 * |
109 * |
108 * @type string $relation Optional. The MySQL keyword used to join |
110 * @type string $relation Optional. The MySQL keyword used to join |
109 * the clauses of the query. Accepts 'AND', or 'OR'. Default 'AND'. |
111 * the clauses of the query. Accepts 'AND', or 'OR'. Default 'AND'. |
110 * @type array { |
112 * @type array ...$0 { |
111 * Optional. An array of first-order clause parameters, or another fully-formed meta query. |
113 * Optional. An array of first-order clause parameters, or another fully-formed meta query. |
112 * |
114 * |
113 * @type string $key Meta key to filter by. |
115 * @type string $key Meta key to filter by. |
114 * @type string $compare_key MySQL operator used for comparing the $key. Accepts '=' and 'LIKE'. |
116 * @type string $compare_key MySQL operator used for comparing the $key. Accepts '=', '!=' |
115 * Default '='. |
117 * 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'REGEXP', 'NOT REGEXP', 'RLIKE', |
|
118 * 'EXISTS' (alias of '=') or 'NOT EXISTS' (alias of '!='). |
|
119 * Default is 'IN' when `$key` is an array, '=' otherwise. |
|
120 * @type string $type_key MySQL data type that the meta_key column will be CAST to for |
|
121 * comparisons. Accepts 'BINARY' for case-sensitive regular expression |
|
122 * comparisons. Default is ''. |
116 * @type string $value Meta value to filter by. |
123 * @type string $value Meta value to filter by. |
117 * @type string $compare MySQL operator used for comparing the $value. Accepts '=', |
124 * @type string $compare MySQL operator used for comparing the $value. Accepts '=', |
118 * '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE', |
125 * '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE', |
119 * 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', 'REGEXP', |
126 * 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', 'REGEXP', |
120 * 'NOT REGEXP', 'RLIKE', 'EXISTS' or 'NOT EXISTS'. |
127 * 'NOT REGEXP', 'RLIKE', 'EXISTS' or 'NOT EXISTS'. |
237 * first (so that its table join is against an unaliased meta table) and |
244 * first (so that its table join is against an unaliased meta table) and |
238 * needs to be its own clause (so it doesn't interfere with the logic of |
245 * needs to be its own clause (so it doesn't interfere with the logic of |
239 * the rest of the meta_query). |
246 * the rest of the meta_query). |
240 */ |
247 */ |
241 $primary_meta_query = array(); |
248 $primary_meta_query = array(); |
242 foreach ( array( 'key', 'compare', 'type', 'compare_key' ) as $key ) { |
249 foreach ( array( 'key', 'compare', 'type', 'compare_key', 'type_key' ) as $key ) { |
243 if ( ! empty( $qv[ "meta_$key" ] ) ) { |
250 if ( ! empty( $qv[ "meta_$key" ] ) ) { |
244 $primary_meta_query[ $key ] = $qv[ "meta_$key" ]; |
251 $primary_meta_query[ $key ] = $qv[ "meta_$key" ]; |
245 } |
252 } |
246 } |
253 } |
247 |
254 |
302 * |
309 * |
303 * @param string $type Type of meta, eg 'user', 'post'. |
310 * @param string $type Type of meta, eg 'user', 'post'. |
304 * @param string $primary_table Database table where the object being filtered is stored (eg wp_users). |
311 * @param string $primary_table Database table where the object being filtered is stored (eg wp_users). |
305 * @param string $primary_id_column ID column for the filtered object in $primary_table. |
312 * @param string $primary_id_column ID column for the filtered object in $primary_table. |
306 * @param object $context Optional. The main query object. |
313 * @param object $context Optional. The main query object. |
307 * @return false|array { |
314 * @return array|false { |
308 * Array containing JOIN and WHERE SQL clauses to append to the main query. |
315 * Array containing JOIN and WHERE SQL clauses to append to the main query. |
309 * |
316 * |
310 * @type string $join SQL fragment to append to the main JOIN clause. |
317 * @type string $join SQL fragment to append to the main JOIN clause. |
311 * @type string $where SQL fragment to append to the main WHERE clause. |
318 * @type string $where SQL fragment to append to the main WHERE clause. |
312 * } |
319 * } |
313 */ |
320 */ |
314 public function get_sql( $type, $primary_table, $primary_id_column, $context = null ) { |
321 public function get_sql( $type, $primary_table, $primary_id_column, $context = null ) { |
315 if ( ! $meta_table = _get_meta_table( $type ) ) { |
322 $meta_table = _get_meta_table( $type ); |
|
323 if ( ! $meta_table ) { |
316 return false; |
324 return false; |
317 } |
325 } |
318 |
326 |
319 $this->table_aliases = array(); |
327 $this->table_aliases = array(); |
320 |
328 |
495 $clause['compare'] = strtoupper( $clause['compare'] ); |
503 $clause['compare'] = strtoupper( $clause['compare'] ); |
496 } else { |
504 } else { |
497 $clause['compare'] = isset( $clause['value'] ) && is_array( $clause['value'] ) ? 'IN' : '='; |
505 $clause['compare'] = isset( $clause['value'] ) && is_array( $clause['value'] ) ? 'IN' : '='; |
498 } |
506 } |
499 |
507 |
500 if ( ! in_array( |
508 $non_numeric_operators = array( |
501 $clause['compare'], |
509 '=', |
502 array( |
510 '!=', |
503 '=', |
511 'LIKE', |
504 '!=', |
512 'NOT LIKE', |
505 '>', |
513 'IN', |
506 '>=', |
514 'NOT IN', |
507 '<', |
515 'EXISTS', |
508 '<=', |
516 'NOT EXISTS', |
509 'LIKE', |
517 'RLIKE', |
510 'NOT LIKE', |
518 'REGEXP', |
511 'IN', |
519 'NOT REGEXP', |
512 'NOT IN', |
520 ); |
513 'BETWEEN', |
521 |
514 'NOT BETWEEN', |
522 $numeric_operators = array( |
515 'EXISTS', |
523 '>', |
516 'NOT EXISTS', |
524 '>=', |
517 'REGEXP', |
525 '<', |
518 'NOT REGEXP', |
526 '<=', |
519 'RLIKE', |
527 'BETWEEN', |
520 ) |
528 'NOT BETWEEN', |
521 ) ) { |
529 ); |
|
530 |
|
531 if ( ! in_array( $clause['compare'], $non_numeric_operators, true ) && ! in_array( $clause['compare'], $numeric_operators, true ) ) { |
522 $clause['compare'] = '='; |
532 $clause['compare'] = '='; |
523 } |
533 } |
524 |
534 |
525 if ( isset( $clause['compare_key'] ) && 'LIKE' === strtoupper( $clause['compare_key'] ) ) { |
535 if ( isset( $clause['compare_key'] ) ) { |
526 $clause['compare_key'] = strtoupper( $clause['compare_key'] ); |
536 $clause['compare_key'] = strtoupper( $clause['compare_key'] ); |
527 } else { |
537 } else { |
|
538 $clause['compare_key'] = isset( $clause['key'] ) && is_array( $clause['key'] ) ? 'IN' : '='; |
|
539 } |
|
540 |
|
541 if ( ! in_array( $clause['compare_key'], $non_numeric_operators, true ) ) { |
528 $clause['compare_key'] = '='; |
542 $clause['compare_key'] = '='; |
529 } |
543 } |
530 |
544 |
531 $meta_compare = $clause['compare']; |
545 $meta_compare = $clause['compare']; |
532 $meta_compare_key = $clause['compare_key']; |
546 $meta_compare_key = $clause['compare_key']; |
591 // meta_key. |
605 // meta_key. |
592 if ( array_key_exists( 'key', $clause ) ) { |
606 if ( array_key_exists( 'key', $clause ) ) { |
593 if ( 'NOT EXISTS' === $meta_compare ) { |
607 if ( 'NOT EXISTS' === $meta_compare ) { |
594 $sql_chunks['where'][] = $alias . '.' . $this->meta_id_column . ' IS NULL'; |
608 $sql_chunks['where'][] = $alias . '.' . $this->meta_id_column . ' IS NULL'; |
595 } else { |
609 } else { |
596 if ( 'LIKE' === $meta_compare_key ) { |
610 /** |
597 $sql_chunks['where'][] = $wpdb->prepare( "$alias.meta_key LIKE %s", '%' . $wpdb->esc_like( trim( $clause['key'] ) ) . '%' ); |
611 * In joined clauses negative operators have to be nested into a |
598 } else { |
612 * NOT EXISTS clause and flipped, to avoid returning records with |
599 $sql_chunks['where'][] = $wpdb->prepare( "$alias.meta_key = %s", trim( $clause['key'] ) ); |
613 * matching post IDs but different meta keys. Here we prepare the |
|
614 * nested clause. |
|
615 */ |
|
616 if ( in_array( $meta_compare_key, array( '!=', 'NOT IN', 'NOT LIKE', 'NOT EXISTS', 'NOT REGEXP' ), true ) ) { |
|
617 // Negative clauses may be reused. |
|
618 $i = count( $this->table_aliases ); |
|
619 $subquery_alias = $i ? 'mt' . $i : $this->meta_table; |
|
620 $this->table_aliases[] = $subquery_alias; |
|
621 |
|
622 $meta_compare_string_start = 'NOT EXISTS ('; |
|
623 $meta_compare_string_start .= "SELECT 1 FROM $wpdb->postmeta $subquery_alias "; |
|
624 $meta_compare_string_start .= "WHERE $subquery_alias.post_ID = $alias.post_ID "; |
|
625 $meta_compare_string_end = 'LIMIT 1'; |
|
626 $meta_compare_string_end .= ')'; |
600 } |
627 } |
|
628 |
|
629 switch ( $meta_compare_key ) { |
|
630 case '=': |
|
631 case 'EXISTS': |
|
632 $where = $wpdb->prepare( "$alias.meta_key = %s", trim( $clause['key'] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
|
633 break; |
|
634 case 'LIKE': |
|
635 $meta_compare_value = '%' . $wpdb->esc_like( trim( $clause['key'] ) ) . '%'; |
|
636 $where = $wpdb->prepare( "$alias.meta_key LIKE %s", $meta_compare_value ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
|
637 break; |
|
638 case 'IN': |
|
639 $meta_compare_string = "$alias.meta_key IN (" . substr( str_repeat( ',%s', count( $clause['key'] ) ), 1 ) . ')'; |
|
640 $where = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
|
641 break; |
|
642 case 'RLIKE': |
|
643 case 'REGEXP': |
|
644 $operator = $meta_compare_key; |
|
645 if ( isset( $clause['type_key'] ) && 'BINARY' === strtoupper( $clause['type_key'] ) ) { |
|
646 $cast = 'BINARY'; |
|
647 } else { |
|
648 $cast = ''; |
|
649 } |
|
650 $where = $wpdb->prepare( "$alias.meta_key $operator $cast %s", trim( $clause['key'] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
|
651 break; |
|
652 |
|
653 case '!=': |
|
654 case 'NOT EXISTS': |
|
655 $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key = %s " . $meta_compare_string_end; |
|
656 $where = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
|
657 break; |
|
658 case 'NOT LIKE': |
|
659 $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key LIKE %s " . $meta_compare_string_end; |
|
660 |
|
661 $meta_compare_value = '%' . $wpdb->esc_like( trim( $clause['key'] ) ) . '%'; |
|
662 $where = $wpdb->prepare( $meta_compare_string, $meta_compare_value ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
|
663 break; |
|
664 case 'NOT IN': |
|
665 $array_subclause = '(' . substr( str_repeat( ',%s', count( $clause['key'] ) ), 1 ) . ') '; |
|
666 $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key IN " . $array_subclause . $meta_compare_string_end; |
|
667 $where = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
|
668 break; |
|
669 case 'NOT REGEXP': |
|
670 $operator = $meta_compare_key; |
|
671 if ( isset( $clause['type_key'] ) && 'BINARY' === strtoupper( $clause['type_key'] ) ) { |
|
672 $cast = 'BINARY'; |
|
673 } else { |
|
674 $cast = ''; |
|
675 } |
|
676 |
|
677 $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key REGEXP $cast %s " . $meta_compare_string_end; |
|
678 $where = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
|
679 break; |
|
680 } |
|
681 |
|
682 $sql_chunks['where'][] = $where; |
601 } |
683 } |
602 } |
684 } |
603 |
685 |
604 // meta_value. |
686 // meta_value. |
605 if ( array_key_exists( 'value', $clause ) ) { |
687 if ( array_key_exists( 'value', $clause ) ) { |
606 $meta_value = $clause['value']; |
688 $meta_value = $clause['value']; |
607 |
689 |
608 if ( in_array( $meta_compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) { |
690 if ( in_array( $meta_compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ), true ) ) { |
609 if ( ! is_array( $meta_value ) ) { |
691 if ( ! is_array( $meta_value ) ) { |
610 $meta_value = preg_split( '/[,\s]+/', $meta_value ); |
692 $meta_value = preg_split( '/[,\s]+/', $meta_value ); |
611 } |
693 } |
612 } else { |
694 } else { |
613 $meta_value = trim( $meta_value ); |
695 $meta_value = trim( $meta_value ); |
697 * In the case of WP_Meta_Query, this only applies to 'IN' clauses that are |
778 * In the case of WP_Meta_Query, this only applies to 'IN' clauses that are |
698 * connected by the relation 'OR'. |
779 * connected by the relation 'OR'. |
699 * |
780 * |
700 * @since 4.1.0 |
781 * @since 4.1.0 |
701 * |
782 * |
702 * @param array $clause Query clause. |
783 * @param array $clause Query clause. |
703 * @param array $parent_query Parent query of $clause. |
784 * @param array $parent_query Parent query of $clause. |
704 * @return string|bool Table alias if found, otherwise false. |
785 * @return string|false Table alias if found, otherwise false. |
705 */ |
786 */ |
706 protected function find_compatible_table_alias( $clause, $parent_query ) { |
787 protected function find_compatible_table_alias( $clause, $parent_query ) { |
707 $alias = false; |
788 $alias = false; |
708 |
789 |
709 foreach ( $parent_query as $sibling ) { |
790 foreach ( $parent_query as $sibling ) { |
728 $compatible_compares = array( '!=', 'NOT IN', 'NOT LIKE' ); |
809 $compatible_compares = array( '!=', 'NOT IN', 'NOT LIKE' ); |
729 } |
810 } |
730 |
811 |
731 $clause_compare = strtoupper( $clause['compare'] ); |
812 $clause_compare = strtoupper( $clause['compare'] ); |
732 $sibling_compare = strtoupper( $sibling['compare'] ); |
813 $sibling_compare = strtoupper( $sibling['compare'] ); |
733 if ( in_array( $clause_compare, $compatible_compares ) && in_array( $sibling_compare, $compatible_compares ) ) { |
814 if ( in_array( $clause_compare, $compatible_compares, true ) && in_array( $sibling_compare, $compatible_compares, true ) ) { |
734 $alias = $sibling['alias']; |
815 $alias = $sibling['alias']; |
735 break; |
816 break; |
736 } |
817 } |
737 } |
818 } |
738 |
819 |
739 /** |
820 /** |
740 * Filters the table alias identified as compatible with the current clause. |
821 * Filters the table alias identified as compatible with the current clause. |
741 * |
822 * |
742 * @since 4.1.0 |
823 * @since 4.1.0 |
743 * |
824 * |
744 * @param string|bool $alias Table alias, or false if none was found. |
825 * @param string|bool $alias Table alias, or false if none was found. |
745 * @param array $clause First-order query clause. |
826 * @param array $clause First-order query clause. |
746 * @param array $parent_query Parent of $clause. |
827 * @param array $parent_query Parent of $clause. |
747 * @param object $this WP_Meta_Query object. |
828 * @param WP_Meta_Query $this WP_Meta_Query object. |
748 */ |
829 */ |
749 return apply_filters( 'meta_query_find_compatible_table_alias', $alias, $clause, $parent_query, $this ); |
830 return apply_filters( 'meta_query_find_compatible_table_alias', $alias, $clause, $parent_query, $this ); |
750 } |
831 } |
751 |
832 |
752 /** |
833 /** |