| @@ -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 | } |
| @@ -79,24 +88,24 @@ | ||
| 79 | 88 | $args['date_to'] = $args['date']; |
| 80 | 89 | } |
| 81 | 90 | |
| 82 | 91 | if ( ! empty( $args['date_from'] ) ) { |
| 83 | - $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' ) ) ); | |
| 84 | 93 | $where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) >= %s", $date ); |
| 85 | 94 | } |
| 86 | 95 | |
| 87 | 96 | if ( ! empty( $args['date_to'] ) ) { |
| 88 | - $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' ) ) ); | |
| 89 | 98 | $where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) <= %s", $date ); |
| 90 | 99 | } |
| 91 | 100 | |
| 92 | 101 | if ( ! empty( $args['date_after'] ) ) { |
| 93 | - $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'] ) ) ); | |
| 94 | 103 | $where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) > %s", $date ); |
| 95 | 104 | } |
| 96 | 105 | |
| 97 | 106 | if ( ! empty( $args['date_before'] ) ) { |
| 98 | - $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'] ) ) ); | |
| 99 | 108 | $where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) < %s", $date ); |
| 100 | 109 | } |
| 101 | 110 | |
| 102 | 111 | /** |
| @@ -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,14 +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 | 278 | return $result; |
| 238 | 279 | } |
| 239 | 280 | } |
