export_concrete5_posts_to_wordpress.sql

SELECT b1.bID as id,
       u.uID as post_author,
       b1.bDateAdded as post_date,
       CONVERT_TZ(b1.bDateAdded,'-05:00','+00:00') as post_date_gmt,
       b2.content as post_content,
       c2.cvName as post_title,
       c2.cvDescription as post_excerpt,
       IF(b1.bIsActive = 1,'publish','draft') as post_status,
       'open' as comment_status,
       'open' as ping_status,
       '' as post_password,
       c2.cvHandle as post_name,
       '' as to_ping,
       '' as pinged,
       b1.bDateModified as post_modified,
       CONVERT_TZ(b1.bDateAdded,'-05:00','+00:00') as post_modified_gmt,
       '' as post_content_filtered,
       0 as post_parent,
       CONCAT('http://your_domain.com/', c2.cvHandle) as guid,
       0 as menu_order,
       'post' as post_type,
       '' as post_mime_type,
       0 as comment_count
FROM Blocks b1
JOIN btContentLocal b2 ON b1.bID = b2.bID
JOIN Users u ON u.uID = b1.uID
JOIN (SELECT * FROM CollectionVersionBlocks WHERE bID IN (SELECT max(bID) FROM CollectionVersionBlocks GROUP BY bID)) c1 ON c1.bID = b1.bID
JOIN (SELECT * FROM CollectionVersions x1 WHERE cvID = (SELECT MAX(cvID) FROM CollectionVersions x2 WHERE x2.cID = x1.cID)) c2 ON c1.cID = c2.cID
WHERE b1.bFilename = 'blog_post'
ORDER BY b1.bID;