Rails Scopes – Putting a Limit on JSON

My Conditions project has two wireless “conditions” sensors sending air temperature and humidity to a Rails server every minute. Now I want to display the current temperature and humidity on a CharlieWing. The first task is to get the Rails server to stop dumping the entire history of temperature readings into the JSON file returned when I request it. I’d like to implement this with URL parameters so I can request (for example) http://192.168.2.2/measures?station=1&limit=1 but my attempts so far have failed. As usual, I’m going to put my pedant hat on and get this right.

Introduction

I want to be able to modify the parameters passed to the Rails server via the URL and so control the data set returned as JSON. The part of the URL after the question mark ‘?’ is generally referred to as the query string. I won’t use that terminology but instead refer to the parameter and value pairs.

For example the URL http://localhost:3000/temps?station=12&limit=3 has:

  • A querystring of station=12&limit=3
  • A parameter station with the value 12
  • A parameter limit with the value 3

Simplified Scenario

This scenario uses a Rails app with a single model. I’ll prove some of the code I’ll use later in the More Complex Scenario.

Create a Basic Rails Service

I’ve left out the responses to all these commands so I’ve assumed everything works.

  1. Create a Rails app
    ~: rails new climate04
  2. Change into the new app directory
    ~: cd climate04
    ~/climate04:
  3. Create the database
    ~/climate04: rake db:create
  4. Create a scaffold
    Note: I’m using the scaffold so I can test web pages, JSON via web pages and test via the Rails Console.
    ~/climate04: rails generate scaffold Temp value:float
  5. Migrate the new class into the database
    ~/climate04: rake db:migrate
  6. Make console output more pretty. Add gem 'hirb' to the Gemfile in the root folder of the Rails app.
  7. Create some data with Rails Console
    ~/climate04: rails c
    Running via Spring preloader in process 3777
    Loading development environment (Rails 5.0.6)
    irb(main):001:0> Temp.all
    D, [2017-11-29T16:38:42.885032 #3777] DEBUG -- : Temp Load (0.9ms) SELECT "temps".* FROM "temps"
    +----+-------+-------------------------+-------------------------+
    | id | value | created_at              | updated_at              |
    +----+-------+-------------------------+-------------------------+
    |  1 | 100.0 | 2017-11-29 05:37:50 UTC | 2017-11-29 05:37:50 UTC |
    +----+-------+-------------------------+-------------------------+
    1 row in set
    irb(main):002:0> Temp.create(value: 100)
    D, [2017-11-29T16:38:53.342343 #3777] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-29T16:38:53.345695 #3777] DEBUG -- : SQL (0.5ms) INSERT INTO "temps" ("value", "created_at", "updated_at") VALUES (?, ?, ?) [["value", 100.0], ["created_at", "2017-11-29 05:38:53.342910"], ["updated_at", "2017-11-29 05:38:53.342910"]]
    D, [2017-11-29T16:38:53.348521 #3777] DEBUG -- : (2.4ms) commit transaction
    +----+-------+-------------------------+-------------------------+
    | id | value | created_at              | updated_at              |
    +----+-------+-------------------------+-------------------------+
    |  2 | 100.0 | 2017-11-29 05:38:53 UTC | 2017-11-29 05:38:53 UTC |
    +----+-------+-------------------------+-------------------------+
    1 row in set
    irb(main):003:0> Temp.create(value: 100)
    D, [2017-11-29T16:38:55.045169 #3777] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-29T16:38:55.047662 #3777] DEBUG -- : SQL (0.4ms) INSERT INTO "temps" ("value", "created_at", "updated_at") VALUES (?, ?, ?) [["value", 100.0], ["created_at", "2017-11-29 05:38:55.045559"], ["updated_at", "2017-11-29 05:38:55.045559"]]
    D, [2017-11-29T16:38:55.050315 #3777] DEBUG -- : (2.2ms) commit transaction
    +----+-------+-------------------------+-------------------------+
    | id | value | created_at              | updated_at              |
    +----+-------+-------------------------+-------------------------+
    | 3  | 100.0 | 2017-11-29 05:38:55 UTC | 2017-11-29 05:38:55 UTC |
    +----+-------+-------------------------+-------------------------+
    1 row in set
    irb(main):004:0> Temp.create(value: 100)
    D, [2017-11-29T16:38:56.173346 #3777] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-29T16:38:56.175889 #3777] DEBUG -- : SQL (0.4ms) INSERT INTO "temps" ("value", "created_at", "updated_at") VALUES (?, ?, ?) [["value", 100.0], ["created_at", "2017-11-29 05:38:56.173788"], ["updated_at", "2017-11-29 05:38:56.173788"]]
    D, [2017-11-29T16:38:56.178541 #3777] DEBUG -- : (2.3ms) commit transaction
    +----+-------+-------------------------+-------------------------+
    | id | value | created_at              | updated_at              |
    +----+-------+-------------------------+-------------------------+
    |  4 | 100.0 | 2017-11-29 05:38:56 UTC | 2017-11-29 05:38:56 UTC |
    +----+-------+-------------------------+-------------------------+
    1 row in set
    irb(main):005:0> Temp.create(value: 100)
    D, [2017-11-29T16:38:57.117736 #3777] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-29T16:38:57.120683 #3777] DEBUG -- : SQL (0.4ms) INSERT INTO "temps" ("value", "created_at", "updated_at") VALUES (?, ?, ?) [["value", 100.0], ["created_at", "2017-11-29 05:38:57.118302"], ["updated_at", "2017-11-29 05:38:57.118302"]]
    D, [2017-11-29T16:38:57.123357 #3777] DEBUG -- : (2.2ms) commit transaction
    +----+-------+-------------------------+-------------------------+
    | id | value | created_at              | updated_at              |
    +----+-------+-------------------------+-------------------------+
    |  5 | 100.0 | 2017-11-29 05:38:57 UTC | 2017-11-29 05:38:57 UTC |
    +----+-------+-------------------------+-------------------------+
    1 row in set
    irb(main):006:0> Temp.create(value: 100)
    D, [2017-11-29T16:38:57.981541 #3777] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-29T16:38:57.984440 #3777] DEBUG -- : SQL (0.5ms) INSERT INTO "temps" ("value", "created_at", "updated_at") VALUES (?, ?, ?) [["value", 100.0], ["created_at", "2017-11-29 05:38:57.982087"], ["updated_at", "2017-11-29 05:38:57.982087"]]
    D, [2017-11-29T16:38:57.987252 #3777] DEBUG -- : (2.3ms) commit transaction
    +----+-------+-------------------------+-------------------------+
    | id | value | created_at              | updated_at              |
    +----+-------+-------------------------+-------------------------+
    |  6 | 100.0 | 2017-11-29 05:38:57 UTC | 2017-11-29 05:38:57 UTC |
    +----+-------+-------------------------+-------------------------+
    1 row in set
    irb(main):007:0> Temp.create(value: 100)
    D, [2017-11-29T16:38:58.861516 #3777] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-29T16:38:58.864608 #3777] DEBUG -- : SQL (0.4ms) INSERT INTO "temps" ("value", "created_at", "updated_at") VALUES (?, ?, ?) [["value", 100.0], ["created_at", "2017-11-29 05:38:58.862060"], ["updated_at", "2017-11-29 05:38:58.862060"]]
    D, [2017-11-29T16:38:58.867372 #3777] DEBUG -- : (2.3ms) commit transaction
    +----+-------+-------------------------+-------------------------+
    | id | value | created_at              | updated_at              |
    +----+-------+-------------------------+-------------------------+
    |  7 | 100.0 | 2017-11-29 05:38:58 UTC | 2017-11-29 05:38:58 UTC |
    +----+-------+-------------------------+-------------------------+
    1 row in set
    irb(main):008:0> Temp.create(value: 100)
    D, [2017-11-29T16:38:59.813117 #3777] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-29T16:38:59.815655 #3777] DEBUG -- : SQL (0.4ms) INSERT INTO "temps" ("value", "created_at", "updated_at") VALUES (?, ?, ?) [["value", 100.0], ["created_at", "2017-11-29 05:38:59.813524"], ["updated_at", "2017-11-29 05:38:59.813524"]]
    D, [2017-11-29T16:38:59.818302 #3777] DEBUG -- : (2.2ms) commit transaction
    +----+-------+-------------------------+-------------------------+
    | id | value | created_at              | updated_at              |
    +----+-------+-------------------------+-------------------------+
    |  8 | 100.0 | 2017-11-29 05:38:59 UTC | 2017-11-29 05:38:59 UTC |
    +----+-------+-------------------------+-------------------------+
    1 row in set
    irb(main):009:0> Temp.create(value: 100)
    D, [2017-11-29T16:39:00.709118 #3777] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-29T16:39:00.711653 #3777] DEBUG -- : SQL (0.4ms) INSERT INTO "temps" ("value", "created_at", "updated_at") VALUES (?, ?, ?) [["value", 100.0], ["created_at", "2017-11-29 05:39:00.709523"], ["updated_at", "2017-11-29 05:39:00.709523"]]
    D, [2017-11-29T16:39:00.714298 #3777] DEBUG -- : (2.2ms) commit transaction
    +----+-------+-------------------------+-------------------------+
    | id | value | created_at              | updated_at              |
    +----+-------+-------------------------+-------------------------+
    |  9 | 100.0 | 2017-11-29 05:39:00 UTC | 2017-11-29 05:39:00 UTC |
    +----+-------+-------------------------+-------------------------+
    1 row in set
    irb(main):010:0> Temp.all
    D, [2017-11-29T16:39:05.365624 #3777] DEBUG -- : Temp Load (0.2ms) SELECT "temps".* FROM "temps"
    +----+-------+-------------------------+-------------------------+
    | id | value | created_at              | updated_at              |
    +----+-------+-------------------------+-------------------------+
    |  1 | 100.0 | 2017-11-29 05:37:50 UTC | 2017-11-29 05:37:50 UTC |
    |  2 | 100.0 | 2017-11-29 05:38:53 UTC | 2017-11-29 05:38:53 UTC |
    |  3 | 100.0 | 2017-11-29 05:38:55 UTC | 2017-11-29 05:38:55 UTC |
    |  4 | 100.0 | 2017-11-29 05:38:56 UTC | 2017-11-29 05:38:56 UTC |
    |  5 | 100.0 | 2017-11-29 05:38:57 UTC | 2017-11-29 05:38:57 UTC |
    |  6 | 100.0 | 2017-11-29 05:38:57 UTC | 2017-11-29 05:38:57 UTC |
    |  7 | 100.0 | 2017-11-29 05:38:58 UTC | 2017-11-29 05:38:58 UTC |
    |  8 | 100.0 | 2017-11-29 05:38:59 UTC | 2017-11-29 05:38:59 UTC |
    |  9 | 100.0 | 2017-11-29 05:39:00 UTC | 2017-11-29 05:39:00 UTC |
    +----+-------+-------------------------+-------------------------+
    9 rows in set

Modify the App

The point of modifying the app is to go from having all data returned in a JSON page to returning only limited data.

  1. Create a scope on the model’s class:
    Full app/models/temp.rb file
    class Temp < ApplicationRecord
      scope :recent_limit, -> (limit) { order("created_at desc").limit(limit) }
    end
  2. Test the scope in Rails Console
    irb(main):001:0> Temp.recent_limit(2)
    D, [2017-11-29T16:44:52.873966 #3848] DEBUG -- : Temp Load (1.0ms) SELECT "temps".* FROM "temps" ORDER BY created_at desc LIMIT ? [["LIMIT", 2]]
    +----+-------+-------------------------+-------------------------+
    | id | value | created_at              | updated_at              |
    +----+-------+-------------------------+-------------------------+
    |  9 | 100.0 | 2017-11-29 05:39:00 UTC | 2017-11-29 05:39:00 UTC |
    |  8 | 100.0 | 2017-11-29 05:38:59 UTC | 2017-11-29 05:38:59 UTC |
    +----+-------+-------------------------+-------------------------+
    2 rows in set
  3. Add the scope to the controller.
    Note the scope uses the already-populated @temps collection object on which to apply the scope.
    Excerpt from: app/controllers/temps_controller.rb

    # GET /temps
    # GET /temps.json
    def index
      @temps = Temp.all
      # https://www.justinweiss.com/articles/search-and-filter-rails-models-without-bloating-your-controller
      @temps = @temps.recent_limit(params[:limit]) if params[:limit].present?
    end
    
  4. Test the filter with curl or a browser
    Note: line breaks added for clarity

    ~/climate04: curl http://localhost:3000/temps.json
    [
    {"id":1,"value":100.0,"created_at":"2017-11-29T05:37:50.030Z","updated_at":"2017-11-29T05:37:50.030Z","url":"http://localhost:3000/temps/1.json"},
    {"id":2,"value":100.0,"created_at":"2017-11-29T05:38:53.342Z","updated_at":"2017-11-29T05:38:53.342Z","url":"http://localhost:3000/temps/2.json"},
    {"id":3,"value":100.0,"created_at":"2017-11-29T05:38:55.045Z","updated_at":"2017-11-29T05:38:55.045Z","url":"http://localhost:3000/temps/3.json"},
    {"id":4,"value":100.0,"created_at":"2017-11-29T05:38:56.173Z","updated_at":"2017-11-29T05:38:56.173Z","url":"http://localhost:3000/temps/4.json"},
    {"id":5,"value":100.0,"created_at":"2017-11-29T05:38:57.118Z","updated_at":"2017-11-29T05:38:57.118Z","url":"http://localhost:3000/temps/5.json"},
    {"id":6,"value":100.0,"created_at":"2017-11-29T05:38:57.982Z","updated_at":"2017-11-29T05:38:57.982Z","url":"http://localhost:3000/temps/6.json"},
    {"id":7,"value":100.0,"created_at":"2017-11-29T05:38:58.862Z","updated_at":"2017-11-29T05:38:58.862Z","url":"http://localhost:3000/temps/7.json"},
    {"id":8,"value":100.0,"created_at":"2017-11-29T05:38:59.813Z","updated_at":"2017-11-29T05:38:59.813Z","url":"http://localhost:3000/temps/8.json"},
    {"id":9,"value":100.0,"created_at":"2017-11-29T05:39:00.709Z","updated_at":"2017-11-29T05:39:00.709Z","url":"http://localhost:3000/temps/9.json"}
    ]
    ~/climate04: curl http://localhost:3000/temps.json?limit=2
    [
    {"id":9,"value":100.0,"created_at":"2017-11-29T05:39:00.709Z","updated_at":"2017-11-29T05:39:00.709Z","url":"http://localhost:3000/temps/9.json"},
    {"id":8,"value":100.0,"created_at":"2017-11-29T05:38:59.813Z","updated_at":"2017-11-29T05:38:59.813Z","url":"http://localhost:3000/temps/8.json"}
    ]
    ~/climate04:

BOOM!

Adding a Latest Parameter

Now use the same logic to add a ‘latest’ parameter.

  1. Modify the model
    Full app/models/temp.rb file.

    class Temp < ApplicationRecord
      scope :recent_limit, -> (limit) { order("created_at desc").limit(limit) }
      scope :latest, -> { order("created_at desc").limit(1) }
    end
    
  2. Modify the controller
    Note: Excerpt from app/controllers/temps_controller.rb

    # GET /temps
    # GET /temps.json
    def index
      @temps = Temp.all
    
      # https://www.justinweiss.com/articles/search-and-filter-rails-models-without-bloating-your-controller/
      @temps = @temps.recent_limit(params[:limit]) if params[:limit].present?
      @temps = @temps.latest() if params[:latest].present?
    end
    
  3. Test
    Note a URL parameter must have a value or it’s ignored.
    WORKS: http://localhost:3000/temps.json?latest=0
    FAILS: http://localhost:3000/temps.json?latest

    ~/climate04: curl http://localhost:3000/temps.json?latest=0
    [{"id":9,"value":100.0,"created_at":"2017-11-29T05:39:00.709Z","updated_at":"2017-11-29T05:39:00.709Z","url":"http://localhost:3000/temps/9.json"}]
    
    ~/climate04: curl http://localhost:3000/temps.json?latest
    [
    {"id":1,"value":100.0,"created_at":"2017-11-29T05:37:50.030Z","updated_at":"2017-11-29T05:37:50.030Z","url":"http://localhost:3000/temps/1.json"},
    {"id":2,"value":100.0,"created_at":"2017-11-29T05:38:53.342Z","updated_at":"2017-11-29T05:38:53.342Z","url":"http://localhost:3000/temps/2.json"},
    {"id":3,"value":100.0,"created_at":"2017-11-29T05:38:55.045Z","updated_at":"2017-11-29T05:38:55.045Z","url":"http://localhost:3000/temps/3.json"},
    {"id":4,"value":100.0,"created_at":"2017-11-29T05:38:56.173Z","updated_at":"2017-11-29T05:38:56.173Z","url":"http://localhost:3000/temps/4.json"},
    {"id":5,"value":100.0,"created_at":"2017-11-29T05:38:57.118Z","updated_at":"2017-11-29T05:38:57.118Z","url":"http://localhost:3000/temps/5.json"},
    {"id":6,"value":100.0,"created_at":"2017-11-29T05:38:57.982Z","updated_at":"2017-11-29T05:38:57.982Z","url":"http://localhost:3000/temps/6.json"},
    {"id":7,"value":100.0,"created_at":"2017-11-29T05:38:58.862Z","updated_at":"2017-11-29T05:38:58.862Z","url":"http://localhost:3000/temps/7.json"},
    {"id":8,"value":100.0,"created_at":"2017-11-29T05:38:59.813Z","updated_at":"2017-11-29T05:38:59.813Z","url":"http://localhost:3000/temps/8.json"},
    {"id":9,"value":100.0,"created_at":"2017-11-29T05:39:00.709Z","updated_at":"2017-11-29T05:39:00.709Z","url":"http://localhost:3000/temps/9.json"}
    ]
    ~/climate04:
    

So now I have what I wanted when I started Modifying the App (above). Well, I want more. The actual data I use has a station_id attached to every temperature and humidity record created. So now I need to slightly complicate things to get it to a point I can use it in production.

More Complex Scenario

To fulfil all that I need this code to do I must start with a more complex Rails app. I’ll build a Station model to represent the physical locations where my temperature and humidity probes are. Then I’ll record each measurement against the station that generated the data.

The Basics

  1. Create the Rails app
    ~: rails new climate05
  2. Change to the new app directory
    ~: cd climate05
    ~/climate05:
  3. Create the database
    ~/climate05: rake db:create
  4. Create a scaffold for Station
    ~/climate05: rails generate scaffold Station name:string
  5. Migrate the Station model to the database
    ~/climate05: rake db:migrate
  6. Pretty up the console output. Add gem 'hirb' to the Gemfile in the root folder of the Rails app.
  7. Create a couple of Stations using Rails Console
    ~/climate05: rails c
    Running via Spring preloader in process 5914
    Loading development environment (Rails 5.0.6)
    irb(main):001:0> Station.create(name: "Outside")
    D, [2017-11-30T08:39:57.341906 #5914] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-30T08:39:57.351125 #5914] DEBUG -- : SQL (0.4ms) INSERT INTO "stations" ("name", "created_at", "updated_at") VALUES (?, ?, ?) [["name", "Outside"], ["created_at", "2017-11-29 21:39:57.342349"], ["updated_at", "2017-11-29 21:39:57.342349"]]
    D, [2017-11-30T08:39:57.354102 #5914] DEBUG -- : (2.5ms) commit transaction
    +----+---------+-------------------------+-------------------------+
    | id | name    | created_at              | updated_at              |
    +----+---------+-------------------------+-------------------------+
    |  1 | Outside | 2017-11-29 21:39:57 UTC | 2017-11-29 21:39:57 UTC |
    +----+---------+-------------------------+-------------------------+
    1 row in set
    irb(main):002:0> Station.create(name: "Inside")
    D, [2017-11-30T08:40:04.749707 #5914] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-30T08:40:04.752476 #5914] DEBUG -- : SQL (0.4ms) INSERT INTO "stations" ("name", "created_at", "updated_at") VALUES (?, ?, ?) [["name", "Inside"], ["created_at", "2017-11-29 21:40:04.750290"], ["updated_at", "2017-11-29 21:40:04.750290"]]
    D, [2017-11-30T08:40:04.755210 #5914] DEBUG -- : (2.3ms) commit transaction
    +----+--------+-------------------------+-------------------------+
    | id | name   | created_at              | updated_at              |
    +----+--------+-------------------------+-------------------------+
    |  2 | Inside | 2017-11-29 21:40:04 UTC | 2017-11-29 21:40:04 UTC |
    +----+--------+-------------------------+-------------------------+
    1 row in set
    irb(main):003:0> Station.all
    D, [2017-11-30T08:40:08.030270 #5914] DEBUG -- : Station Load (0.3ms) SELECT "stations".* FROM "stations"
    +----+---------+-------------------------+-------------------------+
    | id | name    | created_at              | updated_at              |
    +----+---------+-------------------------+-------------------------+
    |  1 | Outside | 2017-11-29 21:39:57 UTC | 2017-11-29 21:39:57 UTC |
    |  2 | Inside  | 2017-11-29 21:40:04 UTC | 2017-11-29 21:40:04 UTC |
    +----+---------+-------------------------+-------------------------+
    2 rows in set
    irb(main):004:0>
  8. Add a Measure model and have it hold a reference to a Station
    ~/climate05: rails generate scaffold Measure temperature:float humidity:float station:references
  9. At this point Measures hold references to a Station, but Rails likes relationships to be described at both ends. So we need to edit Station and describe the reverse relation to Measure.
    Full listing of station.rb

    class Station < ApplicationRecord
      has_many :measures # added 
    end
  10. So now we can push the Measure model into the database.
    ~/climate05: rake db:migrate
  11. Again using Rails Console we can now add some data to the Measure table.
    ~/climate05: rails c
    Running via Spring preloader in process 6024
    Loading development environment (Rails 5.0.6)
    Cannot read termcap database; using dumb terminal settings.
    irb(main):001:0> Measure.create(temperature:25, humidity:75, station_id:1)
    D, [2017-11-30T08:48:44.641600 #6024] DEBUG -- : (0.0ms) begin transaction
    D, [2017-11-30T08:48:44.666686 #6024] DEBUG -- : Station Load (0.1ms) SELECT "stations".* FROM "stations" WHERE "stations"."id" = ? LIMIT ? [["id", 1], ["LIMIT", 1]]
    D, [2017-11-30T08:48:44.672578 #6024] DEBUG -- : SQL (0.4ms) INSERT INTO "measures" ("temperature", "humidity", "station_id", "created_at", "updated_at") VALUES (?, ?, ?, ?, ?) [["temperature", 25.0], ["humidity", 75.0], ["station_id", 1], ["created_at", "2017-11-29 21:48:44.670299"], ["updated_at", "2017-11-29 21:48:44.670299"]]
    D, [2017-11-30T08:48:44.673817 #6024] DEBUG -- : (0.9ms) commit transaction
    +----+-------------+----------+------------+----------------------+----------------------+
    | id | temperature | humidity | station_id | created_at           | updated_at           |
    +----+-------------+----------+------------+----------------------+----------------------+
    |  1 |        25.0 |     75.0 |          1 | 2017-11-29 21:48:... | 2017-11-29 21:48:... |
    +----+-------------+----------+------------+----------------------+----------------------+
    1 row in set
    irb(main):002:0> s1 = Station.find(1)
    D, [2017-11-30T08:49:00.419915 #6024] DEBUG -- : Station Load (0.2ms) SELECT "stations".* FROM "stations" WHERE "stations"."id" = ? LIMIT ? [["id", 1], ["LIMIT", 1]]
    +----+---------+-------------------------+-------------------------+
    | id | name    | created_at              |              updated_at |
    +----+---------+-------------------------+-------------------------+
    |  1 | Outside | 2017-11-29 21:39:57 UTC | 2017-11-29 21:39:57 UTC |
    +----+---------+-------------------------+-------------------------+
    1 row in set
    irb(main):003:0> Measure.create(temperature:26, humidity:76, station: s1)
    D, [2017-11-30T08:49:20.765263 #6024] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-30T08:49:20.768279 #6024] DEBUG -- : SQL (0.4ms) INSERT INTO "measures" ("temperature", "humidity", "station_id", "created_at", "updated_at") VALUES (?, ?, ?, ?, ?) [["temperature", 26.0], ["humidity", 76.0], ["station_id", 1], ["created_at", "2017-11-29 21:49:20.766052"], ["updated_at", "2017-11-29 21:49:20.766052"]]
    D, [2017-11-30T08:49:20.770950 #6024] DEBUG -- : (2.3ms) commit transaction
    +----+-------------+----------+------------+----------------------+----------------------+
    | id | temperature | humidity | station_id | created_at           | updated_at           |
    +----+-------------+----------+------------+----------------------+----------------------+
    |  2 |        26.0 |     76.0 |          1 | 2017-11-29 21:49:... | 2017-11-29 21:49:... |
    +----+-------------+----------+------------+----------------------+----------------------+
    1 row in set
    irb(main):004:0> Measure.create(temperature:27, humidity:77, station: s1)
    D, [2017-11-30T08:49:33.507560 #6024] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-30T08:49:33.510608 #6024] DEBUG -- : SQL (0.4ms) INSERT INTO "measures" ("temperature", "humidity", "station_id", "created_at", "updated_at") VALUES (?, ?, ?, ?, ?) [["temperature", 27.0], ["humidity", 77.0], ["station_id", 1], ["created_at", "2017-11-29 21:49:33.508351"], ["updated_at", "2017-11-29 21:49:33.508351"]]
    D, [2017-11-30T08:49:33.513407 #6024] DEBUG -- : (2.4ms) commit transaction
    +----+-------------+----------+------------+----------------------+----------------------+
    | id | temperature | humidity | station_id | created_at           | updated_at           |
    +----+-------------+----------+------------+----------------------+----------------------+
    |  3 |        27.0 |     77.0 |          1 | 2017-11-29 21:49:... | 2017-11-29 21:49:... |
    +----+-------------+----------+------------+----------------------+----------------------+
    1 row in set
    irb(main):005:0> Measure.create(temperature:12, humidity:12, station_id: 2)
    D, [2017-11-30T08:49:52.593952 #6024] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-30T08:49:52.595693 #6024] DEBUG -- : Station Load (0.1ms) SELECT "stations".* FROM "stations" WHERE "stations"."id" = ? LIMIT ? [["id", 2], ["LIMIT", 1]]
    D, [2017-11-30T08:49:52.601386 #6024] DEBUG -- : SQL (3.5ms) INSERT INTO "measures" ("temperature", "humidity", "station_id", "created_at", "updated_at") VALUES (?, ?, ?, ?, ?) [["temperature", 12.0], ["humidity", 12.0], ["station_id", 2], ["created_at", "2017-11-29 21:49:52.596222"], ["updated_at", "2017-11-29 21:49:52.596222"]]
    D, [2017-11-30T08:49:52.602329 #6024] DEBUG -- : (0.6ms) commit transaction
    +----+-------------+----------+------------+----------------------+----------------------+
    | id | temperature | humidity | station_id | created_at           | updated_at           |
    +----+-------------+----------+------------+----------------------+----------------------+
    |  4 |        12.0 |     12.0 |          2 | 2017-11-29 21:49:... | 2017-11-29 21:49:... |
    +----+-------------+----------+------------+----------------------+----------------------+
    1 row in set
    irb(main):006:0> Measure.create(temperature:14, humidity:14, station_id: 2)
    D, [2017-11-30T08:50:03.962147 #6024] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-30T08:50:03.963720 #6024] DEBUG -- : Station Load (0.2ms) SELECT "stations".* FROM "stations" WHERE "stations"."id" = ? LIMIT ? [["id", 2], ["LIMIT", 1]]
    D, [2017-11-30T08:50:03.966172 #6024] DEBUG -- : SQL (0.4ms) INSERT INTO "measures" ("temperature", "humidity", "station_id", "created_at", "updated_at") VALUES (?, ?, ?, ?, ?) [["temperature", 14.0], ["humidity", 14.0], ["station_id", 2], ["created_at", "2017-11-29 21:50:03.964281"], ["updated_at", "2017-11-29 21:50:03.964281"]]
    D, [2017-11-30T08:50:03.968692 #6024] DEBUG -- : (2.2ms) commit transaction
    +----+-------------+----------+------------+----------------------+----------------------+
    | id | temperature | humidity | station_id | created_at           | updated_at           |
    +----+-------------+----------+------------+----------------------+----------------------+
    |  5 |        14.0 |     14.0 |          2 | 2017-11-29 21:50:... | 2017-11-29 21:50:... |
    +----+-------------+----------+------------+----------------------+----------------------+
    1 row in set
    irb(main):007:0> Measure.create(temperature:16, humidity:16, station_id: 2)
    D, [2017-11-30T08:50:11.026530 #6024] DEBUG -- : (0.1ms) begin transaction
    D, [2017-11-30T08:50:11.028320 #6024] DEBUG -- : Station Load (0.2ms) SELECT "stations".* FROM "stations" WHERE "stations"."id" = ? LIMIT ? [["id", 2], ["LIMIT", 1]]
    D, [2017-11-30T08:50:11.030939 #6024] DEBUG -- : SQL (0.4ms) INSERT INTO "measures" ("temperature", "humidity", "station_id", "created_at", "updated_at") VALUES (?, ?, ?, ?, ?) [["temperature", 16.0], ["humidity", 16.0], ["station_id", 2], ["created_at", "2017-11-29 21:50:11.028853"], ["updated_at", "2017-11-29 21:50:11.028853"]]
    D, [2017-11-30T08:50:11.033586 #6024] DEBUG -- : (2.2ms) commit transaction
    +----+-------------+----------+------------+----------------------+----------------------+
    | id | temperature | humidity | station_id | created_at           | updated_at           |
    +----+-------------+----------+------------+----------------------+----------------------+
    |  6 |        16.0 |     16.0 |          2 | 2017-11-29 21:50:... | 2017-11-29 21:50:... |
    +----+-------------+----------+------------+----------------------+----------------------+
    1 row in set
    irb(main):008:0> Station.all
    D, [2017-11-30T08:50:14.090611 #6024] DEBUG -- : Station Load (0.3ms) SELECT "stations".* FROM "stations"
    +----+---------+-------------------------+-------------------------+
    | id | name    | created_at              | updated_at              |
    +----+---------+-------------------------+-------------------------+
    |  1 | Outside | 2017-11-29 21:39:57 UTC | 2017-11-29 21:39:57 UTC |
    |  2 | Inside  | 2017-11-29 21:40:04 UTC | 2017-11-29 21:40:04 UTC |
    +----+---------+-------------------------+-------------------------+
    2 rows in set
    irb(main):009:0> Measure.all
    D, [2017-11-30T08:50:17.017978 #6024] DEBUG -- : Measure Load (0.2ms) SELECT "measures".* FROM "measures"
    +----+-------------+----------+------------+----------------------+----------------------+
    | id | temperature | humidity | station_id | created_at           | updated_at           |
    +----+-------------+----------+------------+----------------------+----------------------+
    |  1 |        25.0 |     75.0 |          1 | 2017-11-29 21:48:... | 2017-11-29 21:48:... |
    |  2 |        26.0 |     76.0 |          1 | 2017-11-29 21:49:... | 2017-11-29 21:49:... |
    |  3 |        27.0 |     77.0 |          1 | 2017-11-29 21:49:... | 2017-11-29 21:49:... |
    |  4 |        12.0 |     12.0 |          2 | 2017-11-29 21:49:... | 2017-11-29 21:49:... |
    |  5 |        14.0 |     14.0 |          2 | 2017-11-29 21:50:... | 2017-11-29 21:50:... |
    |  6 |        16.0 |     16.0 |          2 | 2017-11-29 21:50:... | 2017-11-29 21:50:... |
    +----+-------------+----------+------------+----------------------+----------------------+
    6 rows in set
    irb(main):010:0>
  12. We should check the Station — Measure relationship is working both ways.
    ~/climate05: rails c
    Running via Spring preloader in process 7401
    Loading development environment (Rails 5.0.6)
    Cannot read termcap database;
    using dumb terminal settings.
    irb(main):001:0> s1 = Station.find(1)
    D, [2017-11-30T11:55:47.649727 #7401] DEBUG -- : Station Load (0.2ms) SELECT "stations".* FROM "stations" WHERE "stations"."id" = ? LIMIT ? [["id", 1], ["LIMIT", 1]]
    +----+---------+-------------------------+-------------------------+
    | id | name    | created_at              | updated_at              |
    +----+---------+-------------------------+-------------------------+
    |  1 | Outside | 2017-11-29 21:39:57 UTC | 2017-11-29 21:39:57 UTC |
    +----+---------+-------------------------+-------------------------+
    1 row in set
    irb(main):002:0> m1 = Measure.find(1)
    D, [2017-11-30T11:55:56.588283 #7401] DEBUG -- : Measure Load (0.1ms) SELECT "measures".* FROM "measures" WHERE "measures"."id" = ? LIMIT ? [["id", 1], ["LIMIT", 1]]
    +----+-------------+----------+------------+-------------------------+-------------------------+
    | id | temperature | humidity | station_id | created_at              | updated_at              |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    |  1 |        25.0 |     75.0 |          1 | 2017-11-29 21:48:44 UTC | 2017-11-29 21:48:44 UTC |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    1 row in set
    irb(main):003:0> s1.measures
    D, [2017-11-30T11:56:00.719362 #7401] DEBUG -- : Measure Load (0.2ms) SELECT "measures".* FROM "measures" WHERE "measures"."station_id" = ? [["station_id", 1]]
    +----+-------------+----------+------------+-------------------------+-------------------------+
    | id | temperature | humidity | station_id | created_at              | updated_at              |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    |  1 |        25.0 |     75.0 |          1 | 2017-11-29 21:48:44 UTC | 2017-11-29 21:48:44 UTC |
    |  2 |        26.0 |     76.0 |          1 | 2017-11-29 21:49:20 UTC | 2017-11-29 21:49:20 UTC |
    |  3 |        27.0 |     77.0 |          1 | 2017-11-29 21:49:33 UTC | 2017-11-29 21:49:33 UTC |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    3 rows in set
    irb(main):004:0> m1.station
    D, [2017-11-30T11:56:07.282607 #7401] DEBUG -- : Station Load (0.1ms) SELECT "stations".* FROM "stations" WHERE "stations"."id" = ? LIMIT ? [["id", 1], ["LIMIT", 1]]
    +----+---------+-------------------------+-------------------------+
    | id | name    | created_at              | updated_at              |
    +----+---------+-------------------------+-------------------------+
    |  1 | Outside | 2017-11-29 21:39:57 UTC | 2017-11-29 21:39:57 UTC |
    +----+---------+-------------------------+-------------------------+
    1 row in set
    irb(main):005:0> exit
    ~/climate05:
    

At this stage we have a working, populated Rails app with a one-to-many relationship between Station and Measure. The problem is that when we access http://localhost:3000/measures.json we get all Measure data.

Filtering Measure Data by Station

Now I want to provide the station_id for the station I’m interested in and get back only the measure data for that station; using a URL like: http://localhost:3000/measures.json?station=1. Using what we learned above, I’ll first add a scope to the Measure model, then set up the controller to call that scope when the right parameter is received.

  1. Add a scope to Measure
    Full listing of app/models/measure.rb
    class Measure < ApplicationRecord
      belongs_to :station
    
      # https://www.justinweiss.com/articles/search-and-filter-rails-models-without-bloating-your-controller/
      scope :by_station, -> (station_id) { where("station_id = ?", "#{station_id}") }
    
    end
  2. Test it works using Rails Console
    ~/Junk/Climate/climate05: rails c
    Running via Spring preloader in process 7897
    Loading development environment (Rails 5.0.6)
    Cannot read termcap database;
    using dumb terminal settings.
    irb(main):001:0> Measure.by_station(1)
    D, [2017-11-30T12:50:57.030654 #7897] DEBUG -- : Measure Load (1.0ms) SELECT "measures".* FROM "measures" WHERE (station_id = '1')
    +----+-------------+----------+------------+-------------------------+-------------------------+
    | id | temperature | humidity | station_id | created_at              | updated_at              |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    |  1 |        25.0 |     75.0 |          1 | 2017-11-29 21:48:44 UTC | 2017-11-29 21:48:44 UTC |
    |  2 |        26.0 |     76.0 |          1 | 2017-11-29 21:49:20 UTC | 2017-11-29 21:49:20 UTC |
    |  3 |        27.0 |     77.0 |          1 | 2017-11-29 21:49:33 UTC | 2017-11-29 21:49:33 UTC |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    3 rows in set
    irb(main):002:0> exit
    ~/climate05:
  3. Change the controller to cause this new scope to take effect when a URL with a station_id parameter is received.
    # GET /measures
    # GET /measures.json
    def index
      @measures = Measure.all
    
      # Added
      @measures = @measures.by_station(params[:station_id]) if params[:station_id].present?
    end
  4. Test the scope works when requesting JSON via the correct URL:
    Note: line breaks added for clarity

    ~/climate05: curl http://localhost:3000/measures.json?station_id=1
    [
    {"id":1,"temperature":25.0,"humidity":75.0,"station_id":1,"created_at":"2017-11-29T21:48:44.670Z","updated_at":"2017-11-29T21:48:44.670Z","url":"http://localhost:3000/measures/1.json"},
    {"id":2,"temperature":26.0,"humidity":76.0,"station_id":1,"created_at":"2017-11-29T21:49:20.766Z","updated_at":"2017-11-29T21:49:20.766Z","url":"http://localhost:3000/measures/2.json"},
    {"id":3,"temperature":27.0,"humidity":77.0,"station_id":1,"created_at":"2017-11-29T21:49:33.508Z","updated_at":"2017-11-29T21:49:33.508Z","url":"http://localhost:3000/measures/3.json"}
    ]
    ~/climate05:

Done.

Latest Measures by Station with Limit

I had initially defined a scope called by_station_latest_with_limit but then did a bit more reading and was reminded that scopes are stackable. With a station scope and a latest scope I could call Measure.station(2).latest.limit(1) to get what I want. limit is built-in.

  1. Modify the model to provide a scope that will always sort by the latest records. I’ve also renamed by_station to just station.
    Full listing of measure.rb
    class Measure < ApplicationRecord
      belongs_to :station
      # https://www.justinweiss.com/articles/search-and-filter-rails-models-without-bloating-your-controller/
      scope :station, -> (station_id) { where("station_id = ?", "#{station_id}") }
      scope :latest, -> { order("created_at desc") }
    end
    
  2. Test it!
    ~/climate05: rails c
    Running via Spring preloader in process 8441
    Loading development environment (Rails 5.0.6)
    irb(main):001:0> Measure.latest
    D, [2017-11-30T13:27:25.953504 #8441] DEBUG -- : Measure Load (1.0ms) SELECT "measures".* FROM "measures" ORDER BY created_at desc
    +----+-------------+----------+------------+-------------------------+-------------------------+
    | id | temperature | humidity | station_id | created_at              | updated_at              |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    |  6 |        16.0 |     16.0 |          2 | 2017-11-29 21:50:11 UTC | 2017-11-29 21:50:11 UTC |
    |  5 |        14.0 |     14.0 |          2 | 2017-11-29 21:50:03 UTC | 2017-11-29 21:50:03 UTC |
    |  4 |        12.0 |     12.0 |          2 | 2017-11-29 21:49:52 UTC | 2017-11-29 21:49:52 UTC |
    |  3 |        27.0 |     77.0 |          1 | 2017-11-29 21:49:33 UTC | 2017-11-29 21:49:33 UTC |
    |  2 |        26.0 |     76.0 |          1 | 2017-11-29 21:49:20 UTC | 2017-11-29 21:49:20 UTC |
    |  1 |        25.0 |     75.0 |          1 | 2017-11-29 21:48:44 UTC | 2017-11-29 21:48:44 UTC |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    6 rows in set
    irb(main):002:0> Measure.station(1)
    D, [2017-11-30T13:27:35.254971 #8441] DEBUG -- : Measure Load (0.2ms) SELECT "measures".* FROM "measures" WHERE (station_id = '1')
    +----+-------------+----------+------------+-------------------------+-------------------------+
    | id | temperature | humidity | station_id | created_at              | updated_at              |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    |  1 |        25.0 |     75.0 |          1 | 2017-11-29 21:48:44 UTC | 2017-11-29 21:48:44 UTC |
    |  2 |        26.0 |     76.0 |          1 | 2017-11-29 21:49:20 UTC | 2017-11-29 21:49:20 UTC |
    |  3 |        27.0 |     77.0 |          1 | 2017-11-29 21:49:33 UTC | 2017-11-29 21:49:33 UTC |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    3 rows in set
    irb(main):003:0> Measure.station(1).latest
    D, [2017-11-30T13:27:41.701704 #8441] DEBUG -- : Measure Load (0.3ms) SELECT "measures".* FROM "measures" WHERE (station_id = '1') ORDER BY created_at desc
    +----+-------------+----------+------------+-------------------------+-------------------------+
    | id | temperature | humidity | station_id | created_at              | updated_at              |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    |  3 |        27.0 |     77.0 |          1 | 2017-11-29 21:49:33 UTC | 2017-11-29 21:49:33 UTC |
    |  2 |        26.0 |     76.0 |          1 | 2017-11-29 21:49:20 UTC | 2017-11-29 21:49:20 UTC |
    |  1 |        25.0 |     75.0 |          1 | 2017-11-29 21:48:44 UTC | 2017-11-29 21:48:44 UTC |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    3 rows in set
    irb(main):004:0> Measure.station(1).latest.limit(1)
    D, [2017-11-30T13:27:48.395816 #8441] DEBUG -- : Measure Load (0.4ms) SELECT "measures".* FROM "measures" WHERE (station_id = '1') ORDER BY created_at desc LIMIT ? [["LIMIT", 1]]
    +----+-------------+----------+------------+-------------------------+-------------------------+
    | id | temperature | humidity | station_id | created_at              | updated_at              |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    |  3 |        27.0 |     77.0 |          1 | 2017-11-29 21:49:33 UTC | 2017-11-29 21:49:33 UTC |
    +----+-------------+----------+------------+-------------------------+-------------------------+
    1 row in set
    irb(main):005:0>
    

Making It Work For JSON

Right now we can’t use this filtering method on JSON because we haven’t configured the controller correctly yet. We’ll do that now.

We have three scopes we can work with: station, latest and limit. When we set these up in measure_controller.rb we have to arrange them so they will be executed in the correct order. I’ve altered the index method like this:

# GET /measures
# GET /measures.json
def index
  @measures = Measure.all

  # Added
  @measures = @measures.station(params[:station]) if params[:station].present?
  @measures = @measures.latest.limit(params[:limit]) if params[:limit].present?
end

Depending on the parameters passed in the URL one, two or three lines are executed and the effect is cumulative.

  • When no parameters are passed the only line executed is @measures = Measure.all so all records are returned.
  • If only the station parameter is passed then @measures = Measure.all fills the @measures collection with all records then @measures = @measures.station(params[:station]) if params[:station].present? filters all records matching the provided value for station.
  • If only the limit parameter is passed in then @measures = Measure.all fills the @measures collection with all records. Then, when @measures = @measures.latest.limit(params[:limit]) if params[:limit].present? is executed the latest scope is applied and then the limit scope.
  • If both station and limit parameters are passed in, then all three lines are executed in that order. If the last two lines were reordered then you would get the latest(limit) records first, then they would be filtered for the station’s id. Which might yield nothing.

Testing

There’s yet another special condition to account for. When testing at the bash prompt (command line) on my Mac I found the URL was being truncated. If I enter curl http://localhost:3000/measures.json?station=2&limit=2 then it would execute without the limit=2 part. That’s because in bash ampersand (&) means “put this process in the background”. So to request JSON while using more than one parameter I have to put the URL in quotes, like so: curl "http://localhost:3000/measures.json?station=2&limit=2". With this minor technicality out of the way I can fully test. Line breaks added for clarity. (Yes, I know. I have to do something better for code formatting. Sorry)

~/climate05: curl http://localhost:3000/measures.json
[
{"id":1,"temperature":25.0,"humidity":75.0,"station_id":1,"created_at":"2017-11-29T21:48:44.670Z","updated_at":"2017-11-29T21:48:44.670Z","url":"http://localhost:3000/measures/1.json"},
{"id":2,"temperature":26.0,"humidity":76.0,"station_id":1,"created_at":"2017-11-29T21:49:20.766Z","updated_at":"2017-11-29T21:49:20.766Z","url":"http://localhost:3000/measures/2.json"},
{"id":3,"temperature":27.0,"humidity":77.0,"station_id":1,"created_at":"2017-11-29T21:49:33.508Z","updated_at":"2017-11-29T21:49:33.508Z","url":"http://localhost:3000/measures/3.json"},
{"id":4,"temperature":12.0,"humidity":12.0,"station_id":2,"created_at":"2017-11-29T21:49:52.596Z","updated_at":"2017-11-29T21:49:52.596Z","url":"http://localhost:3000/measures/4.json"},
{"id":5,"temperature":14.0,"humidity":14.0,"station_id":2,"created_at":"2017-11-29T21:50:03.964Z","updated_at":"2017-11-29T21:50:03.964Z","url":"http://localhost:3000/measures/5.json"},
{"id":6,"temperature":16.0,"humidity":16.0,"station_id":2,"created_at":"2017-11-29T21:50:11.028Z","updated_at":"2017-11-29T21:50:11.028Z","url":"http://localhost:3000/measures/6.json"}
]
~/climate05: curl http://localhost:3000/measures.json?station=2
[
{"id":4,"temperature":12.0,"humidity":12.0,"station_id":2,"created_at":"2017-11-29T21:49:52.596Z","updated_at":"2017-11-29T21:49:52.596Z","url":"http://localhost:3000/measures/4.json"},
{"id":5,"temperature":14.0,"humidity":14.0,"station_id":2,"created_at":"2017-11-29T21:50:03.964Z","updated_at":"2017-11-29T21:50:03.964Z","url":"http://localhost:3000/measures/5.json"},
{"id":6,"temperature":16.0,"humidity":16.0,"station_id":2,"created_at":"2017-11-29T21:50:11.028Z","updated_at":"2017-11-29T21:50:11.028Z","url":"http://localhost:3000/measures/6.json"}
]
~/climate05: curl http://localhost:3000/measures.json?limit=2
[
{"id":6,"temperature":16.0,"humidity":16.0,"station_id":2,"created_at":"2017-11-29T21:50:11.028Z","updated_at":"2017-11-29T21:50:11.028Z","url":"http://localhost:3000/measures/6.json"},
{"id":5,"temperature":14.0,"humidity":14.0,"station_id":2,"created_at":"2017-11-29T21:50:03.964Z","updated_at":"2017-11-29T21:50:03.964Z","url":"http://localhost:3000/measures/5.json"}
]
~/climate05: curl "http://localhost:3000/measures.json?station=2&limit=2"
[
{"id":6,"temperature":16.0,"humidity":16.0,"station_id":2,"created_at":"2017-11-29T21:50:11.028Z","updated_at":"2017-11-29T21:50:11.028Z","url":"http://localhost:3000/measures/6.json"},
{"id":5,"temperature":14.0,"humidity":14.0,"station_id":2,"created_at":"2017-11-29T21:50:03.964Z","updated_at":"2017-11-29T21:50:03.964Z","url":"http://localhost:3000/measures/5.json"}
]
~/climate05:

Conclusion

And there it is. The URL that my simple Arduino can use to get just one record from the database for a station number it nominates. The next post will be about parsing the JSON on the Arduino using its very limited memory and processor constraints.

That is all