SQL: select posts except a category in WordPress

特定のカテゴリーを「information」だとすると、
MySQL のSQLは、差集合を取る except  を使用できないので,
not exists を使用する

SELECT * FROM (select * from $wpdb->posts where post_status=”publish” and post_type=”post” order by post_date DESC) aa
where not exists (select * from (SELECT $wpdb->term_relationships.object_id, tt1.term_id, tt1.name FROM $wpdb->term_relationships
, (SELECT $wpdb->term_taxonomy.term_taxonomy_id,wp_terms.term_id,$wpdb->terms.name FROM $wpdb->term_taxonomy, $wpdb->terms
WHERE $wpdb->term_taxonomy.term_id =$wpdb->terms.term_id ) tt1
WHERE $wpdb->term_relationships.term_taxonomy_id = tt1.term_taxonomy_id and tt1.name=”information”) tt2
where tt2.object_id=aa.ID)
さらにもうひとつの例(1026の者の出品しないデータを取得する)
select cc.ID, cc.post_title from
(SELECT aa.ID,aa.post_title FROM tsl_posts aa,
(select * from wp_postmeta where meta_key=’_w-no’ AND meta_value LIKE ‘1026%’) bb
WHERE aa.post_type = ‘product’
AND aa.post_status = ‘publish’
AND aa.ID = bb.post_id order by bb.meta_value ) cc
where not exists (select c1.ID, c1.post_title from (SELECT a1.ID,a1.post_title FROM tsl_posts a1,
(select * from wp_postmeta where meta_key=’_w-syuppin’ AND meta_value =’出品’) b1
WHERE a1.post_type = ‘product’
AND a1.post_status = ‘publish’
AND a1.ID = b1.post_id order by b1.meta_value ) c1
where cc.ID = c1.ID)

さらにもうひとつの例(検索で出品者のみのpost_idを取得する)
select ee.post_id from (select cc.post_id from (select aa.post_id from (select post_id from wp_postmeta where meta_key=’_a-no’ and meta_value like ‘%10%’) aa, (select post_id from wp_postmeta where meta_key=’_a-ename’ and meta_value like ‘%%’) bb where aa.post_id = bb.post_id) cc, (select ID as post_id,post_title from tsl_posts where post_title like ‘%%’ and post_status=’publish’ and post_type=’artisan’ ) dd where cc.post_id = dd.post_id) ee where exists (select ff.post_id from (select post_id from wp_postmeta where meta_key = ‘_a-syuppin’ and meta_value = ‘出品’) ff where ee.post_id = ff.post_id)

上記に追加(検索で出品者のみをフリガナ順にして ID,post_title を取得する)
select jj.ID,jj.post_title from (select gg.post_id,gg.meta_value from (select post_id, meta_value from wp_postmeta where meta_key=’_a-furigana’)gg, (select ee.post_id from (select cc.post_id from (select aa.post_id from (select post_id from wp_postmeta where meta_key=’_a-no’ and meta_value like ‘%10%’) aa, (select post_id from wp_postmeta where meta_key=’_a-ename’ and meta_value like ‘%%’) bb where aa.post_id = bb.post_id) cc, (select ID as post_id,post_title from tsl_posts where post_title like ‘%%’ and post_status=’publish’ and post_type=’artisan’ ) dd where cc.post_id = dd.post_id) ee where exists (select ff.post_id from (select post_id from wp_postmeta where meta_key = ‘_a-syuppin’ and meta_value = ‘出品’) ff where ee.post_id = ff.post_id)) hh where gg.post_id = hh.post_id order by gg.meta_value) ii, tsl_posts jj where ii.post_id = jj.ID

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

次の記事

SQL: select Magic Fields data in WordPress