sql_to_md/sql_query.js at main · jalediazb/sql_to_md · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
// exports.query = `
//   SELECT
//     posts.ID,
//     posts.post_date,
//     posts.post_modified,
//     posts.post_type,
//     template.meta_value AS template,
//     posts.post_parent,
//     posts.post_name AS slug,
//     parent.post_name AS parent_slug,
//     posts.post_title,
//     seo_title.meta_value AS seo_title,
//     seo_description.meta_value AS seo_description,
//     featured_image_ID.meta_value AS featured_image_id,
//     img.guid AS thumbnail_url,
//     GROUP_CONCAT(DISTINCT tc.name) AS categories,
//     GROUP_CONCAT(DISTINCT tt.name) AS tags,
//     posts.post_excerpt,
//     posts.post_content
//   FROM
//     wp_posts AS posts
//   LEFT JOIN wp_postmeta AS template
//     ON template.post_id = posts.ID
//     AND template.meta_key = '_wp_page_template'
//   LEFT JOIN wp_postmeta AS featured_image_ID
//     ON featured_image_ID.post_id = posts.ID
//     AND featured_image_ID.meta_key = '_thumbnail_id'
//   LEFT JOIN wp_posts AS parent
//     ON posts.post_parent = parent.ID
//   LEFT JOIN wp_posts AS img
//     ON featured_image_ID.meta_value = img.ID
//   LEFT JOIN wp_term_relationships AS tr
//     ON posts.ID = tr.object_id
//   LEFT JOIN wp_term_taxonomy AS ttax
//     ON tr.term_taxonomy_id = ttax.term_taxonomy_id
//   LEFT JOIN wp_terms AS tc
//     ON ttax.term_id = tc.term_id
//     AND ttax.taxonomy = 'category'
//   LEFT JOIN wp_terms AS tt
//     ON ttax.term_id = tt.term_id
//     AND ttax.taxonomy = 'post_tag'
//   LEFT JOIN wp_postmeta AS seo_title
//     ON seo_title.post_id = posts.ID
//     AND seo_title.meta_key = '_genesis_title'
//   LEFT JOIN wp_postmeta AS seo_description
//     ON seo_description.post_id = posts.ID
//     AND seo_description.meta_key = '_genesis_description'
//   WHERE
//     posts.post_status = 'publish'
//     AND posts.post_type IN ('post', 'page')
//   GROUP BY
//     posts.ID, posts.post_title;
//   `


exports.query = `
SELECT
    posts.ID,
    posts.post_date,
    posts.post_modified,
    posts.post_type,
    MAX(template.meta_value) AS template,
    posts.post_parent,
    posts.post_name AS slug,
    parent.post_name AS parent_slug,
    posts.post_title,
    MAX(seo_title.meta_value) AS seo_title,
    MAX(seo_description.meta_value) AS seo_description,
    MAX(featured_image_ID.meta_value) AS featured_image_id,
    MAX(img.guid) AS thumbnail_url,
    alt_featured_img.meta_value as alt_featured_img,
    GROUP_CONCAT(DISTINCT tc.name) AS categories,
    GROUP_CONCAT(DISTINCT tt.name) AS tags,
    posts.post_excerpt,
    posts.post_content
FROM
    wp_posts AS posts
LEFT JOIN wp_postmeta AS template
    ON template.post_id = posts.ID
    AND template.meta_key = '_wp_page_template'
LEFT JOIN wp_postmeta AS featured_image_ID
    ON featured_image_ID.post_id = posts.ID
    AND featured_image_ID.meta_key = '_thumbnail_id'
left join wp_postmeta as alt_featured_img
	on alt_featured_img.post_id = featured_image_ID.meta_value
	and alt_featured_img.meta_key = '_wp_attachment_image_alt'
LEFT JOIN wp_posts AS parent
    ON posts.post_parent = parent.ID
LEFT JOIN wp_posts AS img
    ON featured_image_ID.meta_value = img.ID
LEFT JOIN wp_term_relationships AS tr
    ON posts.ID = tr.object_id
LEFT JOIN wp_term_taxonomy AS ttax
    ON tr.term_taxonomy_id = ttax.term_taxonomy_id
LEFT JOIN wp_terms AS tc
    ON ttax.term_id = tc.term_id
    AND ttax.taxonomy = 'category'
LEFT JOIN wp_terms AS tt
    ON ttax.term_id = tt.term_id
    AND ttax.taxonomy = 'post_tag'
LEFT JOIN wp_postmeta AS seo_title
    ON seo_title.post_id = posts.ID
    AND seo_title.meta_key = '_genesis_title'
LEFT JOIN wp_postmeta AS seo_description
    ON seo_description.post_id = posts.ID
    AND seo_description.meta_key = '_genesis_description'
WHERE
    posts.post_status = 'publish'
    AND posts.post_type IN ('post', 'page')
GROUP BY
    posts.ID, posts.post_title, posts.post_date, posts.post_modified, posts.post_type, posts.post_parent, posts.post_name, parent.post_name, posts.post_excerpt, posts.post_content, alt_featured_img.meta_value;
`