Wordpress custom query: cross-reference custom taxonomy from one post type with custom meta from another

Question

I'm having trouble with a custom Wordpress MySQL query. My situation is this: I have the regular post type 'post' for posts and a custom post type, 'authors', for information about authors. The 'authors' post type holds all authors, though not all need be authors of posts.

Each post (of types 'post' and 'author') has a custom taxonomy with the author's exact name (eg. "John Smith"). The post type 'author' has additional custom meta values for the author's first name and last name (as they can become complex and for easier ordering by last name, first name).

Now I'm trying to select all authors who've published a post, count their number of posts and show the meta values, associated with their name. I'm uncertain as to how to cross-reference the taxonomy from one post type ('post') with the meta values form another ('author') in one row.

What I want:

John Smith    John    Smith    10

What I've got so far:

John Smith    10    John
John Smith    10    Smith

I'm stuck with the query below. Any help will be much appreciated!

SELECT
    N, C, meta_value
    FROM
        (SELECT
            t.name AS N, count(*) AS C
                FROM
                    wp_2_posts p
                    INNER JOIN wp_2_term_relationships AS tr ON p.ID=tr.object_id
                    INNER JOIN wp_2_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                    INNER JOIN wp_2_terms AS t ON tt.term_id = t.term_id
                WHERE 1=1
                    AND tt.taxonomy = 'myauthor'
                    AND p.post_type = 'post'
                    AND p.post_status = 'publish'
                GROUP BY
                    N
                ORDER BY
                C DESC) AS x
        INNER JOIN wp_2_posts p2
        INNER JOIN wp_2_term_relationships AS tr2 ON p2.ID=tr2.object_id
        INNER JOIN wp_2_term_taxonomy AS tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
        INNER JOIN wp_2_terms AS t2 ON tt2.term_id = t2.term_id
        INNER JOIN wp_2_postmeta AS m2 ON m2.post_id = p2.ID
            WHERE 1=1
                AND post_type = 'author'
                AND t2.name = x.N
                AND (m2.meta_key = 'lastname' OR m2.meta_key = 'firstname')

and and · Accepted Answer · 2012-10-30T13:57:41

Here's a rather ungly solution, any ideas on how to improve it?

SELECT
    N, C, m2.meta_value AS L, m3.meta_value AS F
    FROM
        (SELECT
            t.name AS N, count(*) AS C
                FROM
                    wp_2_posts p
                    INNER JOIN wp_2_term_relationships AS tr ON p.ID=tr.object_id
                    INNER JOIN wp_2_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                    INNER JOIN wp_2_terms AS t ON tt.term_id = t.term_id
                WHERE 1=1
                    AND tt.taxonomy = 'myauthor'
                    AND p.post_type = 'post'
                    AND p.post_status = 'publish'
                GROUP BY
                    N) AS x
        INNER JOIN wp_2_posts p2
        INNER JOIN wp_2_term_relationships AS tr2 ON p2.ID=tr2.object_id
        INNER JOIN wp_2_term_taxonomy AS tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
        INNER JOIN wp_2_terms AS t2 ON tt2.term_id = t2.term_id
        INNER JOIN wp_2_postmeta AS m2 ON m2.post_id = p2.ID
        INNER JOIN wp_2_posts p3
        INNER JOIN wp_2_term_relationships AS tr3 ON p3.ID=tr3.object_id
        INNER JOIN wp_2_term_taxonomy AS tt3 ON tr3.term_taxonomy_id = tt3.term_taxonomy_id
        INNER JOIN wp_2_terms AS t3 ON tt3.term_id = t3.term_id
        INNER JOIN wp_2_postmeta AS m3 ON m3.post_id = p3.ID
            WHERE 1=1
                AND p3.post_type = 'author'
                AND t3.name = x.N
                AND m3.meta_key = 'firstname'
                AND p2.post_type = 'author'
                AND t2.name = x.N
                AND m2.meta_key = 'lastname'
    ORDER BY
        C DESC,
        L

Wordpress custom query: cross-reference custom taxonomy from one post type with custom meta from another

1 Answers