WPAS_Tickets_List::apply_ordering_criteria PHP Method

    public function apply_ordering_criteria($posts, $query)
    {
        if ($query->get('wpas_order_by_urgency')) {
            /**
             * Hooks in WP_Query should never modify SQL based on context.
             * Instead they should modify based on a query_var so they can
             * be tested and side-effects are minimized.
             */
            /**
             * @var wpdb $wpdb
             *
             */
            global $wpdb;
            $sql = <<<SQL
SELECT 
\twpas_ticket.ID AS ticket_id,
\twpas_ticket.post_title AS ticket_title,
\twpas_reply.ID AS reply_id,
\twpas_reply.post_title AS reply_title,
\twpas_replies.reply_count AS reply_count,
\twpas_replies.latest_reply,
\twpas_ticket.post_author=wpas_reply.post_author AS client_replied_last
FROM 
\t{$wpdb->posts} AS wpas_ticket 
\tINNER JOIN {$wpdb->postmeta} AS wpas_postmeta ON wpas_ticket.ID=wpas_postmeta.post_id AND '_wpas_status'=wpas_postmeta.meta_key AND 'open'=CAST(wpas_postmeta.meta_value AS CHAR)
\tLEFT OUTER JOIN {$wpdb->posts} AS wpas_reply ON wpas_ticket.ID=wpas_reply.post_parent
\tLEFT OUTER JOIN (
\t\tSELECT
\t\t\tpost_parent AS ticket_id,
\t\t\tCOUNT(*) AS reply_count,
\t\t\tMAX(post_date) AS latest_reply
\t\tFROM
\t\t\t{$wpdb->posts}
\t\tWHERE 1=1
\t\t\tAND 'ticket_reply' = post_type
\t\tGROUP BY
\t\t\tpost_parent
\t) wpas_replies ON wpas_replies.ticket_id=wpas_reply.post_parent AND wpas_replies.latest_reply=wpas_reply.post_date 
WHERE 1=1
\tAND wpas_replies.latest_reply IS NOT NULL
\tAND 'ticket_reply'=wpas_reply.post_type
ORDER BY
\twpas_replies.latest_reply ASC
SQL;
            $no_replies = $client_replies = $agent_replies = array();
            foreach ($posts as $post) {
                $no_replies[$post->ID] = $post;
            }
            /**
             * The post order will be modifiedusing the following logic:
             *
             * 		Order 	- 	Ticket State
             *		-----   	-------------------------------------------
             * 		 1st   	- 	No reply - older since request made
             * 	 	 2nd 	- 	No reply - newer since request made
             * 	 	 3rd 	- 	Reply - older response since client replied
             * 	 	 4th 	- 	Reply - newer response since client replied
             * 	 	 5th 	- 	Reply - newer response since agent replied
             * 	 	 6th 	- 	Reply - older response since agent replied
             */
            foreach ($wpdb->get_results($sql) as $reply_post) {
                if (isset($no_replies[$reply_post->ticket_id])) {
                    if ($reply_post->client_replied_last) {
                        $client_replies[$reply_post->ticket_id] = $no_replies[$reply_post->ticket_id];
                    } else {
                        $agent_replies[$reply_post->ticket_id] = $no_replies[$reply_post->ticket_id];
                    }
                    unset($no_replies[$reply_post->ticket_id]);
                }
            }
            $posts = array_values($no_replies + $client_replies + array_reverse($agent_replies, true));
        }
        return $posts;
    }