9567

Get latest comment records and the corresponding post slugs in Wordpress

Question:

I have the following query for getting the latest comments:

global $wpdb; $sql = "SELECT DISTINCT ID, post_title, post_password, comment_ID, comment_post_ID, comment_author, comment_date_gmt, comment_approved, comment_type,comment_author_url, SUBSTRING(comment_content,1,30) AS com_excerpt FROM $wpdb->comments LEFT OUTER JOIN $wpdb->posts ON ($wpdb->comments.comment_post_ID = $wpdb->posts.ID) WHERE comment_approved = '1' AND comment_type = '' AND post_password = '' ORDER BY comment_date_gmt DESC LIMIT 10"; $comments = $wpdb->get_results($sql);

I also want to get the name(slug) of the post for each comment, without running a query for each comment. Can you suggest a modification to the above query to achieve this? Better yet, can I do this using built-in Wordpress functions?

Basically I need:

$comments = array ( 'comment_object' => ... , 'post_name' => ... )

Answer1:

From my own Recent Comments code with minor edits:

/** * @return object */ function recent_comments_query($limit) { global $wpdb; $sql = "SELECT DISTINCT ID, post_title, post_name, // <- post name post_password, comment_ID, comment_post_ID, comment_author AS author, comment_date_gmt, comment_approved, comment_type, comment_author_url AS url, SUBSTRING(comment_content, 1, 200) AS comment_content FROM $wpdb->comments LEFT OUTER JOIN $wpdb->posts ON ( $wpdb->comments.comment_post_ID = $wpdb->posts.ID ) WHERE comment_approved = '1' AND comment_type = '' AND post_password = '' ORDER BY comment_date_gmt DESC LIMIT $limit"; return $wpdb->get_results($sql); }

You’ll get back an object with $limit results or NULL.

Recommend

  • Android layouts - programmatically setting value for a custom layout components
  • how to display a link to individual microposts? (ruby on rails 3)
  • Custom comment type wordpress
  • Is the use of hidden fields in forms insecure?
  • Postgresql ORDER BY spaces
  • I am trying to get current word from textarea,where my cursor is clicked now
  • How display formatted text in EditText?
  • Replacing regex matched text with uppercase version in Postgresql
  • RegEx: Find all digits after certain string
  • SQL match on letter and number arrangement without using regular expressions
  • mySQL Stored Procedure for splitting strings by delimiter
  • Looping in ArrayLists with a Method
  • jQuery - Can not edit/delete input value in Firefox
  • Formatting a string using values from a generic list by LINQ
  • IOS Pushnotification in Arabic language
  • String Split sql function only returning first word in string
  • XPath one of multiple attribute values with condition
  • How can I use regex with sed (or equivalent unix command line tool) to fix title case in LaTeX headi
  • How to separate (split) string with comma in SQL Server stored procedure
  • SQL converting rows data in to column using single table
  • Any Framework functions helping to find the longest common starting substring of multiple strings?
  • How can I output data with special characters visible?
  • load an ad (div) just once on first load
  • Error “undefined variable at noteLine” in FLWOR expression when returning multiple nodes
  • “Where” statement: match a single word (not substring)
  • Full text search on substring in PostgreSQL
  • How to get multiple substrings from string in Android/Java ?
  • php -> preg_replace -> remove space ONLY between quotes
  • How to group items in a list by their first letter in Silverlight?
  • Controlling ellipsis style in CSS
  • Convert array of strings to array of objects
  • Does Perl currently (5.8 and 5.10) make any promises about the order alternations will be used?
  • pyspark substring and aggregation
  • Replace last two characters in column
  • Busy indicator not showing up in wpf window [duplicate]
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?