| @@ -1,7 +1,16 @@ | ||
| 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 | 15 | * Hold the number of records found |
| 7 | 16 | * |
| @@ -11,9 +20,9 @@ | ||
| 11 | 20 | |
| 12 | 21 | /** |
| 13 | 22 | * Query records |
| 14 | 23 | * |
| 15 | - * @param array Query args | |
| 24 | + * @param array $args Arguments to filter the records by. | |
| 16 | 25 | * |
| 17 | 26 | * @return array Stream Records |
| 18 | 27 | */ |
| 19 | 28 | public function query( $args ) { |
| @@ -45,11 +54,11 @@ | ||
| 45 | 54 | $where .= $wpdb->prepare( " AND $wpdb->stream.user_role = %s", $args['user_role'] ); |
| 46 | 55 | } |
| 47 | 56 | |
| 48 | 57 | if ( ! empty( $args['search'] ) ) { |
| 49 | - $field = ! empty( $args['search_field'] ) ? $args['search_field'] : 'summary'; | |
| 58 | + $field = ! empty( $args['search_field'] ) ? $args['search_field'] : 'summary'; | |
| 50 | 59 | |
| 51 | - // Sanitize field | |
| 60 | + // Sanitize field. | |
| 52 | 61 | $allowed_fields = array( 'ID', 'site_id', 'blog_id', 'object_id', 'user_id', 'user_role', 'created', 'summary', 'connector', 'context', 'action', 'ip' ); |
| 53 | 62 | if ( in_array( $field, $allowed_fields, true ) ) { |
| 54 | 63 | $where .= $wpdb->prepare( " AND $wpdb->stream.{$field} LIKE %s", "%{$args['search']}%" ); // @codingStandardsIgnoreLine can't prepare column name |
| 55 | 64 | } |
| @@ -73,33 +82,33 @@ | ||
| 73 | 82 | |
| 74 | 83 | /** |
| 75 | 84 | * PARSE DATE PARAM FAMILY |
| 76 | 85 | */ |
| 86 | + if ( ! empty( $args['date'] ) ) { | |
| 87 | + $args['date_from'] = $args['date']; | |
| 88 | + $args['date_to'] = $args['date']; | |
| 89 | + } | |
| 90 | + | |
| 77 | 91 | if ( ! empty( $args['date_from'] ) ) { |
| 78 | - $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' ) ) ); | |
| 79 | 93 | $where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) >= %s", $date ); |
| 80 | 94 | } |
| 81 | 95 | |
| 82 | 96 | if ( ! empty( $args['date_to'] ) ) { |
| 83 | - $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' ) ) ); | |
| 84 | 98 | $where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) <= %s", $date ); |
| 85 | 99 | } |
| 86 | 100 | |
| 87 | 101 | if ( ! empty( $args['date_after'] ) ) { |
| 88 | - $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'] ) ) ); | |
| 89 | 103 | $where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) > %s", $date ); |
| 90 | 104 | } |
| 91 | 105 | |
| 92 | 106 | if ( ! empty( $args['date_before'] ) ) { |
| 93 | - $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'] ) ) ); | |
| 94 | 108 | $where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) < %s", $date ); |
| 95 | 109 | } |
| 96 | 110 | |
| 97 | - if ( ! empty( $args['date'] ) ) { | |
| 98 | - $args['date_from'] = date( 'Y-m-d', strtotime( $args['date'] ) ) . ' 00:00:00'; | |
| 99 | - $args['date_to'] = date( 'Y-m-d', strtotime( $args['date'] ) ) . ' 23:59:59'; | |
| 100 | - } | |
| 101 | - | |
| 102 | 111 | /** |
| 103 | 112 | * PARSE __IN PARAM FAMILY |
| 104 | 113 | */ |
| 105 | 114 | $ins = array(); |
| @@ -169,24 +178,29 @@ | ||
| 169 | 178 | |
| 170 | 179 | /** |
| 171 | 180 | * PARSE ORDER PARAMS |
| 172 | 181 | */ |
| 173 | - $order = esc_sql( $args['order'] ); | |
| 174 | - $orderby = esc_sql( $args['orderby'] ); | |
| 175 | 182 | $orderable = array( 'ID', 'site_id', 'blog_id', 'object_id', 'user_id', 'user_role', 'summary', 'created', 'connector', 'context', 'action' ); |
| 176 | 183 | |
| 177 | - if ( in_array( $orderby, $orderable, true ) ) { | |
| 178 | - $orderby = sprintf( '%s.%s', $wpdb->stream, $orderby ); | |
| 179 | - } 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'] ) ) { | |
| 180 | 190 | $orderby = "CAST($wpdb->streammeta.meta_value AS SIGNED)"; |
| 181 | - } elseif ( 'meta_value' === $orderby && ! empty( $args['meta_key'] ) ) { | |
| 191 | + } elseif ( 'meta_value' === $args['orderby'] && ! empty( $args['meta_key'] ) ) { | |
| 182 | 192 | $orderby = "$wpdb->streammeta.meta_value"; |
| 183 | - } else { | |
| 184 | - $orderby = "$wpdb->stream.ID"; | |
| 185 | 193 | } |
| 186 | 194 | |
| 187 | - $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 | + } | |
| 188 | 200 | |
| 201 | + $orderby = sprintf( 'ORDER BY %s %s', $orderby, $order ); | |
| 202 | + | |
| 189 | 203 | /** |
| 190 | 204 | * PARSE FIELDS PARAMETER |
| 191 | 205 | */ |
| 192 | 206 | $fields = (array) $args['fields']; |
| @@ -193,9 +207,9 @@ | ||
| 193 | 207 | $selects = array(); |
| 194 | 208 | |
| 195 | 209 | if ( ! empty( $fields ) ) { |
| 196 | 210 | foreach ( $fields as $field ) { |
| 197 | - // We'll query the meta table later | |
| 211 | + // We'll query the meta table later. | |
| 198 | 212 | if ( 'meta' === $field ) { |
| 199 | 213 | continue; |
| 200 | 214 | } |
| 201 | 215 | |
| @@ -207,11 +221,21 @@ | ||
| 207 | 221 | |
| 208 | 222 | $select = implode( ', ', $selects ); |
| 209 | 223 | |
| 210 | 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 | + /** | |
| 211 | 235 | * BUILD THE FINAL QUERY |
| 212 | 236 | */ |
| 213 | - $query = "SELECT SQL_CALC_FOUND_ROWS {$select} | |
| 237 | + $query = "SELECT {$select} | |
| 214 | 238 | FROM $wpdb->stream |
| 215 | 239 | {$join} |
| 216 | 240 | WHERE 1=1 {$where} |
| 217 | 241 | {$orderby} |
| @@ -226,50 +250,31 @@ | ||
| 226 | 250 | * @return string |
| 227 | 251 | */ |
| 228 | 252 | $query = apply_filters( 'wp_stream_db_query', $query, $args ); |
| 229 | 253 | |
| 230 | - $result = array(); | |
| 254 | + // Build result count query. | |
| 255 | + $count_query = "SELECT COUNT(*) as found | |
| 256 | + FROM $wpdb->stream | |
| 257 | + {$join} | |
| 258 | + WHERE 1=1 {$where}"; | |
| 259 | + | |
| 231 | 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 | + /** | |
| 232 | 271 | * QUERY THE DATABASE FOR RESULTS |
| 233 | 272 | */ |
| 234 | - $result['items'] = $wpdb->get_results( $query ); // @codingStandardsIgnoreLine $query already prepared | |
| 235 | - $result['count'] = $result['items'] ? absint( $wpdb->get_var( 'SELECT FOUND_ROWS()' ) ) : 0; | |
| 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 | |
| 276 | + ); | |
| 236 | 277 | |
| 237 | - // Add meta to the records, when applicable | |
| 238 | - if ( empty( $fields ) || in_array( 'meta', $fields, true ) ) { | |
| 239 | - $result['items'] = $this->add_record_meta( $result['items'] ); | |
| 240 | - } | |
| 241 | - | |
| 242 | 278 | return $result; |
| 243 | - } | |
| 244 | - | |
| 245 | - /** | |
| 246 | - * Add meta to a set of records | |
| 247 | - * | |
| 248 | - * @param array $records | |
| 249 | - * | |
| 250 | - * @return array | |
| 251 | - */ | |
| 252 | - public function add_record_meta( $records ) { | |
| 253 | - global $wpdb; | |
| 254 | - | |
| 255 | - $record_ids = array_map( 'absint', wp_list_pluck( $records, 'ID' ) ); | |
| 256 | - | |
| 257 | - if ( empty( $record_ids ) ) { | |
| 258 | - return (array) $records; | |
| 259 | - } | |
| 260 | - | |
| 261 | - $sql_meta = sprintf( | |
| 262 | - "SELECT * FROM $wpdb->streammeta WHERE record_id IN ( %s )", | |
| 263 | - implode( ',', $record_ids ) | |
| 264 | - ); | |
| 265 | - | |
| 266 | - $meta = $wpdb->get_results( $sql_meta ); // @codingStandardsIgnoreLine prepare okay | |
| 267 | - $ids_f = array_flip( $record_ids ); | |
| 268 | - | |
| 269 | - foreach ( $meta as $meta_record ) { | |
| 270 | - $records[ $ids_f[ $meta_record->record_id ] ]->meta[ $meta_record->meta_key ] = maybe_unserialize( $meta_record->meta_value ); | |
| 271 | - } | |
| 272 | - | |
| 273 | - return (array) $records; | |
| 274 | 279 | } |
| 275 | 280 | } |
