前提
WordPressのDBに対して直接SQLを利用してデータを取得したい時等に使う。
参考資料
指定した記事IDのタイトルと、その記事に紐付くカテゴリのIDと名称を表示する
SELECT wp_posts.ID, wp_posts.post_title, wp_term_relationships.term_taxonomy_id AS category_id, wp_terms.name AS category_name
FROM wp_posts
INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
INNER JOIN wp_terms ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
WHERE wp_term_taxonomy.taxonomy='category' AND wp_posts.ID=記事ID;
指定した記事IDのタイトルと、その記事に紐付くタグのIDと名称を表示する
SELECT wp_posts.id, wp_posts.post_title, wp_term_relationships.term_taxonomy_id AS tag_id, wp_terms.name AS tag_name
FROM wp_posts
INNER JOIN wp_term_relationships ON wp_posts.id = wp_term_relationships.object_id
INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
INNER JOIN wp_terms ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
WHERE wp_term_taxonomy.taxonomy='post_tag' AND wp_posts.id=記事ID;
指定したカテゴリIDのカテゴリ名を表示する
SELECT wp_terms.term_id AS cateogory_id, wp_terms.name as category_name
FROM wp_terms
INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
WHERE wp_term_taxonomy.taxonomy='category' AND wp_terms.term_id=カテゴリID;
指定したタグIDのタグ名とスラッグを表示する
SELECT wp_terms.term_id AS tag_id, wp_terms.name AS tag_name, wp_terms.slug AS slug
FROM wp_terms
INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
WHERE wp_term_taxonomy.taxonomy='post_tag' AND wp_terms.term_id=タグID;
指定したタグのスラッグで、タグIDとタグ名を検索する
SELECT wp_terms.slug AS slug, wp_terms.term_id AS tag_id, wp_terms.name AS tag_name
FROM wp_terms
INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
WHERE wp_term_taxonomy.taxonomy='post_tag' AND wp_terms.slug LIKE '%検索するスラッグ%';
指定したカテゴリIDに紐付く公開済みの記事の記事ID等を表示する
SELECT wp_terms.term_id AS category_id, wp_terms.name AS category_name, wp_posts.ID AS post_id, wp_posts.post_title AS post_title
FROM wp_terms
INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
INNER JOIN wp_term_relationships ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
INNER JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID
WHERE wp_term_taxonomy.taxonomy = 'category' AND wp_posts.post_status = 'publish' AND wp_terms.term_id=カテゴリID;
指定したタグIDに紐付く公開済みの記事を表示する
SELECT wp_terms.term_id AS category_id, wp_terms.name AS category_name, wp_posts.ID AS post_id, wp_posts.post_title AS post_title
FROM wp_terms
INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
INNER JOIN wp_term_relationships ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
INNER JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID
WHERE wp_term_taxonomy.taxonomy = 'post_tag' AND wp_posts.post_status = 'publish' AND wp_terms.term_id=タグID;
紐付けられたカテゴリが多い順で記事の情報を表示する
SELECT wp_posts.ID AS id,
wp_posts.post_title AS title,
GROUP_CONCAT(wp_term_relationships.term_taxonomy_id) AS category_id,
GROUP_CONCAT(wp_terms.name) AS category_name,
COUNT(wp_posts.ID) AS amount
FROM wp_posts
INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
INNER JOIN wp_terms ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
WHERE wp_posts.post_status = 'publish' AND wp_term_taxonomy.taxonomy='category'
GROUP BY wp_posts.ID
ORDER BY amount DESC;
紐付けられたタグが多い順で記事の情報を表示する
SELECT wp_posts.ID AS id,
wp_posts.post_title AS title,
GROUP_CONCAT(wp_term_relationships.term_taxonomy_id) AS tag_id,
GROUP_CONCAT(wp_terms.name) AS tag_name,
COUNT(wp_posts.ID) AS amount
FROM wp_posts
INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
INNER JOIN wp_terms ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
WHERE wp_posts.post_status = 'publish' AND wp_term_taxonomy.taxonomy='post_tag'
GROUP BY wp_posts.ID
ORDER BY amount DESC;
サイトのURLを確認する
SELECT option_value FROM wp_options WHERE option_name='home';