AP_QA_Query_Hooks::sql_filter( array $sql, Object $wp_query )

	public static function sql_filter( $sql, $wp_query ) {
		global $wpdb;

		// Do not filter if wp-admin.
		if ( is_admin() ) {
			return $sql;
		}

		if ( isset( $wp_query->query['ap_query'] ) ) {
			$sql['join']   = $sql['join'] . " LEFT JOIN {$wpdb->ap_qameta} qameta ON qameta.post_id = {$wpdb->posts}.ID";
			$sql['fields'] = $sql['fields'] . ', qameta.*, qameta.votes_up - qameta.votes_down AS votes_net';
			$post_status   = '';
			$query_status  = $wp_query->query['post_status'];

			if ( isset( $wp_query->query['ap_current_user_ignore'] ) && false === $wp_query->query['ap_current_user_ignore'] ) {
				// Build the post_status mysql query.
				if ( ! empty( $query_status ) ) {
					if ( is_array( $query_status ) ) {
						$i = 1;

						foreach ( get_post_stati() as $status ) {
							if ( in_array( $status, $wp_query->query['post_status'], true ) ) {
								$post_status .= $wpdb->posts . ".post_status = '" . $status . "'";

								if ( count( $query_status ) != $i ) { // phpcs:ignore Universal.Operators.StrictComparisons.LooseNotEqual
									$post_status .= ' OR ';
								} else {
									$post_status .= ')';
								}
								++$i;
							}
						}
					} else {
						$post_status .= $wpdb->posts . ".post_status = '" . $query_status . "' ";
					}
				}

				$pos = strpos( $sql['where'], $post_status );

				// Replace post_status query.
				if ( is_user_logged_in() && false !== $pos ) {
					$pos          = $pos + strlen( $post_status );
					$author_query = $wpdb->prepare( " OR ( {$wpdb->posts}.post_author = %d AND {$wpdb->posts}.post_status IN ('private_post') ) ", get_current_user_id() );
					$sql['where'] = substr_replace( $sql['where'], $author_query, $pos, 0 );
				}
			}

			// Hack to fix WP_Query for fetching anonymous author posts.
			if ( isset( $wp_query->query['author'] ) && 0 === $wp_query->query['author'] ) {
				$sql['where'] = $sql['where'] . $wpdb->prepare( " AND {$wpdb->posts}.post_author = %d", $wp_query->query['author'] );
			}

			$ap_order_by  = isset( $wp_query->query['ap_order_by'] ) ? $wp_query->query['ap_order_by'] : 'active';
			$answer_query = isset( $wp_query->query['ap_answers_query'] );

			if ( 'answers' === $ap_order_by && ! $answer_query ) {
				$sql['orderby'] = 'IFNULL(qameta.answers, 0) DESC, ' . $sql['orderby'];
			} elseif ( 'views' === $ap_order_by && ! $answer_query ) {
				$sql['orderby'] = 'IFNULL(qameta.views, 0) DESC, ' . $sql['orderby'];
			} elseif ( 'unanswered' === $ap_order_by && ! $answer_query ) {
				$sql['orderby'] = 'IFNULL(qameta.answers, 0) ASC,' . $sql['orderby'];
			} elseif ( 'voted' === $ap_order_by ) {
				$sql['orderby'] = 'CASE WHEN IFNULL(votes_net, 0) >= 0 THEN 1 ELSE 2 END ASC, ABS(votes_net) DESC, ' . $sql['orderby'];
			} elseif ( 'solved' === $ap_order_by && ! $answer_query ) {
				$sql['orderby'] = "if( qameta.selected_id = '' or qameta.selected_id is null, 0, 1 ) DESC," . $sql['orderby'];
			} elseif ( 'unsolved' === $ap_order_by && ! $answer_query ) {
				$sql['orderby'] = "if( qameta.selected_id = '' or qameta.selected_id is null, 1, 0 ) DESC," . $sql['orderby'];
			} elseif ( 'oldest' === $ap_order_by ) {
				$sql['orderby'] = "{$wpdb->posts}.post_date ASC";
			} elseif ( 'newest' === $ap_order_by ) {
				$sql['orderby'] = "{$wpdb->posts}.post_date DESC";
			} else {
				$sql['orderby'] = 'qameta.last_updated DESC ';
			}

			// Keep featured posts on top.
			if ( ! $answer_query ) {
				$sql['orderby'] = 'CASE WHEN IFNULL(qameta.featured, 0) =1 THEN 1 ELSE 2 END ASC, ' . $sql['orderby'];
			}

			// Keep best answer to top.
			if ( $answer_query && ! $wp_query->query['ignore_selected_answer'] ) {
				$sql['orderby'] = 'case when qameta.selected = 1 then 1 else 2 end, ' . $sql['orderby'];
			}

			// Allow filtering sql query.
			$sql = apply_filters( 'ap_qa_sql', $sql );

			$wp_query->count_request = $sql;
		}

		return $sql;
	}