1
\$\begingroup\$

I have a WordPress site with multiple custom post types, that all share a common custom taxonomy. Users can also be tagged with terms from this taxonomy.

After giving up on JOINS, I managed to conjure the following query to get all of the posts and users based on a term_ID.

global $wpdb;
$sql = "
    SELECT
        P.ID,
        P.post_type
    FROM
        $wpdb->posts P,
        $wpdb->term_relationships TR,
        $wpdb->term_taxonomy TT,
        $wpdb->terms T
    WHERE
        P.ID = TR.object_id AND TR.term_taxonomy_id = TT.term_taxonomy_id AND TT.term_id = T.term_id AND T.term_id = %d AND NOT P.post_status = 'trash'
    UNION
    SELECT
        U.ID,
        U.user_email
    FROM
        $wpdb->users U,
        $wpdb->term_relationships TR,
        $wpdb->term_taxonomy TT,
        $wpdb->terms T
    WHERE
        U.ID = TR.object_id AND TR.term_taxonomy_id = TT.term_taxonomy_id AND TT.term_id = T.term_id AND T.term_id = %d
";
$query = $wpdb->get_results( $wpdb->prepare( $sql, $term_id, $term_id ), OBJECT );

This works and gets the job done, but I have a feeling that there's room for improvement (performance-/efficiency-wise, mostly). Any suggestions?

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.