BP_Messages_Message::get( array $args = array() )

	public static function get( $args = array() ) {
		global $wpdb;

		$bp = buddypress();

		$defaults = array(
			'orderby'           => 'date_sent',
			'order'             => 'DESC',
			'per_page'          => 20,
			'page'              => 1,
			'user_id'           => 0,
			'meta_query'        => false,
			'date_query'        => false,
			'include'           => false,
			'exclude'           => false,
			'include_threads'   => false,
			'exclude_threads'   => false,
			'meta_key__in'      => false,
			'meta_key__not_in'  => false,
			'update_meta_cache' => true,
			'fields'            => 'all',
			'group_by'          => '',
			'subject'           => '',
			'count_total'       => false,
		);

		$r = bp_parse_args( $args, $defaults, 'bp_messages_message_get' );

		$sql = array(
			'select'     => 'SELECT DISTINCT m.id',
			'from'       => "{$bp->messages->table_name_messages} m",
			'where'      => '',
			'orderby'    => '',
			'pagination' => '',
		);

		if ( 'thread_ids' === $r['fields'] ) {
			$sql['select'] = 'SELECT DISTINCT m.thread_id';
		}

		if ( 'sender_ids' === $r['fields'] ) {
			$sql['select'] = 'SELECT DISTINCT m.sender_id';
		}

		$where_conditions = array();

		$meta_query_sql = self::get_meta_query_sql( $r['meta_query'] );

		if ( ! empty( $meta_query_sql['join'] ) ) {
			$sql['from'] .= $meta_query_sql['join'];
		}

		if ( ! empty( $meta_query_sql['where'] ) ) {
			$where_conditions['meta'] = $meta_query_sql['where'];
		}

		// Process date_query into SQL.
		$date_query_sql = self::get_date_query_sql( $r['date_query'] );

		if ( ! empty( $date_query_sql ) ) {
			$where_conditions['date'] = $date_query_sql;
		}

		/**
		 * Meta key IN and NOT IN query
		 */

		if ( ! empty( $r['meta_key__in'] ) || ! empty( $r['meta_key__not_in'] ) ) {
			$sql['from'] .= " INNER JOIN {$bp->messages->table_name_meta} mm ON m.id = mm.message_id";
		}

		if ( ! empty( $r['meta_key__in'] ) ) {
			$meta_key_in                 = implode( "','", wp_parse_slug_list( $r['meta_key__in'] ) );
			$where_conditions['meta_in'] = "mm.meta_key IN ('{$meta_key_in}')";
		}

		if ( ! empty( $r['meta_key__not_in'] ) ) {
			$meta_key_not_in                 = implode( "','", wp_parse_slug_list( $r['meta_key__not_in'] ) );
			$where_conditions['meta_not_in'] = "mm.meta_key NOT IN ('{$meta_key_not_in}')";
		}

		if ( ! empty( $r['user_id'] ) ) {
			$where_conditions['user'] = $wpdb->prepare( 'm.sender_id = %d', $r['user_id'] );
		}

		if ( ! empty( $r['include'] ) ) {
			$include                     = implode( ',', wp_parse_id_list( $r['include'] ) );
			$where_conditions['include'] = "m.id IN ({$include})";
		}

		if ( ! empty( $r['exclude'] ) ) {
			$exclude                     = implode( ',', wp_parse_id_list( $r['exclude'] ) );
			$where_conditions['exclude'] = "m.id NOT IN ({$exclude})";
		}

		if ( ! empty( $r['include_threads'] ) ) {
			$include_threads                     = implode( ',', wp_parse_id_list( $r['include_threads'] ) );
			$where_conditions['include_threads'] = "m.thread_id IN ({$include_threads})";
		}

		if ( ! empty( $r['exclude_threads'] ) ) {
			$exclude_threads                     = implode( ',', wp_parse_id_list( $r['exclude_threads'] ) );
			$where_conditions['exclude_threads'] = "m.thread_id NOT IN ({$exclude_threads})";
		}

		// Get the message with not matching subject.
		if ( ! empty( $r['subject'] ) ) {
			$where_conditions['subject'] = $wpdb->prepare( 'm.subject != %s', $r['subject'] );
		}

		/* Order/orderby ********************************************/

		$order   = $r['order'];
		$orderby = $r['orderby'];

		// Sanitize 'order'.
		$order = bp_esc_sql_order( $order );

		/**
		 * Filters the converted 'orderby' term.
		 *
		 * @since BuddyBoss 1.5.4
		 *
		 * @param string $value   Converted 'orderby' term.
		 * @param string $orderby Original orderby value.
		 */
		$orderby = apply_filters( 'bp_messages_message_get_orderby', self::convert_orderby_to_order_by_term( $orderby ), $orderby );

		$sql['orderby'] = "ORDER BY {$orderby} {$order}";

		if ( ! empty( $r['per_page'] ) && ! empty( $r['page'] ) && - 1 !== $r['per_page'] ) {
			$sql['pagination'] = $wpdb->prepare( 'LIMIT %d, %d', intval( ( $r['page'] - 1 ) * $r['per_page'] ), intval( $r['per_page'] ) );
		}

		/**
		 * Filters the Where SQL statement.
		 *
		 * @since BuddyBoss 1.5.4
		 *
		 * @param array $r                Array of parsed arguments for the get method.
		 * @param array $where_conditions Where conditions SQL statement.
		 */
		$where_conditions = apply_filters( 'bp_messages_message_get_where_conditions', $where_conditions, $r );

		$where = '';
		if ( ! empty( $where_conditions ) ) {
			$sql['where'] = implode( ' AND ', $where_conditions );
			$where        = "WHERE {$sql['where']}";
		}

		/**
		 * Filters the From SQL statement.
		 *
		 * @since BuddyBoss 1.5.4
		 *
		 * @param array  $r   Array of parsed arguments for the get method.
		 * @param string $sql From SQL statement.
		 */
		$sql['from'] = apply_filters( 'bp_messages_message_get_join_sql', $sql['from'], $r );

		$paged_messages_sql = "{$sql['select']} FROM {$sql['from']} {$where} {$sql['orderby']} {$sql['pagination']}";


		/**
		 * Filters the pagination SQL statement.
		 *
		 * @since BuddyBoss 1.5.4
		 *
		 * @param string $value Concatenated SQL statement.
		 * @param array  $sql   Array of SQL parts before concatenation.
		 * @param array  $r     Array of parsed arguments for the get method.
		 */
		$paged_messages_sql = apply_filters( 'bp_messages_message_get_paged_sql', $paged_messages_sql, $sql, $r );

		$paged_message_ids = $wpdb->get_col( $paged_messages_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		$paged_messages = array();

		if ( 'ids' === $r['fields'] || 'thread_ids' === $r['fields'] || 'sender_ids' === $r['fields'] ) {
			// We only want the IDs.
			$paged_messages = array_map( 'intval', $paged_message_ids );
		} elseif ( ! empty( $paged_message_ids ) ) {
			$message_ids_sql      = implode( ',', array_map( 'intval', $paged_message_ids ) );
			$message_data_objects = $wpdb->get_results( "SELECT m.* FROM {$bp->messages->table_name_messages} m WHERE m.id IN ({$message_ids_sql})" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
			foreach ( (array) $message_data_objects as $mdata ) {
				$message_data_objects[ $mdata->id ] = $mdata;
			}
			foreach ( $paged_message_ids as $paged_message_id ) {
				$paged_messages[] = $message_data_objects[ $paged_message_id ];
			}
		}

		$retval = array(
			'messages' => $paged_messages,
			'total'    => 0,
		);

		if ( ! empty( $r['count_total'] ) ) {
			// Find the total number of messages in the results set.
			$total_messages_sql = "SELECT COUNT(DISTINCT m.id) FROM {$sql['from']} $where";

			/**
			 * Filters the SQL used to retrieve total message results.
			 *
			 * @since BuddyBoss 1.5.4
			 *
			 * @param string $t_sql     Concatenated SQL statement used for retrieving total messages results.
			 * @param array  $total_sql Array of SQL parts for the query.
			 * @param array  $r         Array of parsed arguments for the get method.
			 */
			$total_messages_sql = apply_filters( 'bp_messages_message_get_total_sql', $total_messages_sql, $sql, $r );

			$total_messages  = (int) $wpdb->get_var( $total_messages_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
			$retval['total'] = $total_messages;
		}

		return $retval;
	}