| 1 |
<?php |
| 2 |
/** |
| 3 |
* Queries the database for stream records. |
| 4 |
* |
| 5 |
* @package WP_Stream |
| 6 |
*/ |
| 7 |
|
| 8 |
namespace WP_Stream; |
| 9 |
|
| 10 |
/** |
| 11 |
* Class - Query |
| 12 |
*/ |
| 13 |
class Query { |
| 14 |
const ALLOWED_FIELDS = array( 'ID', 'site_id', 'blog_id', 'object_id', 'user_id', 'user_role', 'created', 'summary', 'connector', 'context', 'action', 'ip' ); |
| 15 |
|
| 16 |
/** |
| 17 |
* Hold the number of records found |
| 18 |
* |
| 19 |
* @var int |
| 20 |
*/ |
| 21 |
public $found_records = 0; |
| 22 |
|
| 23 |
/** |
| 24 |
* Query records |
| 25 |
* |
| 26 |
* @param array $args Arguments to filter the records by. |
| 27 |
* |
| 28 |
* @return array Stream Records |
| 29 |
*/ |
| 30 |
public function query( $args ) { |
| 31 |
global $wpdb; |
| 32 |
|
| 33 |
$join = ''; |
| 34 |
$where = ''; |
| 35 |
|
| 36 |
/** |
| 37 |
* PARSE CORE PARAMS |
| 38 |
*/ |
| 39 |
if ( is_numeric( $args['site_id'] ) ) { |
| 40 |
$where .= $wpdb->prepare( " AND $wpdb->stream.site_id = %d", $args['site_id'] ); |
| 41 |
} |
| 42 |
|
| 43 |
if ( is_numeric( $args['blog_id'] ) ) { |
| 44 |
$where .= $wpdb->prepare( " AND $wpdb->stream.blog_id = %d", $args['blog_id'] ); |
| 45 |
} |
| 46 |
|
| 47 |
if ( is_numeric( $args['object_id'] ) ) { |
| 48 |
$where .= $wpdb->prepare( " AND $wpdb->stream.object_id = %d", $args['object_id'] ); |
| 49 |
} |
| 50 |
|
| 51 |
if ( is_numeric( $args['user_id'] ) ) { |
| 52 |
$where .= $wpdb->prepare( " AND $wpdb->stream.user_id = %d", $args['user_id'] ); |
| 53 |
} |
| 54 |
|
| 55 |
if ( ! empty( $args['user_role'] ) ) { |
| 56 |
$where .= $wpdb->prepare( " AND $wpdb->stream.user_role = %s", $args['user_role'] ); |
| 57 |
} |
| 58 |
|
| 59 |
if ( ! empty( $args['search'] ) ) { |
| 60 |
$field = ! empty( $args['search_field'] ) ? $args['search_field'] : 'summary'; |
| 61 |
|
| 62 |
// Sanitize field. |
| 63 |
if ( in_array( $field, self::ALLOWED_FIELDS, true ) ) { |
| 64 |
$where .= $wpdb->prepare( " AND $wpdb->stream.{$field} LIKE %s", "%{$args['search']}%" ); // @codingStandardsIgnoreLine can't prepare column name |
| 65 |
} |
| 66 |
} |
| 67 |
|
| 68 |
if ( ! empty( $args['connector'] ) ) { |
| 69 |
$where .= $wpdb->prepare( " AND $wpdb->stream.connector = %s", $args['connector'] ); |
| 70 |
} |
| 71 |
|
| 72 |
if ( ! empty( $args['context'] ) ) { |
| 73 |
$where .= $wpdb->prepare( " AND $wpdb->stream.context = %s", $args['context'] ); |
| 74 |
} |
| 75 |
|
| 76 |
if ( ! empty( $args['action'] ) ) { |
| 77 |
$where .= $wpdb->prepare( " AND $wpdb->stream.action = %s", $args['action'] ); |
| 78 |
} |
| 79 |
|
| 80 |
if ( ! empty( $args['ip'] ) ) { |
| 81 |
$where .= $wpdb->prepare( " AND $wpdb->stream.ip = %s", wp_stream_filter_var( $args['ip'], FILTER_VALIDATE_IP ) ); |
| 82 |
} |
| 83 |
|
| 84 |
/** |
| 85 |
* PARSE DATE PARAM FAMILY |
| 86 |
*/ |
| 87 |
if ( ! empty( $args['date'] ) ) { |
| 88 |
$args['date_from'] = $args['date']; |
| 89 |
$args['date_to'] = $args['date']; |
| 90 |
} |
| 91 |
|
| 92 |
if ( ! empty( $args['date_from'] ) ) { |
| 93 |
$date = get_gmt_from_date( gmdate( 'Y-m-d H:i:s', strtotime( $args['date_from'] . ' 00:00:00' ) ) ); |
| 94 |
$where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) >= %s", $date ); |
| 95 |
} |
| 96 |
|
| 97 |
if ( ! empty( $args['date_to'] ) ) { |
| 98 |
$date = get_gmt_from_date( gmdate( 'Y-m-d H:i:s', strtotime( $args['date_to'] . ' 23:59:59' ) ) ); |
| 99 |
$where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) <= %s", $date ); |
| 100 |
} |
| 101 |
|
| 102 |
if ( ! empty( $args['date_after'] ) ) { |
| 103 |
$date = get_gmt_from_date( gmdate( 'Y-m-d H:i:s', strtotime( $args['date_after'] ) ) ); |
| 104 |
$where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) > %s", $date ); |
| 105 |
} |
| 106 |
|
| 107 |
if ( ! empty( $args['date_before'] ) ) { |
| 108 |
$date = get_gmt_from_date( gmdate( 'Y-m-d H:i:s', strtotime( $args['date_before'] ) ) ); |
| 109 |
$where .= $wpdb->prepare( " AND DATE($wpdb->stream.created) < %s", $date ); |
| 110 |
} |
| 111 |
|
| 112 |
/** |
| 113 |
* PARSE __IN PARAM FAMILY |
| 114 |
*/ |
| 115 |
$ins = array(); |
| 116 |
|
| 117 |
foreach ( $args as $arg => $value ) { |
| 118 |
if ( '__in' === substr( $arg, -4 ) ) { |
| 119 |
$ins[ $arg ] = $value; |
| 120 |
} |
| 121 |
} |
| 122 |
|
| 123 |
if ( ! empty( $ins ) ) { |
| 124 |
foreach ( $ins as $key => $value ) { |
| 125 |
if ( empty( $value ) || ! is_array( $value ) ) { |
| 126 |
continue; |
| 127 |
} |
| 128 |
|
| 129 |
$field = str_replace( array( 'record_', '__in' ), '', $key ); |
| 130 |
$field = empty( $field ) ? 'ID' : $field; |
| 131 |
$type = is_numeric( array_shift( $value ) ) ? '%d' : '%s'; |
| 132 |
|
| 133 |
if ( ! empty( $value ) ) { |
| 134 |
$format = '(' . join( ',', array_fill( 0, count( $value ), $type ) ) . ')'; |
| 135 |
$where .= $wpdb->prepare( " AND $wpdb->stream.%s IN {$format}", $field, $value ); // @codingStandardsIgnoreLine prepare okay |
| 136 |
} |
| 137 |
} |
| 138 |
} |
| 139 |
|
| 140 |
/** |
| 141 |
* PARSE __NOT_IN PARAM FAMILY |
| 142 |
*/ |
| 143 |
$not_ins = array(); |
| 144 |
|
| 145 |
foreach ( $args as $arg => $value ) { |
| 146 |
if ( '__not_in' === substr( $arg, -8 ) ) { |
| 147 |
$not_ins[ $arg ] = $value; |
| 148 |
} |
| 149 |
} |
| 150 |
|
| 151 |
if ( ! empty( $not_ins ) ) { |
| 152 |
foreach ( $not_ins as $key => $value ) { |
| 153 |
if ( empty( $value ) || ! is_array( $value ) ) { |
| 154 |
continue; |
| 155 |
} |
| 156 |
|
| 157 |
$field = str_replace( array( 'record_', '__not_in' ), '', $key ); |
| 158 |
$field = empty( $field ) ? 'ID' : $field; |
| 159 |
$type = is_numeric( array_shift( $value ) ) ? '%d' : '%s'; |
| 160 |
|
| 161 |
if ( ! empty( $value ) ) { |
| 162 |
$format = '(' . join( ',', array_fill( 0, count( $value ), $type ) ) . ')'; |
| 163 |
$where .= $wpdb->prepare( " AND $wpdb->stream.%s NOT IN {$format}", $field, $value ); // @codingStandardsIgnoreLine prepare okay |
| 164 |
} |
| 165 |
} |
| 166 |
} |
| 167 |
|
| 168 |
/** |
| 169 |
* PARSE PAGINATION PARAMS |
| 170 |
*/ |
| 171 |
$limits = ''; |
| 172 |
$page = absint( $args['paged'] ); |
| 173 |
$per_page = absint( $args['records_per_page'] ); |
| 174 |
|
| 175 |
if ( $per_page >= 0 ) { |
| 176 |
$offset = absint( ( $page - 1 ) * $per_page ); |
| 177 |
$limits = "LIMIT {$offset}, {$per_page}"; |
| 178 |
} |
| 179 |
|
| 180 |
/** |
| 181 |
* PARSE ORDER PARAMS |
| 182 |
*/ |
| 183 |
$orderable = array( 'ID', 'site_id', 'blog_id', 'object_id', 'user_id', 'user_role', 'summary', 'created', 'connector', 'context', 'action' ); |
| 184 |
|
| 185 |
// Default to sorting by record ID. |
| 186 |
$orderby = "$wpdb->stream.ID"; |
| 187 |
|
| 188 |
if ( in_array( $args['orderby'], $orderable, true ) ) { |
| 189 |
$orderby = sprintf( '%s.%s', $wpdb->stream, $args['orderby'] ); |
| 190 |
} elseif ( 'meta_value_num' === $args['orderby'] && ! empty( $args['meta_key'] ) ) { |
| 191 |
$orderby = "CAST($wpdb->streammeta.meta_value AS SIGNED)"; |
| 192 |
} elseif ( 'meta_value' === $args['orderby'] && ! empty( $args['meta_key'] ) ) { |
| 193 |
$orderby = "$wpdb->streammeta.meta_value"; |
| 194 |
} |
| 195 |
|
| 196 |
// Show the recent records first by default. |
| 197 |
$order = 'DESC'; |
| 198 |
if ( 'ASC' === strtoupper( $args['order'] ) ) { |
| 199 |
$order = 'ASC'; |
| 200 |
} |
| 201 |
|
| 202 |
$orderby = sprintf( 'ORDER BY %s %s', $orderby, $order ); |
| 203 |
|
| 204 |
/** |
| 205 |
* PARSE FIELDS PARAMETER |
| 206 |
*/ |
| 207 |
$fields = (array) $args['fields']; |
| 208 |
$selects = array(); |
| 209 |
|
| 210 |
// Column names cannot be passed through $wpdb->prepare(), so restrict |
| 211 |
// the selectable fields to a known allowlist to prevent SQL injection |
| 212 |
// via the `fields` argument. |
| 213 |
if ( ! empty( $fields ) ) { |
| 214 |
foreach ( $fields as $field ) { |
| 215 |
// We'll query the meta table later. |
| 216 |
if ( 'meta' === $field ) { |
| 217 |
continue; |
| 218 |
} |
| 219 |
|
| 220 |
if ( ! in_array( $field, self::ALLOWED_FIELDS, true ) ) { |
| 221 |
continue; |
| 222 |
} |
| 223 |
|
| 224 |
$selects[] = sprintf( "$wpdb->stream.%s", $field ); |
| 225 |
} |
| 226 |
} |
| 227 |
|
| 228 |
if ( empty( $selects ) ) { |
| 229 |
$selects[] = "$wpdb->stream.*"; |
| 230 |
} |
| 231 |
|
| 232 |
$select = implode( ', ', $selects ); |
| 233 |
|
| 234 |
/** |
| 235 |
* Filters query WHERE statement as an alternative to filtering |
| 236 |
* the $query using the hook below. |
| 237 |
* |
| 238 |
* @param string $where WHERE statement. |
| 239 |
* |
| 240 |
* @return string |
| 241 |
*/ |
| 242 |
$where = apply_filters( 'wp_stream_db_query_where', $where ); |
| 243 |
|
| 244 |
/** |
| 245 |
* BUILD THE FINAL QUERY |
| 246 |
*/ |
| 247 |
$query = "SELECT {$select} |
| 248 |
FROM $wpdb->stream |
| 249 |
{$join} |
| 250 |
WHERE 1=1 {$where} |
| 251 |
{$orderby} |
| 252 |
{$limits}"; |
| 253 |
|
| 254 |
/** |
| 255 |
* Filter allows the final query to be modified before execution |
| 256 |
* |
| 257 |
* @param string $query |
| 258 |
* @param array $args |
| 259 |
* |
| 260 |
* @return string |
| 261 |
*/ |
| 262 |
$query = apply_filters( 'wp_stream_db_query', $query, $args ); |
| 263 |
|
| 264 |
// Build result count query. |
| 265 |
$count_query = "SELECT COUNT(*) as found |
| 266 |
FROM $wpdb->stream |
| 267 |
{$join} |
| 268 |
WHERE 1=1 {$where}"; |
| 269 |
|
| 270 |
/** |
| 271 |
* Filter allows the result count query to be modified before execution. |
| 272 |
* |
| 273 |
* @param string $query |
| 274 |
* @param array $args |
| 275 |
* |
| 276 |
* @return string |
| 277 |
*/ |
| 278 |
$count_query = apply_filters( 'wp_stream_db_count_query', $count_query, $args ); |
| 279 |
|
| 280 |
/** |
| 281 |
* QUERY THE DATABASE FOR RESULTS |
| 282 |
*/ |
| 283 |
$result = array( |
| 284 |
'items' => $wpdb->get_results( $query ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 285 |
'count' => absint( $wpdb->get_var( $count_query ) ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 286 |
); |
| 287 |
|
| 288 |
return $result; |
| 289 |
} |
| 290 |
} |
| 291 |
|
