In part one, I discussed how you can use ruby-wpdb to access a WordPress database from within Ruby. In this article, part two, I’ll take a look at some “recipes” - quick snippets that achieve some common tasks, and hopefully give you an idea of what ruby-wpdb is capable of.

Working with posts

Within WordPress, the single most important object is probably the post. WordPress encourages you to segment types of content by creating custom post types; these are just rows in the posts table like any other post, with differing post_type values.

It’s probably fair to say, then, that the most common tasks people want to perform with WordPress involve posts. Here are some ideas for what can be done with ruby-wpdb.

Displaying the five most recent posts

latest_posts = WPDB::Post.reverse_order(:post_date).take(5)

latest_posts.each do |post|
  puts "#{post.post_date}: #{post.post_title}"

Deleting old drafts

# Our cutoff point will be 60 days ago
cutoff = - (86400 * 60)
WPDB::Post.where(post_status: 'draft').where { post_date < cutoff }.destroy

Fetching posts according to postmeta

e.g. to fetch all posts that have postmeta with the key “foo” and the value “bar”:

meta = WPDB::PostMeta.where(meta_key: 'foo', meta_value: 'bar')
posts = WPDB::Post.where(postmeta: meta)

posts.each do |post|
  puts post.post_title

Processing comments

When doing batch operations with WordPress I often find myself working with comments. Whether it’s clearing the moderation queue or processing comments as input to another process, you can do it with ruby-wpdb.

Listing the most recent unmoderated comments

comments = WPDB::Comment.where(comment_approved: 0).reverse_order(:comment_date).take(5)

comments.each do |comment|
  puts "Comment from #{comment.comment_author} <#{comment.comment_author_email}>",

Deleting unmoderated comments over a year old

cutoff = - (86400 * 365)
WPDB::Comment.where(comment_approved: 0).where { comment_date < cutoff }.destroy

Reading options

If you’d like your scripts to be flexible across installs and to behave similarly to WordPress itself, sooner or later you’re going to want to check an option.

ruby-wpdb offers the full interface to options that you’d expect. But if you’d just like to check an option’s value, it offers a shortcut function:

siteurl = WPDB::Option.get_option('siteurl')

Advanced filtering

Under the hood, ruby-wpdb uses Sequel to connect to the WordPress database. This gives you enormous power and flexibility: anything you can do with Sequel, you can do with WordPress.

Particularly useful reading is the documentation for dataset filtering; this demonstrates the power of Sequel at performing complex queries without you having to drop down into writing SQL.

That’s it! Hopefully you can see some common threads across these examples that you can translate to your own tasks.

In part three, I look at ruby-wpdb’s support for Gravity Forms, a popular plugin used for capturing user input — showing you how ruby-wpdb can make analysing and exporting GravityForms data a breeze.