A Quick Glance at SQL Views

During a recent mock interview I was asked if I had ever created any SQL views. First I misunderstood the question, and then understood that I had no idea what that even meant.

Now, I could spend more time describing how stupid I felt at that point, but why do that when I can actually go and create such a view for practice?

As explained to me by my interviewers or as described in w3schools.com, an SQL view is a virtual table generated by the SQL server based on a custom SQL query. It presents up-to-date information on all the entries used to create said table. Such views have a few different uses:

• Organizational tool for presenting data from complex queries comprised of table joins and/or aggregate functions.
• Possible performance enhancement upon building a view with a unique clustered index
• Security measure as an additional abstraction level between the actual data and the consuming applications

Slightly Unorthodox Use for an SQL View

Recently, I had run into a problem when generating JSON responses using Active Model Serializer (AMS) on associated model data. I have a game model with many locations associated to it. It turned out that every time when I updated a location row in the database, the order of the locations returned with the game instance would change. This is because Postgres returns the queried rows in an unspecified order, depending on the entry write-order on disk, among other things.

Why would I care about the order? Because the presentation of the locations on the rendered webpage is a simple for-loop. Thus, if a location’s position in the json-response changes every time a location is updated (aka another player takes control over it in the game), it will lead on its position changing on the page as well. Imagine if Ireland would have relocated from Europe to Australia on the map the minute she declared independence. Weird!

Now, if I was querying these locations straight via a locations controller, it would be easy to just add an ActiveRecord order clause to the query, eg. @locations = Location.where('game_id = ?', current_game.id).order(:id) . However, the locations associated to a game were now returned as JSON in random order via AMS by querying the game table. Thus, I found no simple way to inject the order by clause into the formation of the hash. On the other hand, building a custom method for repeated sorting of a part of a JSON hash didn’t seem worthwhile, either. After all, shouldn’t the DB be able to do this simple sorting for me?

Enter SQL view. Following the principles of setting SQL views together with Rails as described in this handy blog post, I decided to test this feature by creating a virtual ordered_locations table via a SELECT statement on the locations table with index ordering. First, a migration:

class CreateOrderedLocations < ActiveRecord::Migration[5.0]
  def up
    self.connection.execute %Q( CREATE OR REPLACE VIEW ordered_locations AS
        SELECT
            id,
            content,
            defense,
            controlled_by,
            game_id
          FROM locations
          ORDER BY id ASC;
        )
  end

  def down
    self.connection.execute "DROP VIEW IF EXISTS ordered_locations;"
  end

end

Then, the model without an actual DB table:

class OrderedLocation < ApplicationRecord
  belongs_to :game
  has_many :character_locations
  has_many :characters, through: :character_locations

  self.table_name = 'ordered_locations'
end

For the associations to work in the serializers, I added has_many :ordered_locations to app/models/game.rb, and changed the custom associated serializer:

class GameSerializer < ActiveModel::Serializer
  attributes :id, :title, :status, :turn, :winner, :map_name, :map_size, :background_image_link
  has_many :ordered_locations, serializer: CustomLocationSerializer
  # has_many :locations, serializer: CustomLocationSerializer
  has_many :game_players, serializer: CustomGPSerializer
end

And thus, an ajax get request to the games.controller #show action produces JSON with locations ordered by their primary key!

{
  game: {
    id: 1,
    title: "Purge",
    status: "active",
    turn: 2,
    winner: null,
    map_name: "Asylum",
    map_size: 8,
    ordered_locations: [
      {
        id: 1,
        defense: 145,
        controlled_by: 1
      },
      {
        id: 2,
        defense: 142,
        controlled_by: 2
      },
      {
        id: 3,
        defense: 197,
        controlled_by: 1
      },
      {
        id: 4,
        defense: 122,
        controlled_by: 1
      },
      {
        id: 5,
        defense: 58,
        controlled_by: 1
      },
      {
        id: 6,
        defense: 113,
        controlled_by: 1
      },
      {
        id: 7,
        defense: 195,
        controlled_by: 2
      },
      {
        id: 8,
        defense: 143,
        controlled_by: 2
      }
    ],
    game_players: [
      {
        game_id: 1,
        player_id: 1,
        creator: false
      },
      {
        game_id: 1,
        player_id: 2,
        creator: true
      }
    ]
  }
}

Since I’m querying the whole set of locations only through the associated game instance, nothing needed to be changed in the controllers.

While it might be a bit of an overkill to use an SQL view to just guarantee a straightforward table ordering, this simple experiment did provide a solution to my problem of preventing locations from changing position upon updating and I hope it will give some idea how handy such a feature is for organizing data resulting from considerably more complex queries.

Resources

Postgres documentation: Sorting Rows
W3Schools: SQL Views
Tutorialspoint: Using Views
Using Rails and SQL Views For a Report
Common misconceptions on SQL views
More on designing indexed views