Rails - Make a one-off query to a different database and table.

Question

Hi,

I have a rails app with a wordpress blog sellotaped on the side (totally separately at /blog).

The client want's to the latest blog post on the main homepage of the rails app, so I need to do a one-off mysql query to the word-press database. How would I go about doing this in the rails app. The word-press is completely sperate from rails in terms of database.

Cheers.

Answer 1

Assuming it is accessible using the same database credentials and on the same MySQL server, the easiest way would be to run a query specifying the database and table in the FROM clause of the query, as such:

ActiveRecord::Base.connection.select_one(
    "SELECT * FROM blog_database.posts ORDER BY created_at DESC LIMIT 1")

select_one will return a hash of columns to values. For more information on methods you can use on the connection object, see this documentation.

The second option is to create a subclass of ActiveRecord and call establish_connection:

class Blog < ActiveRecord::Base
  establish_connection :blog

  def self.most_recent_post
    connection.select_one("SELECT * FROM posts ...")
  end
end

You will also need to make a blog database entry in your database.yml file. See establish_connection for more details, although unfortunately using it in this way is really only known by looking at the source code for establish_connection.

Then you can use the blog database connection in queries, like so:

Blog.connection.select_one("SELECT * FROM posts ...")

What is nice about doing it this way is now you have a nice place to define a method (in the Blog class, as a class method) to fetch the data, as I have done above.

Both these strategies should work fine with Rails 2.x or 3.x.