voces.sql

SELECT
	'mysql:archive' AS 'source',
	articles.id AS 'article.legacy_id',
	articles.post_name AS 'article.slug',
	articles.post_title AS 'article.title',
	articles.post_excerpt AS 'article.excerpt',
	articles.post_content AS 'article.body',
	NULL AS 'article.main_tag',
	articles.post_date_gmt AS 'article.created_at',
	articles.post_modified_gmt AS 'article.updated_at',
	articles.post_author AS 'author.legacy_id',
	authors.user_email AS 'author.email',
	authors.user_nicename AS 'author.username',
	authors.display_name AS 'author.full_name',
	usermeta.meta_value AS 'author.bio',
	usermeta2.meta_value AS 'author.biocorta',
	concat("/voces/", articles.post_name) AS 'article.url',
	byname.meta_value AS 'author.signature',
	excerpty.meta_value AS 'article.entradilla',
	REPLACE(image.guid, 'http://www2.latercera.com/', 'https://s3.amazonaws.com/bkt-lt-antiguas-2/') AS 'featured_image.guid',
	image.post_title AS 'image.title',
	image.post_content AS 'image.legend',
	image.post_excerpt AS 'image.excrpt',
	image.post_mime_type AS 'featured_image.mimetype',
	'Opinión' AS 'article.categories',
	'Voces' AS 'article.tags',
	NULL AS 'article.format'
FROM
	wp_7_posts articles
	LEFT JOIN wp_7_postmeta wm1 ON (wm1.post_id = articles.id
			AND wm1.meta_value IS NOT NULL
			AND wm1.meta_key = '_thumbnail_id')
	LEFT JOIN wp_7_posts image ON (image.ID = wm1.meta_value)
	LEFT JOIN wp_users authors ON articles.post_author = authors.id
	LEFT JOIN wp_usermeta usermeta ON usermeta.user_id = authors.id
		AND usermeta.meta_key = 'description'
	LEFT JOIN wp_usermeta usermeta2 ON usermeta2.user_id = authors.id
		AND usermeta2.meta_key = 'descorta'
	LEFT JOIN wp_7_postmeta byname ON (byname.post_id IN(articles.id)
			AND byname.meta_key = 'nombre_autor')
	LEFT JOIN wp_7_postmeta excerpty ON (excerpty.post_id = articles.id
			AND excerpty.meta_key = 'entradilla')
WHERE
	articles.post_status = 'publish'
	AND articles.post_type = 'voces'
	# AND articles.ID = 874714
	# AND articles.post_name = "las-horas-mas-oscuras"
ORDER BY
	articles.post_date ASC