WordPressのDBのSQL操作で投稿に対応したカスタムフィールドの抽出方法がわからない。

Question

WordPressのDBに関して、SQL操作で投稿に対応したカスタムフィールドの抽出方法がわかりません。

下記のようなテーブルがあった際に、IDの549に対応するカスタムフィールドを
１件のデータとして抽出するにはどうしたらいいでしょうか。

尚、その際にmeta_keyにアンダーバー(_)が先頭についているレコードは除外をしたいです。

wp_postsテーブル

ID  | post_content | post_type 

549 | aaaaaaaaa    | post

555 | bbbbbbbbb    | acf-field

wp_postmetaテーブル

meta_id | post_id | meta_key | meta_value

2281    |   549   |    lat   | 34.7355884

2282    |   549   |   _lat   | field_5ebcb731afa8e

2283    |   549   |   lng    | 135.282661

2284    |   549   |   _lng   | field_5ebcb761afa8f

抽出したいテーブルのイメージ
→IDの549に対応するカスタムフィールドを１件のデータとして抽出したい

ID | post_content | post_type | lat | lng

549 | aaaaaaaaa | post | 34.7355884 | 135.282661

下記のSQLで抽出をした場合ですと、テーブルの結合はできるのですが、そこからさらに
レコードをまとめることができません。

SELECT *
  FROM wp_posts
  JOIN wp_postmeta
    ON wp_posts.id = wp_postmeta.post_id
WHERE wp_posts.post_type="post" AND wp_posts.id="549" AND wp_postmeta.meta_key NOT LIKE '\_%';

Comments

[K_3578]

Worppress → WordPress >タイトル並びに本文

[tomato01]

ご指摘ありがとうございます。修正いたしました。

Answer 1

meta_keyがlatやlngになっている値を横持ちしたいということであれば、以下のようなクエリで実現できるかと思います。

SELECT ID, 
       post_content, 
       post_type, 
       MAX(CASE WHEN meta_key = 'lat' THEN meta_value END) AS lat,
       MAX(CASE WHEN meta_key = 'lng' THEN meta_value END) AS lng
  FROM wp_posts
  JOIN wp_postmeta
    ON wp_posts.id = wp_postmeta.post_id
 WHERE wp_posts.id = '549'
 GROUP BY ID;

---

2021/05/13 22:15追記

尚、こういったケースでは、オブジェクトみたいな感じにするのではと少し考えてたのですが、そういったこともできそうでしょうか。

MySQL8.0であればJSON_OBJECTAGGを使ってJSON型にまとめてしまうのも一つの手かもしれません。

SELECT ID, 
       post_content, 
       post_type, 
       JSON_OBJECTAGG(meta_key, meta_value)
  FROM wp_posts
  JOIN wp_postmeta
    ON wp_posts.id = wp_postmeta.post_id
 WHERE wp_posts.id = '549'
   AND meta_key NOT LIKE '\_%'
 GROUP BY ID;

Comments

[tomato01]

ご回答を頂き誠にありがとうございます。
思った通りの感じになりました！

尚、こういったケースでは、オブジェクトみたいな感じにするのではと少し考えてたのですが、そういったこともできそうでしょうか。
例
meta_key{ lat:34.7355884, lng: 135.282661}

[tomato01]

ご回答頂き誠にありがとうございます！JSON_OBJECTAGGというのがあるのですね！とても勉強になりました。どうもありがとうございました！

[tomato01]

neko_the_shadowさん
申し訳ございません、もし可能でしたらもう一点おしえてほしいのですが、Whereでlatやlngの値で絞り込みたいのですが、エラーになってしまいます。

SELECT ID,
post_content,
post_type,
MAX(CASE WHEN meta_key = 'lat' THEN meta_value END) AS lat,
MAX(CASE WHEN meta_key = 'lng' THEN meta_value END) AS lng
FROM wp_posts
JOIN wp_postmeta
ON wp_posts.id = wp_postmeta.post_id
WHERE wp_posts.id = '549' AND wp_postmeta.lat ='34.7355884'
GROUP BY ID;

wp_postmetaのレコードの値で絞り込みたい場合はどのように記載をすればいいでしょうか。

[neko_the_shadow]

WHEREではなくHAVINGを使えばよいかと思います。

SELECT
ID,
post_content,
post_type,
MAX(CASE WHEN meta_key = 'lat' THEN meta_value END) AS lat,
MAX(CASE WHEN meta_key = 'lng' THEN meta_value END) AS lng
FROM wp_posts
JOIN wp_postmeta ON wp_posts.id = wp_postmeta.post_id
WHERE wp_posts.id = '549'
GROUP BY ID
HAVING lat ='34.735588'

[tomato01]

お忙しい中ご回答を頂きありがとうございます！havingというのもはじめて聞きました。自分でも勉強してみます。ありがとうございました！