Skip to content

Instantly share code, notes, and snippets.

@jonathanstegall

jonathanstegall/wordpress-article-data.sql

Last active August 31, 2022 15:11
Show Gist options
  • Save jonathanstegall/60edeac8857e2aab47b593c63d20cf16 to your computer and use it in GitHub Desktop.
Save jonathanstegall/60edeac8857e2aab47b593c63d20cf16 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
wordpress-article-data.sql
SELECT
p.ID,
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( p.post_title, 0xE28098, "'" ), 0xE28099, "'" ), 0xE2809C, '"' ), 0xE2809D, '"' ), 0xE28093, '-' ), 0xE28094, '--' ), 0xE280A6, '...' ), CHAR( 145 ), "'" ), CHAR( 146 ), "'" ), CHAR( 147 ), '"' ), CHAR( 148 ), '"' ), CHAR( 150 ), '-' ), CHAR( 151 ), '--' ), CHAR( 133 ), '...' ) as title,
(
SELECT REPLACE(name, '&', '&' ) FROM wp_terms WHERE term_id IN
(
SELECT REPLACE( REPLACE( meta_value, 'a:1:{s:8:"category";s:5:"', '' ), '";}', '' ) as category_id
FROM wp_posts p2
LEFT OUTER JOIN wp_postmeta m ON p2.ID = m.post_id
WHERE p2.ID = p.ID AND m.meta_key = '_category_permalink'
)
) as permalink_category,
(
SELECT GROUP_CONCAT( DISTINCT REPLACE ( REPLACE( t.name, '&', '&' ), '"', "'" ) )
FROM wp_posts p2
INNER JOIN wp_postmeta m ON m.post_id = p2.ID
INNER JOIN wp_term_relationships r ON r.object_id = p2.ID
INNER JOIN wp_term_taxonomy tax ON r.term_taxonomy_id = tax.term_taxonomy_id
INNER JOIN wp_terms t ON tax.term_id = t.term_id
WHERE p2.ID = p.ID AND tax.taxonomy = 'category' AND t.name != 'Uncategorized'
) as all_categories,
(
SELECT GROUP_CONCAT( DISTINCT REPLACE ( REPLACE( t.name, '&', '&' ), '"', "'" ) )
FROM wp_posts p2
INNER JOIN wp_postmeta m ON m.post_id = p2.ID
INNER JOIN wp_term_relationships r ON r.object_id = p2.ID
INNER JOIN wp_term_taxonomy tax ON r.term_taxonomy_id = tax.term_taxonomy_id
INNER JOIN wp_terms t ON tax.term_id = t.term_id
WHERE p2.ID = p.ID AND tax.taxonomy = 'post_tag'
) as tags,
(
SELECT p3.post_title
FROM wp_posts p2
INNER JOIN wp_term_relationships r ON r.object_id = p2.ID
INNER JOIN wp_term_taxonomy tax ON r.term_taxonomy_id = tax.term_taxonomy_id
INNER JOIN wp_terms t ON tax.term_id = t.term_id
inner join wp_term_taxonomy tax2 on tax2.term_id = t.term_id
INNER JOIN wp_term_relationships r2 ON r2.term_taxonomy_id = tax2.term_taxonomy_id
inner join wp_posts p3 on p3.ID = r2.object_id
WHERE p2.ID = p.ID AND tax.taxonomy = 'author' and p3.post_type = 'guest-author'
order by r2.object_id asc
limit 1
) as first_author,
(
SELECT GROUP_CONCAT( DISTINCT REPLACE ( REPLACE( p3.post_title, '&', '&' ), '"', "'" ) order by r2.object_id asc )
FROM wp_posts p2
INNER JOIN wp_term_relationships r ON r.object_id = p2.ID
INNER JOIN wp_term_taxonomy tax ON r.term_taxonomy_id = tax.term_taxonomy_id
INNER JOIN wp_terms t ON tax.term_id = t.term_id
inner join wp_term_taxonomy tax2 on tax2.term_id = t.term_id
INNER JOIN wp_term_relationships r2 ON r2.term_taxonomy_id = tax2.term_taxonomy_id
inner join wp_posts p3 on p3.ID = r2.object_id
WHERE p2.ID = p.ID AND tax.taxonomy = 'author' and p3.post_type = 'guest-author'
) as all_authors,
(
SELECT REPLACE ( meta_value, '"', "'" )
FROM wp_postmeta m
WHERE m.post_id = p.ID and meta_key = '_mp_subtitle_settings_byline'
) as byline,
post_excerpt as excerpt,
ifnull(
CONCAT(
'https://www.minnpost.com/',
(
SELECT slug FROM wp_terms WHERE term_id IN
(
SELECT REPLACE( REPLACE( meta_value, 'a:1:{s:8:"category";s:5:"', '' ), '";}', '' ) as category_id
FROM wp_posts p2
LEFT OUTER JOIN wp_postmeta m ON p2.ID = m.post_id
WHERE p2.ID = p.ID AND m.meta_key = '_category_permalink'
)
),
DATE_FORMAT( post_date, '/%Y/%m/' ), p.post_name, '/'
),
CONCAT(
'https://www.minnpost.com/',
(
SELECT t.slug
FROM wp_posts p2
INNER JOIN wp_postmeta m ON m.post_id = p2.ID
INNER JOIN wp_term_relationships r ON r.object_id = p2.ID
INNER JOIN wp_term_taxonomy tax ON r.term_taxonomy_id = tax.term_taxonomy_id
INNER JOIN wp_terms t ON tax.term_id = t.term_id
WHERE p2.ID = p.ID AND tax.taxonomy = 'category' AND t.name != 'Uncategorized'
LIMIT 1
),
DATE_FORMAT( post_date, '/%Y/%m/' ), p.post_name, '/'
)
) as url,
post_date as publish_date,
post_modified as last_modified_date
FROM wp_posts p
WHERE p.post_type = 'post' AND post_status = 'publish' AND
YEAR(post_date) >= YEAR(CURDATE()) AND MONTH(post_date) = MONTH(CURDATE()) -1
ORDER BY p.ID DESC
;
@jonathanstegall
Copy link
Author

jonathanstegall commented Apr 29, 2019 •
edited
Loading

12/1/2017 was the last active Drupal version of this gist. Rewriting for WordPress on 4/29/2019

@jonathanstegall
Copy link
Author

jonathanstegall commented May 3, 2019 •
edited
Loading

When exporting, do this:

Terminate: pipe instead of comma
Wrap: two double quotes instead of one
Escape:
Line Terminate: \n

@jonathanstegall
Copy link
Author

updated for new data requirements on 8/31/22.

@jonathanstegall
Copy link
Author

jonathanstegall commented Aug 31, 2022 •
edited
Loading

For Glean-specific data from the last six months, do this:

SELECT
p.ID,
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( p.post_title, 0xE28098, "'" ), 0xE28099, "'" ), 0xE2809C, '"' ), 0xE2809D, '"' ), 0xE28093, '-' ), 0xE28094, '--' ), 0xE280A6, '...' ), CHAR( 145 ), "'" ), CHAR( 146 ), "'" ), CHAR( 147 ), '"' ), CHAR( 148 ), '"' ), CHAR( 150 ), '-' ), CHAR( 151 ), '--' ), CHAR( 133 ), '...' ) as title,
(
    SELECT p3.post_title
    FROM wp_posts p2
    INNER JOIN wp_term_relationships r ON r.object_id = p2.ID
    INNER JOIN wp_term_taxonomy tax ON r.term_taxonomy_id = tax.term_taxonomy_id
    INNER JOIN wp_terms t ON tax.term_id = t.term_id
    inner join wp_term_taxonomy tax2 on tax2.term_id = t.term_id
    INNER JOIN wp_term_relationships r2 ON r2.term_taxonomy_id = tax2.term_taxonomy_id
    inner join wp_posts p3 on p3.ID = r2.object_id
    WHERE p2.ID = p.ID AND tax.taxonomy = 'author' and p3.post_type = 'guest-author'
    order by r2.object_id asc
    limit 1
) as first_author,
(
    SELECT GROUP_CONCAT( DISTINCT REPLACE ( REPLACE( p3.post_title, '&', '&' ), '"', "'" ) order by r2.object_id asc )
    FROM wp_posts p2
    INNER JOIN wp_term_relationships r ON r.object_id = p2.ID
    INNER JOIN wp_term_taxonomy tax ON r.term_taxonomy_id = tax.term_taxonomy_id
    INNER JOIN wp_terms t ON tax.term_id = t.term_id
    inner join wp_term_taxonomy tax2 on tax2.term_id = t.term_id
    INNER JOIN wp_term_relationships r2 ON r2.term_taxonomy_id = tax2.term_taxonomy_id
    inner join wp_posts p3 on p3.ID = r2.object_id
    WHERE p2.ID = p.ID AND tax.taxonomy = 'author' and p3.post_type = 'guest-author'
) as all_authors,
(
	SELECT REPLACE ( meta_value, '"', "'" )
	FROM wp_postmeta m
	WHERE m.post_id = p.ID and meta_key = '_mp_subtitle_settings_byline'
) as byline,
post_excerpt as excerpt,
ifnull(
    CONCAT(
        'https://www.minnpost.com/',
        (
            SELECT slug FROM wp_terms WHERE term_id IN 
            (
                SELECT REPLACE( REPLACE( meta_value, 'a:1:{s:8:"category";s:5:"', '' ), '";}', '' ) as category_id
                FROM wp_posts p2
                LEFT OUTER JOIN wp_postmeta m ON p2.ID = m.post_id
                WHERE p2.ID = p.ID AND m.meta_key = '_category_permalink'
            )
        ),
        DATE_FORMAT( post_date, '/%Y/%m/' ), p.post_name, '/'
    ),
    CONCAT(
        'https://www.minnpost.com/',
        (
            SELECT t.slug
            FROM wp_posts p2
            INNER JOIN wp_postmeta m ON m.post_id = p2.ID
            INNER JOIN wp_term_relationships r ON r.object_id = p2.ID
            INNER JOIN wp_term_taxonomy tax ON r.term_taxonomy_id = tax.term_taxonomy_id
            INNER JOIN wp_terms t ON tax.term_id = t.term_id
            WHERE p2.ID = p.ID AND tax.taxonomy = 'category' AND t.name != 'Uncategorized'
            LIMIT 1
        ),
        DATE_FORMAT( post_date, '/%Y/%m/' ), p.post_name, '/'
    )
) as url,
post_date as publish_date,
post_modified as last_modified_date
FROM wp_posts p
left outer JOIN wp_term_relationships r ON r.object_id = p.ID
left outer JOIN wp_term_taxonomy tax ON r.term_taxonomy_id = tax.term_taxonomy_id
left outer join wp_terms t on tax.term_id = t.term_id
WHERE p.post_type = 'post' AND post_status = 'publish' AND t.term_id = 55575 and
p.post_date > DATE_SUB(now(), INTERVAL 6 MONTH)
ORDER BY publish_date DESC
;

@jonathanstegall
Copy link
Author

jonathanstegall commented Aug 31, 2022 •
edited
Loading

12 months of Artscape:

SELECT
p.ID,
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( p.post_title, 0xE28098, "'" ), 0xE28099, "'" ), 0xE2809C, '"' ), 0xE2809D, '"' ), 0xE28093, '-' ), 0xE28094, '--' ), 0xE280A6, '...' ), CHAR( 145 ), "'" ), CHAR( 146 ), "'" ), CHAR( 147 ), '"' ), CHAR( 148 ), '"' ), CHAR( 150 ), '-' ), CHAR( 151 ), '--' ), CHAR( 133 ), '...' ) as title,
(
    SELECT p3.post_title
    FROM wp_posts p2
    INNER JOIN wp_term_relationships r ON r.object_id = p2.ID
    INNER JOIN wp_term_taxonomy tax ON r.term_taxonomy_id = tax.term_taxonomy_id
    INNER JOIN wp_terms t ON tax.term_id = t.term_id
    inner join wp_term_taxonomy tax2 on tax2.term_id = t.term_id
    INNER JOIN wp_term_relationships r2 ON r2.term_taxonomy_id = tax2.term_taxonomy_id
    inner join wp_posts p3 on p3.ID = r2.object_id
    WHERE p2.ID = p.ID AND tax.taxonomy = 'author' and p3.post_type = 'guest-author'
    order by r2.object_id asc
    limit 1
) as first_author,
(
    SELECT GROUP_CONCAT( DISTINCT REPLACE ( REPLACE( p3.post_title, '&', '&' ), '"', "'" ) order by r2.object_id asc )
    FROM wp_posts p2
    INNER JOIN wp_term_relationships r ON r.object_id = p2.ID
    INNER JOIN wp_term_taxonomy tax ON r.term_taxonomy_id = tax.term_taxonomy_id
    INNER JOIN wp_terms t ON tax.term_id = t.term_id
    inner join wp_term_taxonomy tax2 on tax2.term_id = t.term_id
    INNER JOIN wp_term_relationships r2 ON r2.term_taxonomy_id = tax2.term_taxonomy_id
    inner join wp_posts p3 on p3.ID = r2.object_id
    WHERE p2.ID = p.ID AND tax.taxonomy = 'author' and p3.post_type = 'guest-author'
) as all_authors,
(
	SELECT REPLACE ( meta_value, '"', "'" )
	FROM wp_postmeta m
	WHERE m.post_id = p.ID and meta_key = '_mp_subtitle_settings_byline'
) as byline,
post_excerpt as excerpt,
ifnull(
    CONCAT(
        'https://www.minnpost.com/',
        (
            SELECT slug FROM wp_terms WHERE term_id IN 
            (
                SELECT REPLACE( REPLACE( meta_value, 'a:1:{s:8:"category";s:5:"', '' ), '";}', '' ) as category_id
                FROM wp_posts p2
                LEFT OUTER JOIN wp_postmeta m ON p2.ID = m.post_id
                WHERE p2.ID = p.ID AND m.meta_key = '_category_permalink'
            )
        ),
        DATE_FORMAT( post_date, '/%Y/%m/' ), p.post_name, '/'
    ),
    CONCAT(
        'https://www.minnpost.com/',
        (
            SELECT t.slug
            FROM wp_posts p2
            INNER JOIN wp_postmeta m ON m.post_id = p2.ID
            INNER JOIN wp_term_relationships r ON r.object_id = p2.ID
            INNER JOIN wp_term_taxonomy tax ON r.term_taxonomy_id = tax.term_taxonomy_id
            INNER JOIN wp_terms t ON tax.term_id = t.term_id
            WHERE p2.ID = p.ID AND tax.taxonomy = 'category' AND t.name != 'Uncategorized'
            LIMIT 1
        ),
        DATE_FORMAT( post_date, '/%Y/%m/' ), p.post_name, '/'
    )
) as url,
post_date as publish_date,
post_modified as last_modified_date
FROM wp_posts p
left outer JOIN wp_term_relationships r ON r.object_id = p.ID
left outer JOIN wp_term_taxonomy tax ON r.term_taxonomy_id = tax.term_taxonomy_id
left outer join wp_terms t on tax.term_id = t.term_id
WHERE p.post_type = 'post' AND post_status = 'publish' AND t.term_id = 55613 and
p.post_date > DATE_SUB(now(), INTERVAL 12 MONTH)
ORDER BY publish_date DESC
;

Replace 55613 with other category ids to get those categories.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment