2

I'm exporting my WordPress posts data to an external tool* and I wanted to write a query that will get me the current published posts from the wp_posts table but as the latest revision (i.e. latest title, content, etc. but with the original post's ID).

select * from wp_posts where post_parent = 0;

This gets me all the original posts, but only in the state when they were first created, without taking the revisions into account. Then I tried something like this.

select * from wp_posts where post_parent != 0 group by post_parent;

But this only gives me the first revision and not the latest. I guess it would also exclude posts that were never revised, which doesn't work for me either.

I was thinking of joining the table on itself next, and seeing if I can filter it down to the max ID for a given post_parent, but wanted to know if anyone already had a solution before I try that.

How can I get all the posts, but with all their latest revisions applied? It must be possible since the site does when displaying posts.


*The tool in question is Solr, so I am looking to write a query to get all the fields I want to index, if you are curious.

  • 1
    You could just order the posts by their modified date? SQL column post_modified desc order – Ahmed Fouad Sep 26 '16 at 21:54
  • @AhmedMahdi Thanks, but wouldn't that give me all the revisions still? I'm trying to get just a single record per visible post on my site. – Kimberly W Sep 26 '16 at 23:20
  • 1
    For example: select * from wp_posts where 1=1 order by post_modified desc this will order your posts by most recent revised post first. last updated show up first. – Ahmed Fouad Sep 27 '16 at 7:20
2

Thanks to @AhmedMahdi's comment I figured out the following works for my situation:

select * from wp_posts
where post_status = 'publish'
order by post_modified desc;

The where condition is necessary to remove all the draft or private posts that I am not concerned with.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.