geminilabs

[site-reviews] SQL query to export reviews (using assignment post_name)

Dec 23rd, 2021 (edited)
138
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- 1. Install the [Database Browser](https://wordpress.org/plugins/database-browser/) plugin
  2. -- 2. Go to the "Tools > Database Browser" page
  3. -- 3. Click the "Select table" button (it doesn’t matter which table is selected)
  4. -- 4. Copy the table prefix of your tables (for example, if the table is `wp_posts`, the prefix is the first part with the underscore `wp_`).
  5. -- 4. Click the "Edit query" button
  6. -- 5. Paste the SQL query, replacing every instance of `wp_` in the SQL query with your table prefix.
  7. -- 6. Click the "Run query" button
  8. -- 7. Click the "CSV" button to download the results
  9.  
  10. SELECT
  11.     p.post_date AS date,
  12.     p.post_date_gmt AS date_gmt,
  13.     p.post_title AS title,
  14.     p.post_content AS content,
  15.     r.rating,
  16.     r.name,
  17.     r.email,
  18.     r.avatar,
  19.     r.ip_address,
  20.     r.is_approved,
  21.     r.is_pinned,
  22.     r.terms,
  23.     GROUP_CONCAT(DISTINCT CONCAT(p1.post_type, ':', p1.post_name)) AS assigned_posts,
  24.     GROUP_CONCAT(DISTINCT att.term_id) AS assigned_terms,
  25.     GROUP_CONCAT(DISTINCT aut.user_id) AS assigned_users
  26. FROM wp_glsr_ratings AS r
  27. INNER JOIN wp_posts AS p ON r.review_id = p.ID
  28. LEFT JOIN wp_glsr_assigned_posts AS apt ON r.ID = apt.rating_id
  29. LEFT JOIN wp_glsr_assigned_terms AS att ON r.ID = att.rating_id
  30. LEFT JOIN wp_glsr_assigned_users AS aut ON r.ID = aut.rating_id
  31. INNER JOIN wp_posts AS p1 ON apt.post_id = p1.ID
  32. WHERE p.post_type = 'site-review'
  33. AND p.post_status IN ('publish','pending')
  34. GROUP BY r.ID
RAW Paste Data Copied