SQL_該当するidに紐づく評価点を取得する

Question

前提・実現したいこと

・各商品（記事）かつ特定のユーザーが投稿した平均評価（5段階）を算出するSQLを作成したいです。

■投稿した口コミの属性情報が格納されているDB（postmeta）
・投稿するたびに、post_id：500,501…とレコードが追加されていきます。
・meta_idは単なる一意のキーです
・meta_key, meta_value
meta_keyの内容に応じて、meta_valueに値が表示されます。
wpcr3_review_post：レビューを投稿した記事（商品）のID
wpcr3_review_name：投稿者の名前
wpcr3_review_rating：投稿された評価（5段階）

meta_id	post_id	meta_key	meta_value
1	500	wpcr3_review_post	10
2	500	wpcr3_review_name	editor
3	500	wpcr3_review_rating	5
4	501	wpcr3_review_post	11
5	501	wpcr3_review_name	yuki
6	501	wpcr3_review_rating	3

発生している問題・エラーメッセージ

各商品毎の平均評価算出は①のSQL分で正しく動くのですが、各商品かつ特定のユーザー（meta_value='editor'のユーザー）という条件を指定したく、 WHERE句の中に、AND (meta_value='editor' and meta_key='wpcr3_review_name'))
というのを追記すると、上手く値が取れません。

該当のソースコード

①各商品毎の平均評価算出

$review_query="SELECT round(AVG(meta_value),1) AS review_avg,count(meta_value) AS review_cnt FROM $wpdb->postmeta WHERE post_id IN (SELECT post_id FROM $wpdb->postmeta WHERE (meta_value=". get_the_ID() ." and meta_key='wpcr3_review_post')　AND meta_key='wpcr3_review_rating'"

②各商品毎かつ特定のユーザーの平均評価算出

$review_query="SELECT round(AVG(meta_value),1) AS review_avg,count(meta_value) AS review_cnt FROM $wpdb->postmeta WHERE post_id IN (SELECT post_id FROM $wpdb->postmeta WHERE (meta_value=". get_the_ID() ." and meta_key='wpcr3_review_post') AND (meta_value='editor' and meta_key='wpcr3_review_name')) AND meta_key='wpcr3_review_rating'"

試したこと

上記に記載済

補足情報（FW/ツールのバージョンなど）

None

Answer 1

典型的なDB設計のアンチパターンエンティティ・アトリビュート・バリュー(汎用的な属性テーブル)になってます。
RDBでこれをすると、今ハマっているように複雑で判りづらく遅くて間違いやすいクエリになってしまうのです。

ざっくりとですが、
reviewerテーブル
reviewer_id, name

itemテーブル
item_id, name

postテーブル
post_id, item_id, reviewer_id, rating

こんな感じのテーブルにすると良いでしょう。

Comments

[ryu41]

ご回答いただきありがとうございます。
エンティティ・アトリビュート・バリューという考え方は知らなかったのですが、頂いたリンクから見てみました。
処理が複雑化する原因になるのですね。
Wordpressのプラグインを使っているので、なかなかDB構成を変更するまでの知識は無いのですが、自分で作るときは、気を付けるようにします！

Answer 2

()は正しく閉じられていますか？
SQL的には以下のはずです。

SELECT round(AVG(meta_value),1) AS review_avg, count(meta_value) AS review_cnt 
FROM  postmeta 
WHERE meta_key='wpcr3_review_rating'
  and post_id IN (
        SELECT post_id FROM postmeta 
        WHERE meta_key='wpcr3_review_post' and meta_value='10'
      ) 
  and post_id IN (
        SELECT post_id FROM postmeta 
        WHERE meta_key='wpcr3_review_name' and meta_value='editor' 
      ) 

コスト的には、existsで判断した方が効率的かもしれません。

SELECT round(AVG(meta_value),1) AS review_avg, count(meta_value) AS review_cnt 
FROM  postmeta p
WHERE meta_key='wpcr3_review_rating'
  and exists(
        SELECT 1 FROM postmeta 
        WHERE post_id=p.post_id and meta_key='wpcr3_review_post' and meta_value='10' 
      ) 
  and exists(
        SELECT 1 FROM postmeta 
        WHERE post_id=p.post_id and meta_key='wpcr3_review_name' and meta_value='editor' 
      ) 

こっちの方が早いかも

SELECT round(AVG(rating.meta_value),1) AS review_avg, count(rating.post_id) AS review_cnt 
FROM  postmeta rating 
      inner join postmeta post
      on   rating.post_id=post.post_id 
       and post.meta_key='wpcr3_review_post' and post.meta_value='10' 
      inner join postmeta name
      on   rating.post_id=name.post_id 
       and name.meta_key='wpcr3_review_name' and name.meta_value='editor' 
WHERE rating.meta_key='wpcr3_review_rating'

構造的に三つのメタ属性がセットなら、結合条件と抽出条件に明確に分けた方が、可読性は良くなります。

SELECT round(AVG(rating.meta_value),1) AS review_avg, count(rating.post_id) AS review_cnt 
FROM  postmeta post 
      inner join postmeta name
      on  post.post_id=name.post_id 
      and post.meta_key='wpcr3_review_post' and name.meta_key='wpcr3_review_name'
      inner join postmeta rating
      on  post.post_id=rating.post_id 
      and rating.meta_key='wpcr3_review_rating'
WHERE post.meta_value='10' and name.meta_value='editor' 

Comments

[ryu41]

様々なオプションまでご教示頂きありがとうございました。
無事動作するとともに、とても勉強になり感動しました！！！