Building a custom SQL Query in an Active Record Model and exposing it through a RESTful Web Service

Today i’ll be discussing the use of advanced/custom SQL queries in Active Record models.

I recently added a “top trending story” feature on the stories page of my Storyteller app (https://github.com/Mely07/Storyteller-React-Redux-Project). To access the top trending story, or the story with the most comments, I wrote a custom SQL request from within my Story class. Although my Story and Comment tables are connected, as a story has many comments and a comment must be tied to a story(byway of a story_id), I failed to set up the initial “has_many” “belongs_to” Application Record associations when first creating my app therefore used this workaround.

This brings me to this week’s topic of discussion: building a custom SQL query in an Active Record model and exposing it through a RESTful web service.

Setting up my RESTful web service (Ruby on Rails):

For starters, within the routes.rb file, following RESTful routing conventions, I first defined the route that will point to my top trending story:

get ‘stories/top’, to: ‘stories#topStory’

This HTTP request (http://localhost:3000/stories/top) is sent from the client to the server. The application router processes the request via the route.rb file. Once it identifies the TYPE of request and WHERE the request is going the request is mapped to the controller method that is called in the custom route (in my case, topStory).

Next, within my Story Controller:

def topStory
   @story = Story.topStory
   render json: @story
end

The instance variable story is used to store and render or make available as JSON, to my front-end, the return value from the SQL query defined in Story.topStory.

Lastly, the SQL query itself:

class Story < ApplicationRecord
…
   def self.topStory
    Story.find_by_sql(“SELECT * FROM stories WHERE id =(SELECT              story_id FROM comments GROUP BY story_id ORDER BY count(*) DESC LIMIT 1)”)
   end
end

The above custom query requests the return value of the story with the most comments by first grouping all of the comments by story_id, then ordering them in descending order and limiting them to one (the most commented story).

By calling the query from within the Story class as a class method I am able to access this information from within other parts of my application.

Now when calling http://localhost:3000/stories/top the return value is the top trending story:

[{“id”:89,”opening_line”:”When Amelia Bedelia arrived for work, she could not believe her eyes.”,”image”:”https://images2.minutemediacdn.com/image/upload/c_fill,g_auto,h_740,w_1100/v1555927717/shape/mentalfloss/amelia_0.jpg?itok=-Z3TzWD7","genre":"Comedy","author":"Bob5","created_at":"2020-10-10T20:09:50.767Z","updated_at":"2020-10-10T21:05:46.922Z"}]

What we see above is a JavaScript Object Notation(JSON) object (similar to the ruby hash) made up of key-value pairs. The JSON format is often used when retrieving data from a web service and is easily interpreted by JavaScript on the front-end.

Tip: To test a SQL query in the Ruby console type: ActiveRecord::Base.connection.execute(“sql command here inside quotes”)