DEV Community: Nick

Reading a Ruby gem with VSCode

Nick — Tue, 15 Feb 2022 00:00:00 +0000

Whether out of curiosity or trying to understand a method for debugging, it's helpful to know how to dig into a Ruby gem. Recently I was curious over how mocha, a mocking library for minitest, allowed stubbing on any_instance of a Class (as opposed to injecting a stubbed object), for example:

def test_stubbing_an_instance_method_on_all_instances_of_a_class
  Product.any_instance.stubs(:name).returns('stubbed_name')
  product = Product.new
  assert_equal 'stubbed_name', product.name
end

Following this are steps you can try when investigating a gem. I'd love to say these were the exact steps I took while investigating any_instance, but truth is some steps I discovered only while writing this post 😅. The steps are specific to using VSCode.

Open the Source Code

Assuming the gem is installed, gem open <gemname> (or bundle open <gemname> if installed in a project via Bundler) will open it's source code with the command set in the $EDITOR environment variable.

For example EDITOR=code bundle open mocha from my project opens the gem in VSCode:

If the gem isn't installed, you could also look up the source code online (rubygems.org) and if the code is on GitHub, there's a handy shortcut to open it in a browser version of VSCode.

Find your Target

One of the easiest ways to find the method definition is to use #source_location. Fire up an irb console:

require 'minitest'
require 'mocha/minitest'
Object.method(:any_instance).source_location
# => ["/.../ruby/3.0.3/gems/mocha-1.13.0/lib/mocha/class_methods.rb", 45]

In case that doesn't work, since we have the source code open, we can make use of VSCode's search:

Some ideas to try if the results list is really long:

prefixing the search term with the declaration syntax (e.g. class <ClassName> for a class, def <method_name> for an instance method) generally works well, but not always, given the many ways to metaprogram in Ruby. For example, it's not obvious that any_instance would have been defined as an instance method.
"Match Whole Word" (ab) helps exclude classes / methods with the search term as a substring (e.g. it would have excluded any_instance_method and mock_impersonating_any_instance_of in the results above)
"files to include" knowing the structure of most gems (see later section), we can usually restrict to the lib/ directory, which will exclude test files, bin scripts etc

Trace the Callers

While we found the method, it didn't answer our initial question, which was how any_instance becomes callable on any class. We could probably try a "top-down" or "bottom-up" search here.

For "bottom-up" search, since this is plain Ruby as opposed to Rails, we could try to follow the require trail. But if we have an extension like solargraph, a simpler way is to make use of VSCode's Go to References:

Above, we find that Mocha::ClassMethods is included into Class in Mocha::API:

Class.send(:include, ClassMethods)

We can continue doing the same approach, but at some point the "search tree" got quite unwieldy so I switched to a more "top-down" approach, checking the README to see how the gem was require-d.

Know the Structure of a Gem

A quick detour on how a gem is structured - lib/ generally contains the gem's core logic. There you'll find a ruby file and a folder corresponding to the name of the gem. For example, in mocha:

lib/
└── mocha.rb
└── mocha/

The ruby file (e.g. lib/mocha.rb) generally contains most of the gem's bootstrapping logic, as this is the file loaded when requiring the gem (i.e. require 'mocha' would load lib/mocha.rb). So typically we'd start our "top-down" search here.

mocha is slightly different - it supports multiple test libraries, and in the README we are instructed to require a different file depending on our test library. For example, for minitest we'd do:

require 'minitest/unit'
require 'mocha/minitest'

So in this case we'd start our search from lib/mocha/minitest.rb. There on it's pretty straightforward Ruby, leading us back to Mocha::API (albeit with knowledge of how it gets required!).

Footnotes

How to Dispel Ruby Magic and Understand Your Gems (Justin Weiss) - an inspiration for much of this
Structure of a Gem (RubyGems) - more details on how a gem is structured, along with a quick start so you can build your own gem!
VSCode docs on searching across files

Don’t give up on your Rails Generators!

Nick — Sun, 21 Mar 2021 10:17:09 +0000

From speaking with Rails devs, many quickly stop using the built-in generators (save for a few like model or migration), the common reason being that the generated files no longer suit the project - so it’s easier to start from scratch rather generate and then modify them. What a pity! To paraphrase DHH, generators can help improve team productivity and encourage consistency - we just need to tweak them.

DHH

@dhh

A good way to get more people to do the right thing: Generate the pattern by default via built-in generators. Like we do with tests etc.

00:38 AM - 05 Mar 2012

In this post, I look at one approach for customising generators, based on one we customised recently at work: the rake task generator.

TL;DR on Overriding a Generator

Copy over the Generator classes to /lib/generators/... and modify
Copy over the template file to /lib/templates/... and modify
Bonus: Use hook_for to generate a test file if the generator doesn’t do it automatically

The Problem

At work, we use rake tasks quite a fair bit, both for cron-like jobs and one-off migration or support tasks. Rails has a built-in task generator, with the form:

bin/rails g task file_name action

It’s never quite worked for us:

We like to keep one task “action” per file, with the file named the same as the action - this makes it easier to grep for the task. The built-in generator requires separate file_name and action arguments, which is a little unnecessary.
We sometimes namespace tasks e.g. with a “onetime” namespace for one-off tasks, but the built-in generator ignores paths in the file_name and also uses the file_name as the namespace.
We write tests for our rake tasks, but the generator doesn’t create test files.

Let’s address these! There are different approaches available, but in this case it made sense to override the generator entirely - the team wasn’t using it, so we weren’t breaking established conventions.

Overriding the Task Generator

Rails searches for generators in a few paths, so as long as we place our file in /lib/generators/task/task_generator.rb, Rails will load our custom file instead.

We’ll copy over the original task generator to use as a base. Most of the default generators can be found in railties/lib/rails/generators/rails, the one we’re after is here:

module Rails
  module Generators
    class TaskGenerator < NamedBase # :nodoc:
      argument :actions, type: :array, default: [], banner: "action action"

      def create_task_files
        template "task.rb", File.join("lib/tasks", "#{file_name}.rake")
      end
    end
  end
end

Copy it over to /lib/rails/generators/task/task_generator.rb. As a simple sanity check to see if we've overridden the class, let’s make the generator spit out a hardcoded file name and run it. Change this line:

template "task.rb", File.join("lib/tasks", "hardcoded_file_name.rake")

Then run the command:

bin/rails g task path/to/my_task
# create  lib/tasks/hardcoded_file_name.rake

It works! Let's revert the hardcoding and leave the rest of the class as-is for now. We'll be back, promise.

Overriding the Generator Template

We'll going to be customising the template as well, so let's override that. When invoked, generators search for templates in a different set of paths from the invoker (I explored this more in another post) - we'll be wanting to place our template at /lib/templates/rails/task/task.rb.tt.

Copy the original and modify it as such (the lack of indentation is intentional):

<% class_path.each_with_index do |path_fragment, index| -%>
<%= indent("namespace :#{path_fragment}", index * 2) %> do
<% end -%>
<% content = capture do -%>
desc "TODO"
task <%= file_name %>: :environment do
end
<% end -%>
<%= indent(content, class_path.size * 2) %>
<% (0...class_path.size).reverse_each do |index| -%>
<%= indent('end', index * 2) %>
<% end -%>

Let's breakdown the changes. First, we'll sort our name-spacing, including the indentation (note that indent is actually a private method on Rails::Generators::Base, so you won't be able to use it in your regular view templates):

<% class_path.each_with_index do |path_fragment, index| -%>
<%= indent("namespace :#{path_fragment}", index * 2) %> do
<% end -%>
...
<% (0...class_path.size).reverse_each do |index| -%>
<%= indent('end', index * 2) %>
<% end -%>

With the previous command bin/rails g task path/to/my_task, the above results in:

namespace :path do
  namespace :to do
    ...
  end
end

Now lets sort the task itself:

...
<% content = capture do -%>
desc "TODO"
task <%= file_name %>: :environment do
end
<% end -%>
<%= indent(content, class_path.size * 2) %>
...

Here, we indent the task description strings by first assigning it to a variable, content via the capture helper. Then, we indent and insert it into the file. So running the command again will give us:

namespace :path do
  namespace :to do
    desc "TODO"
    task my_task: :environment do
    end

  end
end

Almost there! Our file is still being created as /lib/tasks/my_task.rake though, instead of /lib/tasks/path/to/my_task.rake.

Fixing the Generated Path

Let's fix that. Here's the diff for our generator:

4,5d3
<       argument :actions, type: :array, default: [], banner: "action action"
<
7c5
<         template "task.rb", File.join("lib/tasks", "#{file_name}.rake")
---
>         template "task.rb", File.join("lib/tasks", class_path, "#{file_name}.rake")

We've removed the unnecessary actions argument and added the class_path to the generated file's path. Running the command again nests the file nicely now.

Hooking in a Test file

“Should you test rake tasks?”... is not a discussion for this article, let’s assume you do! We can customise our generator to create a test file.

The method we’re looking for is hook_for, which will cause the generator to attempt to also invoke other generators. Add the following DSL to your generator class:

hook_for :test_framework

In short, this attempts to invoke Rspec::Generators::TaskGenerator, which... doesn’t exist yet. Let’s resolve that.

Adding RSpec Generator and Template

RSpec doesn’t have a custom generator for tasks, but the steps for overriding it are more or less the same. Let’s start by creating the TaskGenerator class in /lib/generators/rspec/task/task_generator.rb:

# frozen_string_literal: true

require 'generators/rspec'

module Rspec
  module Generators
    class TaskGenerator < Base # :nodoc:
      def create_task_specs
        template "task_spec.rb", File.join("spec/tasks", class_path, "#{file_name}_rake_spec.rb")
      end
    end
  end
end

Next, we need to create the corresponding template in /lib/templates/rspec/task/task_spec.rb.tt (note: we can definitely DRY this out more, you might be able to draw some inspiration from these examples by Thoughtbot and 10Pines):

require 'rails_helper'
require 'rake'

describe '<%= class_path.join(':') %>' do
  describe ':<%= file_name %>' do
    before(:all) do
      Rake::Task.define_task(:environment)
      Rake.application = rake = Rake::Application.new
      rake.rake_require 'tasks/<%= File.join(class_path, file_name) %>'
      @task = rake['<%= class_path.concat([file_name]).join(':') %>']
    end

    it 'works' do
      @task.execute
    end
  end
end

With that done, running the generator command now creates the files just as we wanted. Neat!

Go forth and generate!

I hope this has given you some inspiration to give generators a second chance - with a little tweaking, they can be really useful in saving time for you and your team. What ideas do you have for improving your generators?

Javascript Architectures for Rails Apps

Nick — Tue, 09 Feb 2021 14:50:05 +0000

Javascript has become an integral part of modern websites, allowing developers to create interactive, app-like experiences. While the ecosystem has seen some consolidation over the years, there are still quite a few choices you could make. Let's talk about two typical approaches for Rails: "multi-page" and single-page apps.

The "Multi-Page" App

The web was originally envisioned as a collection of documents linked together. Each time you clicked a link, it would load a new document. Rails was built with this in mind, so if you've done any beginner tutorials, this should be the approach you'd be most familiar with.

In this setup, routing is handled by the backend - Rails decides which pages to serve based on the requested URL e.g. in the above example, we might have set /videos/cats to route to a VideosController, which will render a videos/index.html.erb view.

Javascript plays two main roles:

sprinkles of on-page interactivity e.g. hiding or showing in accordions
partial page replacement, mainly via a technique known as AJAX, to make interactions feel speedier and more app-like

Example libraries that can work in this paradigm include JQuery, Stimulus, React or VueJS.

Single Page Apps (SPAs)

SPAs are a pretty radical departure from the multi-page paradigm. They essentially take partial page replacement to the extreme - the Javascript framework handles almost everything. Let's look at an example setup:

In the above example, we host our frontend on a separate server. When someone enters a URL, our frontend server will return a single page, and the Javascript takes over:

the framework handles and simulates "routing" as well. The TLDR; is that it decides what to render for /videos/cats.
it makes the required AJAX calls to our Rails backend (e.g. /api/videos/) based on what's being rendered, retrieving data as JSON. This is somewhat similar to our Rails controllers extracting data from a model.
based on the JSON data, the framework will create, replace or delete HTML elements on the fly, making for a very fluid experience. A close parallel in Rails would be the rendering of data in views.

For the most part, Rails is "API-only" - we may not even need the Asset Pipeline! This might sound antithetical what Rails is ("everything you need to build fantastic applications"), but as with anything in technology, there are tradeoffs that make this worth considering:

The greater separation of frontend and backend in SPA setups lead to increased complexity, but also allows for a separate frontend and backend team that move individually.
SPA frameworks offer a more "declarative" programming paradigm, making it easier to create complex interactions, at the cost of a steeper learning curve.
SPA frameworks generally enable more fluid web-apps as you'd potentially make smaller JSON-only requests and never need to load full pages (at least after the first), though Rails has a few tricks up it's sleeve to close this gap.

Examples of SPA frameworks include React (with some companion libraries), VueJS, EmberJS and AngularJS. React and VueJS are repeated here because they can operate in both paradigms: as "sprinkled" Javascript, or as a full framework.

So, what to choose?

If you're new to Rails and web development, I'd humbly suggest to lean toward a "multi-page" setup. You'd have less complexity to maintain and a gentler learning curve. SPAs can come later (in fact, you might not even need to host your own backend to work with an SPA).

We'll take a look at different options for "multi-page" apps in the next post. You can follow me on Twitter to be the first to know when it's ready.

Which CSS framework - Bootstrap or Tailwind?

Nick — Thu, 04 Feb 2021 19:40:04 +0000

If you're new to web development, you may have heard these two CSS frameworks being name-dropped. TailwindCSS, in particular, has caught on recently, but you'd likely have also heard about the venerable Bootstrap. What should you pick?

Both take different approaches:

Bootstrap is what I'd call a Component-based Framework. It's designed to help developers quickly bootstrap a typical website or app and so comes with a set of pre-made components, along with some utilities for spacing etc. Other similar frameworks include Bulma and Foundation.
TailwindCSS is a Utility-first Framework. In contrast to Bootstrap, which provides a few utilities, Tailwind is all utilities. The core framework has no pre-made components, so you'll need to build your own.

Learning by Example

An example will help crystallise the differences - let's say we want to build a photo gallery with a “Load More” button:

We also want it to be responsive, so on the tinier screens of our smartphones, the gallery morphs into a column layout:

Bootstrap

Here's how you could achieve the layout with Bootstrap (codepen):

<section class="container">
  <div class="row">
    <div class="col-12 col-md-4">
      <div class="card mb-4">
        <img src="https://via.placeholder.com/300x200.png" />
        <div class="card-body">
          <p class="card-text">Card Text.</p>
        </div>
      </div>
    </div>
    <!-- repeat 2x... -->
  </div>
  <div class="text-center">
    <button class="btn btn-primary">Load More</button>
  </div>
</section>

Looking at the CSS classes, you'll probably have already noticed Bootstrap's built-in components:

container, row and col-* classes are for layout
- Bootstrap's layout is based on a grid of 12 units, so col-12 is telling Bootstrap that this column should take up the full width of it's container. This is why if you resize the browser window smaller, it will stretch to the full width.
- col-md-4 says, on a medium-sized (md) screen and above, take only 4 units. This is why in a typical desktop view, it will show (12 / 4 =) 3 columns.
The card and btn (button) components are hopefully quite self-explanatory.

We also used some of Bootstrap's utility classes, which map pretty close to the actual CSS being applied:

mb-4 applies margin-bottom: 4rem by default.
text-center applies text-align: center!important.

Observations:

Because we were able to use Bootstrap's own components, there was little we had to write, and very little CSS we had to actually know. Some built-in components will also handle Javascript interactions for you, such as accordions or one of my favourites, scrollspy.
In keeping with the framework's conventions, we'd probably start grouping and naming our CSS classes by components too. e.g. I might add a chat-message class in my CSS/SASS stylesheet, with a modifier like chat-message-primary to set the colour. (If it sounds tricky deciding how to name classes, it is! People have come up with whole methodologies like BEM in an attempt to solve this.)

In the Wild

Bootstrap has an examples page showing some basic layouts, and you can take a look at some curated examples in production and their official themes.

TailwindCSS

A similar layout built with Tailwind (codepen):

<section class="flex flex-col">
  <div class="flex flex-col md:flex-row md:justify-center md:space-x-4 items-center space-y-4 md:space-y-0">
    <div class="border border-gray-200 rounded">
      <img src="https://via.placeholder.com/300x200.png" /> 
      <p class="p-5">Card Text.</p>
    </div>
    <!-- repeat 2x... -->
  </div>

  <div class="flex justify-center mt-4">
    <button class="px-4 py-2 rounded bg-blue-500 text-white hover:bg-blue-600">
      Load More
    </button>
  </div>
</section>

While Bootstrap provided some utility classes, the Tailwind core only has utility classes:

for layout, we are making use of flexbox via classes like flex and flex-col
responsiveness is achieved using the breakpoint prefixes, e.g. md:flex-row changes the flex-direction to "row" only on medium-sized ("md") screens or larger
for the "components" like the button, we are almost writing the CSS using the utility classes e.g.:
- px-4 adds a padding of 1rem (it has a different default scale from Bootstrap) to the left and right (the x-axis).
- bg-blue-500 sets the background to a "medium" blue.
- hover:bg-blue-600 darkens the background colour when someone hovers over it.

Observations

Using Tailwind isn't too far off from writing plain CSS, which isn't necessarily a bad thing. If you are working with a design that deviates from the norm, a framework like Bootstrap doesn't help much - you'd end up writing most of your own CSS. With Tailwind, that becomes easier because of the utility classes.
We're able to do all our styling without leaving our HTML, just by adding utility classes. So instead of structuring our components via CSS class names, we'd be relying on extracting components via our frontend framework. In Rails we might break up the code into partials:

<%# gallery.html.erb %>
<section class="flex flex-col">
  ...
  <div class="flex justify-center mt-4">
    <%= render partial: "button", text: 'Load More' %>
  </div>
</section>

<%# _button.html.erb %>
<button class="px-4 py-2 rounded bg-blue-500 text-white hover:bg-blue-600">
  <%= text %>
</button>

There's a whole debate about whether this is a good thing, you can read Adam Watham's (the creator of Tailwind) argument here.

In the Wild

Adam Watham's Youtube channel is a great resource for learning best practices on Tailwind. He also has a set of (mostly paid) pre-built Tailwind components that you can use as a base. It's definitely in the spotlight now - for Rails devs, there's even an official tailwind-rails gem.

So, which to pick?

My personal opinion:

If you're a new backend or "full-stack" developer (e.g. Rails developers), I'd highly recommend trying Bootstrap first - make use of their built-in components, and only pick up CSS as needed when tweaking the styles or starting your own components. That way, you'd have more time to spend on learning the other parts of your chosen stack.
If you're set on the frontend path or are more seasoned with CSS, then Tailwind (or maybe even no framework) is a good choice, seeing as you'll need to get good with CSS anyway. I am personally using Tailwind for my new projects and while I'm still figuring out how to structure my code, am enjoying it.

If you have unanswered questions, please do comment below or send a tweet - I'd love to incorporate your feedback into this post to help others!

Asset Compilation in Rails

Nick — Thu, 04 Feb 2021 19:38:02 +0000

In the previous post, we talked about what a frontend is, and briefly looked at how it works in Rails. Now, we'll look specifically at pre-compilation:

Why compilation?

Any moderately complex interactive web-app of today contains a lot of CSS and Javascript, so we smart developers devised extensions and frameworks to make them easier to manage, for example:

webpack does a lot of things, one of them being the bundling of our numerous CSS and JS files (which we break up for ease of development) into fewer files that are faster to load
autoprefixer lets us write CSS without having to worry about vendor-prefixes (some browsers named CSS properties differently as they were experimental at the time etc. For example, transition is -webkit-transition in Safari).

These make it more convenient (once you get past the learning curve) to work on our frontend, but this means our assets usually can't be used in their raw form - Rails has to convert them into the actual HTML, JS and CSS files before sending it to your browser.

In local development, the rails server will mostly do this conversion on the fly for you. But in production, you'll want to pre-compile production-ready assets, so the server can focus on generating and sending pure HTML.

What is “production-ready”?

This includes things like:

Combining separate JS and CSS files into less files so a browser needs to make less requests
Minifying (compressing) the files so they’re smaller and load faster
Fingerprinting so the browser knows when to ignore its cached files and request for the latest ones

What are my options for pre-compilation?

Rails 6 actually has two mechanisms for compiling static assets:

The Asset Pipeline is the traditional way, using a gem called sprockets.
Webpack is pretty much the a modern standard in the JS ecosystem, and Rails includes the webpacker gem to enable this.

You can actually just pick one or the other, but it seems the Rails-recommended way is to use Webpacker for JS files, and the Asset Pipeline for everything else e.g. CSS.

A brand new Rails 6 project loads with that in mind, so:

/app/assets/stylesheets should be where your CSS files go
/app/javascript/ should be where your JS files go

For the curious, here's some backstory on why there are two ways to compile assets, by Ross Kaffenberger.

How do I set up pre-compilation for production?

If you’re deploying with Heroku it will do all the pre-compilation for you, unless you've customised your build process. For other services, you'll have to run rake assets:precompile in your deployment scripts. See the Rails guide for more details.

Next, we'll take a look at the different options for styling our web-sites/apps (i.e. CSS).

Choosing a Rails Frontend in 2021

Nick — Thu, 04 Feb 2021 19:35:12 +0000

As a new web developer starting with Ruby on Rails, there's a dizzying array of choices for the frontend of your site! This is first in a series of posts that attempts to give a lay of the land, with some personal suggestions on where to start. I'll assume you've at least built a simple Rails app before, if not you can't go wrong with the excellent https://www.railstutorial.org/. First, let's get a shared understanding of what a frontend is.

Explaining the frontend

Oxford Dictionary 1. COMPUTING - relating to or denoting the part of a computer system or application with which the user interacts directly.

When you enter a URL in your browser and click enter, a chain of events happen, culminating in your browser receiving a response - this is basically a plain text document that is written in HTML, CSS and Javascript - interpreting and rendering it.

A nice metaphor could be a theatre:

The stage where all the actors perform and sometimes respond to audience interactions is like the frontend. Our browser renders the HTML/CSS and handles user interactions (mouse-clicks, typing) based on the HTML conventions and instructions we wrote in Javascript.
The backend is like a theatre's backstage, where stagehands arrange and prepare props and actors get changed for the next scene. This is akin to all the logic we have in our Rails controllers, combining data from our models with the views we wrote to be sent to the browser.

How does it work in Rails?

When Rails devs say "frontend" though, we usually refer to a bit more, encompassing not just the actual HTML, CSS and Javascript that is sent to the user, but how we've set up Rails to generate them.

There was once a time when web developers would need to write the HTML for every individual page - check the 404.html page in your Rails app for an example. This was fine for early websites, but perhaps impossible for creating the dynamic, interactive websites of today.

In a framework like Rails, you typically wouldn't write the exact HTML for every single page. Rather, there are multiple components involved that Rails combines to generate the resulting HTML. A simplistic illustration:

our "raw" Javascript and CSS assets are usually structured in a way that's convenient for development, but not optimised for sending to users
there is a pre-compilation step that compiles and optimises our assets
a Rails controller renders our view and inserts the correct URLs to the compiled assets, then sends that back in a response to the user

We'll dive a little deeper into the compilation step next, talking about the Asset Pipeline and Webpacker.

Window Functions, Visualized - Rankings

Nick — Mon, 04 Jan 2021 00:00:00 +0000

In the previous post, we talked about how Window Function Calls worked. Today, we'll dive deeper into the different Window Functions that are available, starting with row_number, rank, dense_rank and percent_rank.

We'll be using the same expenses table from the previous post, which had entries like:

description	cost
bus ride	3
lunch	15
...

In the following examples, our window frame will be the whole expenses table, ordered by highest to lowest cost:

SELECT
  [ window function ] OVER(ORDER BY cost DESC),
  expenses.description,
  expenses.cost
FROM expenses

row_number

Let's start with row_number:

... ROW_NUMBER() OVER(ORDER BY cost DESC), ...

All row_number does is add a running number for each row in the frame, starting from 1:

row_number	description	cost
1	groceries	60
2	dinner	35
3	taxi	20
4	lunch	15
5	lunch	15
6	supper	15
7	tea break	5
8	bus ride	4
9	bus ride	3
10	bus ride	3

dense_rank

dense_rank is more interesting. Reviewing the previous example, we can see that there were a few entries that have the same cost. The Postgres documentation refers to these as peer groups, and dense_rank adds a running number counting by the groups instead of rows:

So changing the SQL to this:

... DENSE_RANK() OVER(ORDER BY cost DESC), ...

Gives us this:

dense_rank	description	cost
1	groceries	60
2	dinner	35
3	taxi	20
4	lunch	15
4	lunch	15
4	supper	15
5	tea break	5
6	bus ride	4
7	bus ride	3
7	bus ride	3

Note: I'm actually not sure how Postgres orders the rows within a peer group - in my small example, it looks like an implicit ORDER BY ID DESC was added, but the Postgres docs also say for general ordering, if sorting is not chosen, the rows will be returned in an unspecified order 🤷‍♂️.

rank

rank has one big difference from dense_rank - it counts the "gaps" in the previous peer group:

Let's compare the results of each function to show the difference:

dense_rank	rank	description	cost
1	1	...	...
2	2	...	...
3	3	...	...
4	4	...	...
4	4	...	...
4	4	...	...
5	7	...	...
6	8	...	...
7	9	...	...
7	9	...	...

percent_rank

This is an interesting one - how it's derived is a bit of a mouthful, so let's show by example, starting with the query:

... PERCENT_RANK() OVER(ORDER BY cost DESC), ...

And the result:

percent_rank	description	cost
0	groceries	60
0.11...	dinner	35
0.22...	taxi	20
0.33...	lunch	15
0.33...	lunch	15
0.33...	supper	15
0.66...	tea break	5
0.77...	bus ride	4
0.88...	bus ride	3
0.88...	bus ride	3

Instead of a running number, we get the "relative rank" from 0 to 1 inclusive. This is useful if you need to know how close something is to the highest rank - though, as the above example shows, it may not end with 1 if there are ties for the highest rank (a deeper explanation here).

side-by-side

Finally, let's look at results side by side:

row_number	dense_rank	rank	percent_rank	description	cost
1	1	1	0	groceries	60
2	2	2	0.11...	dinner	35
3	3	3	0.22...	taxi	20
4	4	4	0.33...	lunch	15
5	4	4	0.33...	lunch	15
6	4	4	0.33...	supper	15
7	5	7	0.66...	tea break	5
8	6	8	0.77...	bus ride	4
9	7	9	0.88...	bus ride	3
10	7	9	0.88...	bus ride	3

I hope that helped! Here's an sqlfiddle you can play around with.

In future articles, we'll look into more window functions. Follow me on Twitter to be informed of the next one!

Window Function Calls in Postgres - a Visual Introduction

Nick — Thu, 31 Dec 2020 21:56:03 +0000

What is a window function call? My explanation:

adds a column to query results that is calculated based on other rows in the table

Ok, that's probably still not immediately understandable, so let's walk through an example. Imagine you've been tracking expenses in Postgres:

description	category	created_at	cost
bus ride	transport	2020-01-01	3
lunch	food & drinks	2020-01-01	15
...

Now, say you wanted to see how much each expense compares to the average:

description	category	created_at	cost	avg
bus ride	...	...	3	17.5
lunch	...	...	15	17.5
...

You could use a subquery to calculate the average:

select *, (select avg(cost) from expenses)
from expenses;

But we could also be a little fancier and use a window function call:

select *, avg(cost) over()
from expenses;

Although we have some new syntax to understand (which we'll get into later), that's noticeably shorter! What just happened?

Hello, Window Frames

Let's try to visualise the window function call. For each row, Postgres calculates the average cost of the rest of the table, which is the default "window frame":

Now if that was all you could do with window function calls, I'd be disappointed too. Thankfully, it's not! Aside from being shorter, there are some key features that are harder to replicate with subqueries:

calculations can be done over a subset of the table (i.e. a smaller window frame) instead of the whole table
in addition to the usual aggregation functions like avg or max, there are window functions, which can only be used as part of window function calls

Reframing the Window

Let's try calculating over a subset. Knowing the average across all expenses wasn't very helpful - a big purchase could skew the calculation, so let's instead compare each expense to the category average:

select *, avg(cost) over(partition by category) as "avg in category"
from expenses;

Our result now looks something like:

description	category	created_at	cost	avg in category
tea break	food & drinks	...	5	24
lunch	food & drinks	...	15	24
...
taxi to home	transport	...	20	7.5
bus ride home	transport	...	3	7.5
...

That's a bit more helpful! Let's visualise this:

For each row, our new window frame includes only other rows with the same category, and the average is calculated based on those rows.

This is also a good time to take a brief look at the syntax we've used so far:

..., avg(cost) OVER(PARTITION BY category), ...

In the above:

avg(cost) is the aggregation function that we want to execute over the window frame - we could use other functions such as max or min
OVER is the keyword which tells Postgres we were using a window function call. Everything in parentheses specifies how our table will be divided into different window frames
in this case, we are using PARTITION BY category, which means we are dividing the table into different window frames based on their category

Window Functions

Now that we know a little more about the syntax, let's look at the other hidden power of window functions calls... window functions themselves!

It's coming to the end of the month, and you want to know the biggest expense in each category, for each day. There's a window function for that - rank!

With rank, we can calculate the order of each row in it's window frame. Note we'll still be returning every row in the table, not just the top expenses - we'll fix that later:

select
  *,
  rank() over(
    partition by category, created_at
    order by cost desc
  )
from expenses;

Notice the additional syntax in our window function call:

... OVER(PARTITION BY category, created_at ORDER BY cost DESC) ...

we've added a second condition to PARTITION BY to further divide each window frame by the day each entry was created_at (this is very similar to GROUP BY)
there's now an ORDER BY ... DESC that orders the rows within each frame from highest to lowest cost, so we'll know that a rank of 1 would be the most expensive

Another quick visual:

So for each row, our window frame is other rows with the same category and created_at date, and we order the rows from highest to lowest cost.

The query gives us a result like:

description	category	created_at	cost	rank
groceries	food & drinks	2020-01-01	60	1
dinner	food & drinks	2020-01-01	35	2
lunch	food & drinks	2020-01-01	15	3
...
taxi	transport	2020-01-01	20	1
bus ride	transport	2020-01-01	3	2
bus ride	transport	2020-01-02	4	1
...

Now that we have a ranking, we could use it as a subquery and filter out expenses that are rank 2 or higher.

select *
from (
  -- our earlier query
  ...
) as "ranked_expenses"
where rank = 1;

Which leaves us with exactly what we want - nice!

description	category	created_at	cost	rank
groceries	food & drinks	2020-01-01	60	1
lunch	food & drinks	2020-01-02	15	1
taxi to home	transport	2020-01-01	20	1
bus ride home	transport	2020-01-02	4	1
...

I hoped that helped you grok window functions! If you want to play around with the data in this example, there's an sqlfiddle - in case that doesn't exist at the time of reading, you can also get the SQL to create the table and query the data from this gist.

In the next post, we'll look deeper into Window Functions that involve ranking.

dense_rank	rank	description	cost
1	1	...	...
2	2	...	...
3	3	...	...
4	4	...	...
4	4	...	...
4	4	...	...
5	7	...	...
6	8	...	...
7	9	...	...
7	9	...	...

dense_rank	rank	description	cost
1	1	...	...
2	2	...	...
3	3	...	...
4	4	...	...
4	4	...	...
4	4	...	...
5	7	...	...
6	8	...	...
7	9	...	...
7	9	...	...

dense_rank	rank	description	cost
1	1	...	...
2	2	...	...
3	3	...	...
4	4	...	...
4	4	...	...
4	4	...	...
5	7	...	...
6	8	...	...
7	9	...	...
7	9	...	...