All pastes #1343616 Raw Edit

Wordpres Mysql cheat-sheet

public sql v1 · immutable
#1343616 ·published 2009-02-21 11:27 UTC
rendered paste body
/* All posts of category 'recipes' */SELECT id,post_title    FROM wp_term_taxonomy        JOIN wp_terms            ON wp_term_taxonomy.term_id = wp_terms.term_id        JOIN wp_term_relationships            ON wp_term_taxonomy.term_taxonomy_id                = wp_term_relationships.term_taxonomy_id        JOIN wp_posts            ON wp_term_relationships.object_id = wp_posts.id   WHERE wp_term_taxonomy.taxonomy='category' AND name='recipes'       AND post_type='post' AND post_status='publish'   ORDER BY id DESC/* all tags and number of posts for each (tag cloud) */SELECT wp_terms.name,       count(wp_term_relationships.object_id) AS num_posts    FROM wp_term_taxonomy        JOIN wp_terms            ON wp_term_taxonomy.term_id = wp_terms.term_id        JOIN wp_term_relationships            ON wp_term_taxonomy.term_taxonomy_id                = wp_term_relationships.term_taxonomy_id        JOIN wp_posts            ON wp_term_relationships.object_id = wp_posts.id   WHERE wp_term_taxonomy.taxonomy='post_tag'       AND post_type='post'       AND post_status='publish'   GROUP BY wp_terms.term_id   ORDER BY name ASC/* All posts of with tag 'spicy' */SELECT id,post_title    FROM wp_term_taxonomy        JOIN wp_terms            ON wp_term_taxonomy.term_id = wp_terms.term_id        JOIN wp_term_relationships            ON wp_term_taxonomy.term_taxonomy_id                = wp_term_relationships.term_taxonomy_id        JOIN wp_posts            ON wp_term_relationships.object_id = wp_posts.id   WHERE wp_term_taxonomy.taxonomy='post_tag' AND name='spicy'       AND post_type='post' AND post_status='publish'   ORDER BY id DESC