Support » Plugin: Exports and Reports » [Plugin: Exports and Reports] Badly Needed QUERY POST TITLE and its TAGS

Viewing 15 replies - 1 through 15 (of 20 total)
  • I’d like to know about this too – how do you query two tables? I’d like post data and postmeta data.

    Scratch my earlier post, I figured it out here – http://wordpress.org/support/topic/plugin-exports-and-reports-query-advanced-custom-fields-wp_postmeta-with-wp_posts-and-tags

    But I still wonder if anyone knows how to query the post info with the tags? Something like:

    SELECT
    p.ID,p.post_title,p.post_content,p.post_name,p.post_status,
    m1.term_id AS name
    FROM wp_posts AS p
    LEFT JOIN wp_terms AS m1 ON m1.post_id = p.ID AND m1.name = ‘My_Tag’
    WHERE p.post_type = ‘post’

    I know that’s wrong — I think it needs wp_term_relationships somehow? And it’d be ideal to pull all the tags, not one-by-one.
    Thank you!!

    Hello waking media, we have the same issue, its a bit easy when combining to two tables but it is complicated when the wp_term table is what you want to get along with its posts title.. Its hard to query the post title with the TAGS inside each post I hope some query experts can help us, it is the same problem i got until these time, I emailed the plugin author but he has no response. If someone figure this out please post the solution here THANKS!

    Plugin Author Scott Kingsley Clark

    (@sc0ttkclark)

    Try checking out the query here, it’s for looking up posts by tags, but you should be able to see how it JOINs and attempt to get what you need out of it. In the bottom of your query, try to use a GROUP BY p.ID so it groups by the post ID, and then in your SELECT for the tags, use a GROUP_CONCAT(DISTINCT term.name ORDER BY term.name ASC SEPARATOR ‘|’) to separate all of the tags by a pipe “|”.

    WordPress custom select query – post ID by both tags

    GROUP_CONCAT Information

    Hi Scott,

    Thank you so much for your reply! I think it might be slightly over my head — but I am trying to figure it out.

    One question: What would I put in the Field Name spot to display the tags?

    Thanks!

    Just like on StackOverflow, this seems to return the right posts, without duplicates:

    SELECT
    p .ID,p.post_title,p.post_content,p.post_name,p.post_status

    FROM wp_posts AS p
    INNER JOIN wp_term_relationships AS rel1
    ON p.ID = rel1.object_id
    INNER JOIN wp_term_taxonomy AS tax1
    ON rel1.term_taxonomy_id = tax1.term_taxonomy_id
    INNER JOIN wp_terms AS term1
    ON tax1.term_id = term1.term_id

    INNER JOIN wp_term_relationships AS rel2
    ON p.ID = rel2.object_id
    INNER JOIN wp_term_taxonomy AS tax2
    ON rel2.term_taxonomy_id = tax2.term_taxonomy_id
    INNER JOIN wp_terms AS term2
    ON tax2.term_id = term2.term_id

    WHERE p.post_status = ‘publish’ AND p.post_type = ‘post’
    GROUP BY p.ID

    But: I don’t know what to put in “Field Name” spot to display the tags, and I don’t know how to use this:
    GROUP_CONCAT(DISTINCT term.name ORDER BY term.name ASC SEPARATOR ‘|’)

    Help?

    Hello thanks Sr scoth, Can I have a sample advice I think a sample QUERY i only want to show the POST TITLE, POST URL and ITS TAGS, my sql is curently this, when i read your advice it is actually what im looking for i hope i can these THANK YOU SO MUCH SIR!

    SELECT ID,post_title,guid,post_date FROM wp_posts WHERE post_type='post' AND post_status ='publish'

    this is what I need, the only thing missing is the tags per each title post.

    THEN I got these upon trying but this display every TAGS in the database.

    SELECT wp_posts.ID, wp_posts.post_title, wp_terms.name, wp_posts.guid
    FROM wp_posts
    LEFT JOIN wp_terms  ON wp_posts.ID
    
    WHERE  wp_posts.post_status ='publish'  AND wp_posts.post_status ='publish'

    THANKS IN ADVANCE SIR

    Hey again,

    Not sure if this helps, but this works to display the post’s first tag under the Field Name “name” and if you add the field “taxonomy” you can see that it is selecting for “post_tag”:

    SELECT
    p .ID,p.post_title,p.post_content,p.post_name,p.post_status,tax1.taxonomy,term1.name
    
    FROM wp_posts AS p
      INNER JOIN wp_term_relationships AS rel1
        ON p.ID = rel1.object_id
      INNER JOIN wp_term_taxonomy AS tax1
        ON rel1.term_taxonomy_id = tax1.term_taxonomy_id
      INNER JOIN wp_terms AS term1
        ON tax1.term_id = term1.term_id
    
      INNER JOIN wp_term_relationships AS rel2
        ON p.ID = rel2.object_id
      INNER JOIN wp_term_taxonomy AS tax2
        ON rel2.term_taxonomy_id = tax2.term_taxonomy_id
      INNER JOIN wp_terms AS term2
        ON tax2.term_id = term2.term_id
    
    WHERE p.post_status = 'publish' AND p.post_type = 'post' AND tax1.taxonomy = 'post_tag'
    GROUP BY p.ID

    I think the key is with this thing:
    GROUP_CONCAT(DISTINCT term.name ORDER BY term.name ASC SEPARATOR ‘|’)
    But nothing I try works right.

    Or in my case, with the “Advanced Custom Fields” it looks like this:

    SELECT
    p.ID,p.post_title,p.post_content,p.post_name,p.post_status,name,taxonomy,
    m1.meta_value AS My_First_Advanced_Custom_Field,
    m2.meta_value AS My_Second_Advanced_Custom_Field,
    m3.meta_value AS My_Third_Advanced_Custom_Field,
    m4.meta_value AS My_Fourth_Advanced_Custom_Field
    FROM wp_posts AS p
    LEFT JOIN wp_postmeta AS m1 ON m1.post_id = p.ID AND m1.meta_key = ‘My_First_Advanced_Custom_Field’
    LEFT JOIN wp_postmeta AS m2 ON m2.post_id = p.ID AND m2.meta_key = ‘My_Second_Advanced_Custom_Field’
    LEFT JOIN wp_postmeta AS m3 ON m3.post_id = p.ID AND m3.meta_key = ‘My_Third_Advanced_Custom_Field’
    LEFT JOIN wp_postmeta AS m4 ON m4.post_id = p.ID AND m4.meta_key = ‘My_Fourth_Advanced_Custom_Field’
    RIGHT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
    LEFT JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
    LEFT JOIN wp_terms AS t ON t.term_id = tt.term_id
    WHERE p.post_type = ‘post’ AND p.post_status = ‘publish’ AND taxonomy = ‘post_tag’
    GROUP BY p.ID

    That works to show only the first tag. How can I get it to show them all?

    Plugin Author Scott Kingsley Clark

    (@sc0ttkclark)

    My GROUP_CONCAT(DISTINCT term.name ORDER BY term.name ASC SEPARATOR ‘|’) example should be used in a SELECT like this:

    GROUP_CONCAT(DISTINCT term.name ORDER BY term.name ASC SEPARATOR '|') AS tags

    And in your field editor, just enter “tags” as the field name and in the “real field name” put in the group concat part: “GROUP_CONCAT(DISTINCT term.name ORDER BY term.name ASC SEPARATOR ‘|’)”

    YES! That did it! Thank you!!

    So, for dekzgimutao – joining just the posts and tags is like this:

    SELECT
    p.ID,p.post_title,p.post_content,p.post_name,p.post_status,name,taxonomy,
    GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '|') AS 'tags'
    FROM wp_posts AS p
    RIGHT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
    LEFT JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
    LEFT JOIN wp_terms AS t ON t.term_id = tt.term_id
    WHERE p.post_type = 'post' AND p.post_status = 'publish' AND taxonomy = 'post_tag'
    GROUP BY p.ID

    And as Scott says, create the Field Name “tags” and in the “real field name” put: “GROUP_CONCAT(DISTINCT term.name ORDER BY term.name ASC SEPARATOR ‘|’)”

    And to join the posts + Advanced Custom Fields + tags is like this:

    SELECT
    p.ID,p.post_title,p.post_content,p.post_name,p.post_status,name,taxonomy,
    m1.meta_value AS My_First_Advanced_Custom_Field,
    m2.meta_value AS My_Second_Advanced_Custom_Field,
    m3.meta_value AS My_Third_Advanced_Custom_Field,
    m4.meta_value AS My_Fourth_Advanced_Custom_Field,
    GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '|') AS 'tags'
    FROM wp_posts AS p
    LEFT JOIN wp_postmeta AS m1 ON m1.post_id = p.ID AND m1.meta_key = 'My_First_Advanced_Custom_Field'
    LEFT JOIN wp_postmeta AS m2 ON m2.post_id = p.ID AND m2.meta_key = 'My_Second_Advanced_Custom_Field'
    LEFT JOIN wp_postmeta AS m3 ON m3.post_id = p.ID AND m3.meta_key = 'My_Third_Advanced_Custom_Field'
    LEFT JOIN wp_postmeta AS m4 ON m4.post_id = p.ID AND m4.meta_key = 'My_Fourth_Advanced_Custom_Field'
    RIGHT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
    LEFT JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
    LEFT JOIN wp_terms AS t ON t.term_id = tt.term_id
    WHERE p.post_type = 'post' AND p.post_status = 'publish' AND taxonomy = 'post_tag'
    GROUP BY p.ID

    And again, don’t forget to create the “tags” field name per Scott’s instructions.

    This is wonderful!! Thank you again!!

    THANKS A LOT wakingmedia It sure happened and tag is shown but only one TAG is shown per POST title please help me I almost in it thanks in advance you made my day SIR

    Can you post your code?
    Did you create the field for “tags”?

    yes Sir I actually copied your code and made the field called name this is my code

    SELECT
    p.ID,p.post_title,p.guid,p.post_name,p.post_status,name,taxonomy,
    GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '|') AS 'tags'
    FROM wp_posts AS p
    RIGHT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
    LEFT JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
    LEFT JOIN wp_terms AS t ON t.term_id = tt.term_id
    WHERE p.post_type = 'post' AND p.post_status = 'publish' AND taxonomy = 'post_tag'
    GROUP BY p.ID

    THANKS IN ADVANCE SIR

    Your field should be called, “tags”, like this:
    Field Name = tags
    Real Field = GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR ‘|’)

    Is that what you created?

Viewing 15 replies - 1 through 15 (of 20 total)
  • The topic ‘[Plugin: Exports and Reports] Badly Needed QUERY POST TITLE and its TAGS’ is closed to new replies.