WordPress.org

1. dekzgimutao
   Member
   Posted 4 months ago #

   Hello everyone I was wondering how to query the POST TITLE and the TAGS inside those POST. I have now queried the POST title POST date but Im missing the POST TAGS because it is located on different table and I dont know how to Select the TAGS inside each POST.
   Thank you, any reply is deeply appreciated thank you very much!

   http://wordpress.org/extend/plugins/exports-and-reports/

2. wakingmedia
   Member
   Posted 3 months ago #

   I'd like to know about this too - how do you query two tables? I'd like post data and postmeta data.

3. wakingmedia
   Member
   Posted 3 months ago #

   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!!

4. dekzgimutao
   Member
   Posted 3 months ago #

   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!

5. Scott Kingsley Clark
   Member
   Posted 3 months ago #

   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

6. wakingmedia
   Member
   Posted 3 months ago #

   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!

7. wakingmedia
   Member
   Posted 3 months ago #

   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?

8. dekzgimutao
   Member
   Posted 3 months ago #

   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

9. wakingmedia
   Member
   Posted 3 months ago #

   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.

10. wakingmedia
    Member
    Posted 3 months ago #

    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?

11. Scott Kingsley Clark
    Member
    Posted 3 months ago #

    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 '|')"

12. wakingmedia
    Member
    Posted 3 months ago #

    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!!

13. dekzgimutao
    Member
    Posted 3 months ago #

    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

14. wakingmedia
    Member
    Posted 3 months ago #

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

15. dekzgimutao
    Member
    Posted 3 months ago #

    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

16. wakingmedia
    Member
    Posted 3 months ago #

    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?

17. dekzgimutao
    Member
    Posted 3 months ago #

    Oh my thank you very much sir it gives me chills you actually made it happened I've been doing this for 2weeks now, querying with wordpress is really harp for me THANKS Sir THANK YOU VERY MUCH also with SIR scoth regards CHEERS :)

18. dekzgimutao
    Member
    Posted 3 months ago #

    In addition sir wakingmedia, Can I add another field and it will also have the tags but separated with comma, so it will be to fields in the report Tags separedted with comma and tags separated with |. Anyway thank you very much SIR

19. wakingmedia
    Member
    Posted 3 months ago #

    Yes, I think so -- you'd just add this to your code:

    GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR ', ') AS 'newfield'

    And create another field:
    Field Name: newfield
    Real Name: GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR ', ')

    I'm not really sure if that will work - but in theory...

20. dekzgimutao
    Member
    Posted 3 months ago #

    thank you very much sir waking media its working now, i'm just curious is it possible to put an echo or something before and end tags for example it'll be like these (tag1,tag2,tag3) thanks sir.

21. Scott Kingsley Clark
    Member
    Posted 3 months ago #

    put your GROUP_CONCAT(....) code within a CONCAT('(', GROUP_CONCAT(....), ')')

Reply

You must log in to post.