Wordpress filter post by metavalue

Question

I am making a filter to get posts by status that are saved by post metavalue in a Wordpress plugin. It is a question and answer system, where the question and answer are post objects.

    // Filter post where
function posts_where( $where ) {
    global $wpdb, $dwqa_general_settings;

    switch ( $this->filter['filter_plus'] ) {
        case 'overdue' :
            $overdue_time_frame = isset($dwqa_general_settings['question-overdue-time-frame']) ? $dwqa_general_settings['question-overdue-time-frame'] : 2;
            $where .= " AND post_date < '" . date('Y-m-d H:i:s', strtotime('-'.$overdue_time_frame.' days') ) . "'";
        case 'open':
            // answered
            $where .= " AND ID NOT IN (
                SELECT `t1`.question FROM 
                    ( SELECT `{$wpdb->prefix}posts`.post_author, `{$wpdb->prefix}postmeta`.meta_value as `question`, `{$wpdb->prefix}posts`.post_date FROM `{$wpdb->prefix}posts` JOIN `{$wpdb->prefix}postmeta` ON `{$wpdb->prefix}posts`.ID = `{$wpdb->prefix}postmeta`.post_id WHERE `{$wpdb->prefix}posts`.post_type = 'dwqa-answer' AND ( `{$wpdb->prefix}posts`.post_status = 'publish' OR `{$wpdb->prefix}posts`.post_status = 'private' ) AND `{$wpdb->prefix}postmeta`.meta_key = '_question' ) as `t1`
                JOIN 
                    (SELECT `{$wpdb->prefix}postmeta`.meta_value as `question`, max(`{$wpdb->prefix}posts`.post_date) as `lastdate`  FROM `{$wpdb->prefix}posts` JOIN `{$wpdb->prefix}postmeta` on `{$wpdb->prefix}posts`.ID = `{$wpdb->prefix}postmeta`.post_id WHERE post_type = 'dwqa-answer' AND ( `{$wpdb->prefix}posts`.post_status = 'publish' OR `{$wpdb->prefix}posts`.post_status = 'private' ) AND `{$wpdb->prefix}postmeta`.meta_key = '_question' GROUP BY `{$wpdb->prefix}postmeta`.meta_value) as t2

                ON `t1`.question = `t2`.question AND `t1`.post_date = `t2`.lastdate

                JOIN `{$wpdb->prefix}usermeta` ON `t1`.post_author = `{$wpdb->prefix}usermeta`.user_id
                WHERE 1=1 AND `{$wpdb->prefix}usermeta`.meta_key = '{$wpdb->prefix}capabilities' AND ( 
                                `{$wpdb->prefix}usermeta`.meta_value LIKE '%administrator%' 
                                OR `{$wpdb->prefix}usermeta`.meta_value LIKE '%editor%' 
                                OR `{$wpdb->prefix}usermeta`.meta_value LIKE '%author%' 
                            ) ";
            if( current_user_can('edit_posts' ) ) {
                $where .= " AND ID NOT IN (
                                SELECT `{$wpdb->prefix}postmeta`.meta_value FROM 
                                    `{$wpdb->prefix}comments` 
                                JOIN 
                                    ( SELECT `{$wpdb->prefix}comments`.comment_ID, `{$wpdb->prefix}comments`.comment_post_ID, max( `{$wpdb->prefix}comments`.comment_date ) as comment_time FROM `{$wpdb->prefix}comments` 
                                     JOIN `{$wpdb->prefix}posts` ON `{$wpdb->prefix}comments`.comment_post_ID = `{$wpdb->prefix}posts`.ID 
                                     WHERE `{$wpdb->prefix}comments`.comment_approved = 1 AND `{$wpdb->prefix}posts`.post_type = 'dwqa-answer'
                                     GROUP BY `{$wpdb->prefix}comments`.comment_post_ID ) as t1 
                                ON `{$wpdb->prefix}comments`.comment_post_ID = t1.comment_post_ID AND `{$wpdb->prefix}comments`.comment_date = t1.comment_time 
                                JOIN `{$wpdb->prefix}usermeta` ON `{$wpdb->prefix}comments`.user_id = `{$wpdb->prefix}usermeta`.user_id
                                JOIN `{$wpdb->prefix}postmeta` ON `{$wpdb->prefix}postmeta`.post_id = `{$wpdb->prefix}comments`.comment_post_ID
                                WHERE 1=1 AND `{$wpdb->prefix}usermeta`.meta_key = '{$wpdb->prefix}capabilities' 
                                    AND `{$wpdb->prefix}usermeta`.meta_value NOT LIKE '%administrator%'
                                    AND `{$wpdb->prefix}usermeta`.meta_value NOT LIKE '%editor%' 
                                    AND `{$wpdb->prefix}usermeta`.meta_value NOT LIKE '%author%'
                                    AND `{$wpdb->prefix}postmeta`.meta_key = '_question'
                            ) ";
            }
            $where .= " )";

            break;
        case 'replied':
            // answered
            $where .= " AND ID IN (
                SELECT `t1`.question FROM 
                    ( SELECT `{$wpdb->prefix}posts`.post_author, `{$wpdb->prefix}postmeta`.meta_value as `question`, `{$wpdb->prefix}posts`.post_date FROM `{$wpdb->prefix}posts` JOIN `{$wpdb->prefix}postmeta` ON `{$wpdb->prefix}posts`.ID = `{$wpdb->prefix}postmeta`.post_id WHERE `{$wpdb->prefix}posts`.post_type = 'dwqa-answer' AND ( `{$wpdb->prefix}posts`.post_status = 'publish' OR `{$wpdb->prefix}posts`.post_status = 'private' ) AND `{$wpdb->prefix}postmeta`.meta_key = '_question' ) as `t1`
                JOIN 
                    (SELECT `{$wpdb->prefix}postmeta`.meta_value as `question`, max(`{$wpdb->prefix}posts`.post_date) as `lastdate`  FROM `{$wpdb->prefix}posts` JOIN `{$wpdb->prefix}postmeta` on `{$wpdb->prefix}posts`.ID = `{$wpdb->prefix}postmeta`.post_id WHERE post_type = 'dwqa-answer' AND ( `{$wpdb->prefix}posts`.post_status = 'publish' OR `{$wpdb->prefix}posts`.post_status = 'private' ) AND `{$wpdb->prefix}postmeta`.meta_key = '_question' GROUP BY `{$wpdb->prefix}postmeta`.meta_value) as t2

                ON `t1`.question = `t2`.question AND `t1`.post_date = `t2`.lastdate

                JOIN `{$wpdb->prefix}usermeta` ON `t1`.post_author = `{$wpdb->prefix}usermeta`.user_id
                WHERE 1=1 AND `{$wpdb->prefix}usermeta`.meta_key = '{$wpdb->prefix}capabilities' AND ( `{$wpdb->prefix}usermeta`.meta_value LIKE '%administrator%' 
                                OR `{$wpdb->prefix}usermeta`.meta_value LIKE '%editor%' 
                                OR `{$wpdb->prefix}usermeta`.meta_value LIKE '%author%' 
                            ) 
            )";
            break;
        default:
            # code...
            break;
    }
    return $where;
}

Review my MySQL code to make sure it performs well.

Answer 1

I remember answering a question that was similar to this one, in that it also needed a stored procedure.

this code could use several stored procedures. I don't know how sprocs work in MySQL but I assume that you can still code them.

it looks to me like you are trying to write a lot of SQL Code in the PHP, you can do this but it is a waste of the PHP Server Resources, let the Database do the work it is supposed to be doing.

you should be able to do the Case Statement in SQL as well. all of this should be SQL Code not SQL inside of PHP Logic.