| @@ -1,14 +1,18 @@ | ||
| 1 | 1 | <?php |
| 2 | +/** | |
| 3 | + * Queries the database for stream records. | |
| 4 | + * | |
| 5 | + * @package WP_Stream | |
| 6 | + */ | |
| 7 | + | |
| 2 | 8 | namespace WP_Stream; |
| 3 | 9 | |
| 10 | +/** | |
| 11 | + * Class - Query | |
| 12 | + */ | |
| 4 | 13 | class Query { |
| 5 | 14 | /** |
| 6 | - * @var DB | |
| 7 | - */ | |
| 8 | - public $db; | |
| 9 | - | |
| 10 | - /** | |
| 11 | 15 | * Hold the number of records found |
| 12 | 16 | * |
| 13 | 17 | * @var int |
| 14 | 18 | */ |
| @@ -14,20 +18,11 @@ | ||
| 14 | 18 | */ |
| 15 | 19 | public $found_records = 0; |
| 16 | 20 | |
| 17 | 21 | /** |
| 18 | - * Class constructor. | |
| 19 | - * | |
| 20 | - * @param DB $db The parent database class. | |
| 21 | - */ | |
| 22 | - public function __construct( $db ) { | |
| 23 | - $this->db = $db; | |
| 24 | - } | |
| 25 | - | |
| 26 | - /** | |
| 27 | 22 | * Query records |
| 28 | 23 | * |
| 29 | - * @param array Query args | |
| 24 | + * @param array $args Arguments to filter the records by. | |
| 30 | 25 | * |
| 31 | 26 | * @return array Stream Records |
| 32 | 27 | */ |
| 33 | 28 | public function query( $args ) { |
| @@ -32,72 +27,8 @@ | ||
| 32 | 27 | */ |
| 33 | 28 | public function query( $args ) { |
| 34 | 29 | global $wpdb; |
| 35 | 30 | |
| 36 | - $defaults = array( | |
| 37 | - // Search param | |
| 38 | - 'search' => null, | |
| 39 | - 'search_field' => 'summary', | |
| 40 | - 'record_after' => null, // Deprecated, use date_after instead | |
| 41 | - // Date-based filters | |
| 42 | - 'date' => null, // Ex: 2015-07-01 | |
| 43 | - 'date_from' => null, // Ex: 2015-07-01 | |
| 44 | - 'date_to' => null, // Ex: 2015-07-01 | |
| 45 | - 'date_after' => null, // Ex: 2015-07-01T15:19:21+00:00 | |
| 46 | - 'date_before' => null, // Ex: 2015-07-01T15:19:21+00:00 | |
| 47 | - // Record ID filters | |
| 48 | - 'record' => null, | |
| 49 | - 'record__in' => array(), | |
| 50 | - 'record__not_in' => array(), | |
| 51 | - // Pagination params | |
| 52 | - 'records_per_page' => get_option( 'posts_per_page', 20 ), | |
| 53 | - 'paged' => 1, | |
| 54 | - // Order | |
| 55 | - 'order' => 'desc', | |
| 56 | - 'orderby' => 'date', | |
| 57 | - // Fields selection | |
| 58 | - 'fields' => array(), | |
| 59 | - ); | |
| 60 | - | |
| 61 | - // Additional property fields | |
| 62 | - $properties = array( | |
| 63 | - 'user_id' => null, | |
| 64 | - 'user_role' => null, | |
| 65 | - 'ip' => null, | |
| 66 | - 'object_id' => null, | |
| 67 | - 'site_id' => null, | |
| 68 | - 'blog_id' => null, | |
| 69 | - 'connector' => null, | |
| 70 | - 'context' => null, | |
| 71 | - 'action' => null, | |
| 72 | - ); | |
| 73 | - | |
| 74 | - /** | |
| 75 | - * Filter allows additional query properties to be added | |
| 76 | - * | |
| 77 | - * @return array Array of query properties | |
| 78 | - */ | |
| 79 | - $properties = apply_filters( 'wp_stream_query_properties', $properties ); | |
| 80 | - | |
| 81 | - // Add property fields to defaults, including their __in/__not_in variations | |
| 82 | - foreach ( $properties as $property => $default ) { | |
| 83 | - if ( ! isset( $defaults[ $property ] ) ) { | |
| 84 | - $defaults[ $property ] = $default; | |
| 85 | - } | |
| 86 | - | |
| 87 | - $defaults[ "{$property}__in" ] = array(); | |
| 88 | - $defaults[ "{$property}__not_in" ] = array(); | |
| 89 | - } | |
| 90 | - | |
| 91 | - $args = wp_parse_args( $args, $defaults ); | |
| 92 | - | |
| 93 | - /** | |
| 94 | - * Filter allows additional arguments to query $args | |
| 95 | - * | |
| 96 | - * @return array Array of query arguments | |
| 97 | - */ | |
| 98 | - $args = apply_filters( 'wp_stream_query_args', $args ); | |
| 99 | - | |
| 100 | 31 | $join = ''; |
| 101 | 32 | $where = ''; |
| 102 | 33 | |
| 103 | 34 | /** |
| @@ -123,11 +54,11 @@ | ||
| 123 | 54 | $where .= $wpdb->prepare( " AND $wpdb->stream.user_role = %s", $args['user_role'] ); |
| 124 | 55 | } |
| 125 | 56 | |
| 126 | 57 | if ( ! empty( $args['search'] ) ) { |
| 127 | - $field = ! empty( $args['search_field'] ) ? $args['search_field'] : 'summary'; | |
| 58 | + $field = ! empty( $args['search_field'] ) ? $args['search_field'] : 'summary'; | |
| 128 | 59 | |
| 129 | - // Sanitize field | |
| 60 | + // Sanitize field. | |
| 130 | 61 | $allowed_fields = array( 'ID', 'site_id', 'blog_id', 'object_id', 'user_id', 'user_role', 'created', 'summary', 'connector', 'context', 'action', 'ip' ); |
| 131 | 62 | if ( in_array( $field, $allowed_fields, true ) ) { |
| 132 | 63 | $where .= $wpdb->prepare( " AND $wpdb->stream.{$field} LIKE %s", "%{$args['search']}%" ); // @codingStandardsIgnoreLine can't prepare column name |
| 133 | 64 | } |
| @@ -151,33 +82,33 @@ | ||
| 151 | 82 | |
| 152 | 83 | /** |
| 153 | 84 | * PARSE DATE PARAM FAMILY |
| 154 | 85 | */ |
| 86 | + if ( ! empty( $args['date'] ) ) { | |
| 87 | + $args['date_from'] = $args['date']; | |
| 88 | + $args['date_to'] = $args['date']; | |
| 89 | + } | |
| 90 | + | |
| 155 | 91 | if ( ! empty( $args['date_from'] ) ) { |
| 156 | - $date = get_gmt_from_date( date( 'Y-m-d H:i:s', strtotime( $args['date_from'] . ' 00:00:00' ) ) ); | |
| 92 | + $date = get_gmt_from_date( gmdate( 'Y-m-d H:i:s', strtotime( $args['date_from'] . ' 00:00:00' ) ) ); | |
| 157 | 93 | $where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) >= %s", $date ); |
| 158 | 94 | } |
| 159 | 95 | |
| 160 | 96 | if ( ! empty( $args['date_to'] ) ) { |
| 161 | - $date = get_gmt_from_date( date( 'Y-m-d H:i:s', strtotime( $args['date_to'] . ' 23:59:59' ) ) ); | |
| 97 | + $date = get_gmt_from_date( gmdate( 'Y-m-d H:i:s', strtotime( $args['date_to'] . ' 23:59:59' ) ) ); | |
| 162 | 98 | $where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) <= %s", $date ); |
| 163 | 99 | } |
| 164 | 100 | |
| 165 | 101 | if ( ! empty( $args['date_after'] ) ) { |
| 166 | - $date = get_gmt_from_date( date( 'Y-m-d H:i:s', strtotime( $args['date_after'] ) ) ); | |
| 102 | + $date = get_gmt_from_date( gmdate( 'Y-m-d H:i:s', strtotime( $args['date_after'] ) ) ); | |
| 167 | 103 | $where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) > %s", $date ); |
| 168 | 104 | } |
| 169 | 105 | |
| 170 | 106 | if ( ! empty( $args['date_before'] ) ) { |
| 171 | - $date = get_gmt_from_date( date( 'Y-m-d H:i:s', strtotime( $args['date_before'] ) ) ); | |
| 107 | + $date = get_gmt_from_date( gmdate( 'Y-m-d H:i:s', strtotime( $args['date_before'] ) ) ); | |
| 172 | 108 | $where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) < %s", $date ); |
| 173 | 109 | } |
| 174 | 110 | |
| 175 | - if ( ! empty( $args['date'] ) ) { | |
| 176 | - $args['date_from'] = date( 'Y-m-d', strtotime( $args['date'] ) ) . ' 00:00:00'; | |
| 177 | - $args['date_to'] = date( 'Y-m-d', strtotime( $args['date'] ) ) . ' 23:59:59'; | |
| 178 | - } | |
| 179 | - | |
| 180 | 111 | /** |
| 181 | 112 | * PARSE __IN PARAM FAMILY |
| 182 | 113 | */ |
| 183 | 114 | $ins = array(); |
| @@ -247,24 +178,29 @@ | ||
| 247 | 178 | |
| 248 | 179 | /** |
| 249 | 180 | * PARSE ORDER PARAMS |
| 250 | 181 | */ |
| 251 | - $order = esc_sql( $args['order'] ); | |
| 252 | - $orderby = esc_sql( $args['orderby'] ); | |
| 253 | 182 | $orderable = array( 'ID', 'site_id', 'blog_id', 'object_id', 'user_id', 'user_role', 'summary', 'created', 'connector', 'context', 'action' ); |
| 254 | 183 | |
| 255 | - if ( in_array( $orderby, $orderable, true ) ) { | |
| 256 | - $orderby = sprintf( '%s.%s', $wpdb->stream, $orderby ); | |
| 257 | - } elseif ( 'meta_value_num' === $orderby && ! empty( $args['meta_key'] ) ) { | |
| 184 | + // Default to sorting by record ID. | |
| 185 | + $orderby = "$wpdb->stream.ID"; | |
| 186 | + | |
| 187 | + if ( in_array( $args['orderby'], $orderable, true ) ) { | |
| 188 | + $orderby = sprintf( '%s.%s', $wpdb->stream, $args['orderby'] ); | |
| 189 | + } elseif ( 'meta_value_num' === $args['orderby'] && ! empty( $args['meta_key'] ) ) { | |
| 258 | 190 | $orderby = "CAST($wpdb->streammeta.meta_value AS SIGNED)"; |
| 259 | - } elseif ( 'meta_value' === $orderby && ! empty( $args['meta_key'] ) ) { | |
| 191 | + } elseif ( 'meta_value' === $args['orderby'] && ! empty( $args['meta_key'] ) ) { | |
| 260 | 192 | $orderby = "$wpdb->streammeta.meta_value"; |
| 261 | - } else { | |
| 262 | - $orderby = "$wpdb->stream.ID"; | |
| 263 | 193 | } |
| 264 | 194 | |
| 265 | - $orderby = "ORDER BY {$orderby} {$order}"; | |
| 195 | + // Show the recent records first by default. | |
| 196 | + $order = 'DESC'; | |
| 197 | + if ( 'ASC' === strtoupper( $args['order'] ) ) { | |
| 198 | + $order = 'ASC'; | |
| 199 | + } | |
| 266 | 200 | |
| 201 | + $orderby = sprintf( 'ORDER BY %s %s', $orderby, $order ); | |
| 202 | + | |
| 267 | 203 | /** |
| 268 | 204 | * PARSE FIELDS PARAMETER |
| 269 | 205 | */ |
| 270 | 206 | $fields = (array) $args['fields']; |
| @@ -271,9 +207,9 @@ | ||
| 271 | 207 | $selects = array(); |
| 272 | 208 | |
| 273 | 209 | if ( ! empty( $fields ) ) { |
| 274 | 210 | foreach ( $fields as $field ) { |
| 275 | - // We'll query the meta table later | |
| 211 | + // We'll query the meta table later. | |
| 276 | 212 | if ( 'meta' === $field ) { |
| 277 | 213 | continue; |
| 278 | 214 | } |
| 279 | 215 | |
| @@ -285,11 +221,21 @@ | ||
| 285 | 221 | |
| 286 | 222 | $select = implode( ', ', $selects ); |
| 287 | 223 | |
| 288 | 224 | /** |
| 225 | + * Filters query WHERE statement as an alternative to filtering | |
| 226 | + * the $query using the hook below. | |
| 227 | + * | |
| 228 | + * @param string $where WHERE statement. | |
| 229 | + * | |
| 230 | + * @return string | |
| 231 | + */ | |
| 232 | + $where = apply_filters( 'wp_stream_db_query_where', $where ); | |
| 233 | + | |
| 234 | + /** | |
| 289 | 235 | * BUILD THE FINAL QUERY |
| 290 | 236 | */ |
| 291 | - $query = "SELECT SQL_CALC_FOUND_ROWS {$select} | |
| 237 | + $query = "SELECT {$select} | |
| 292 | 238 | FROM $wpdb->stream |
| 293 | 239 | {$join} |
| 294 | 240 | WHERE 1=1 {$where} |
| 295 | 241 | {$orderby} |
| @@ -304,51 +250,31 @@ | ||
| 304 | 250 | * @return string |
| 305 | 251 | */ |
| 306 | 252 | $query = apply_filters( 'wp_stream_db_query', $query, $args ); |
| 307 | 253 | |
| 254 | + // Build result count query. | |
| 255 | + $count_query = "SELECT COUNT(*) as found | |
| 256 | + FROM $wpdb->stream | |
| 257 | + {$join} | |
| 258 | + WHERE 1=1 {$where}"; | |
| 259 | + | |
| 308 | 260 | /** |
| 261 | + * Filter allows the result count query to be modified before execution. | |
| 262 | + * | |
| 263 | + * @param string $query | |
| 264 | + * @param array $args | |
| 265 | + * | |
| 266 | + * @return string | |
| 267 | + */ | |
| 268 | + $count_query = apply_filters( 'wp_stream_db_count_query', $count_query, $args ); | |
| 269 | + | |
| 270 | + /** | |
| 309 | 271 | * QUERY THE DATABASE FOR RESULTS |
| 310 | 272 | */ |
| 311 | - $results = $wpdb->get_results( $query ); // @codingStandardsIgnoreLine $query already prepared | |
| 312 | - | |
| 313 | - // Hold the number of records found | |
| 314 | - $this->found_records = absint( $wpdb->get_var( 'SELECT FOUND_ROWS()' ) ); | |
| 315 | - | |
| 316 | - // Add meta to the records, when applicable | |
| 317 | - if ( empty( $fields ) || in_array( 'meta', $fields, true ) ) { | |
| 318 | - $results = $this->add_record_meta( $results ); | |
| 319 | - } | |
| 320 | - | |
| 321 | - return (array) $results; | |
| 322 | - } | |
| 323 | - | |
| 324 | - /** | |
| 325 | - * Add meta to a set of records | |
| 326 | - * | |
| 327 | - * @param array $records | |
| 328 | - * | |
| 329 | - * @return array | |
| 330 | - */ | |
| 331 | - public function add_record_meta( $records ) { | |
| 332 | - global $wpdb; | |
| 333 | - | |
| 334 | - $record_ids = array_map( 'absint', wp_list_pluck( $records, 'ID' ) ); | |
| 335 | - | |
| 336 | - if ( empty( $record_ids ) ) { | |
| 337 | - return (array) $records; | |
| 338 | - } | |
| 339 | - | |
| 340 | - $sql_meta = sprintf( | |
| 341 | - "SELECT * FROM $wpdb->streammeta WHERE record_id IN ( %s )", | |
| 342 | - implode( ',', $record_ids ) | |
| 273 | + $result = array( | |
| 274 | + 'items' => $wpdb->get_results( $query ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared | |
| 275 | + 'count' => absint( $wpdb->get_var( $count_query ) ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared | |
| 343 | 276 | ); |
| 344 | 277 | |
| 345 | - $meta = $wpdb->get_results( $sql_meta ); // @codingStandardsIgnoreLine prepare okay | |
| 346 | - $ids_f = array_flip( $record_ids ); | |
| 347 | - | |
| 348 | - foreach ( $meta as $meta_record ) { | |
| 349 | - $records[ $ids_f[ $meta_record->record_id ] ]->meta[ $meta_record->meta_key ] = maybe_unserialize( $meta_record->meta_value ); | |
| 350 | - } | |
| 351 | - | |
| 352 | - return (array) $records; | |
| 278 | + return $result; | |
| 353 | 279 | } |
| 354 | 280 | } |
