Rails API with query string time stamp

Question

I am continuing on creating an API with ruby on rails. I have got the basis of it done by following https://levelup.gitconnected.com/7-steps-to-create-an-api-in-rails-2f984c7c4286

but instead of Forests and trails, I am using restaurants and Comments as my models. Restaurants has many comments and comments has one restaurant.

Right now I can call on: http://localhost:3000/api/v1/restaurants/1?by_updated_at=2022-08-20T10:00:00.000Z

This will send me the exact comments that updated at that given time.

[
    {
        "id": 1,
        "restaurant_id": 1,
        "comment": "good.",
         "age": 28,
        "created_at": "2022-08-20T10:00:00.000Z",
        "updated_at": "2022-08-20T10:00:00.000Z"
    }
]

I would like to go further and say if the user wants to find the updated time of a comment for restaurant 1 like: http://localhost:3000/api/v1/restaurants/1?by_updated_at=2022-08-20T12:00:00.000Z AND there is no record with the exact updated_at time given:

How do I get the record that is closest to it? So that the record shown is then:

    {
        "id": 2,
        "restaurant_id": 1,
        "comment": "very good.",
         "age": 25,
        "created_at": "2022-08-20T12:30:00.000Z",
        "updated_at": "2022-08-20T12:30:00.000Z"
    }
]

Right now my code looks like: restaurants_controller.rb

  def show
    @restaurant = Restaurant.find(params[:id])
    @comments = apply_scopes(@restaurant.comments).all
    render json: @comments
  end

comment.rb (model) with

gem 'has_scope'

class Comment < ApplicationRecord
  belongs_to :restaurant
  scope :by_updated_at, ->updated_at { where(updated_at: updated_at)}

I looked at the first answer of this one: How to pass query params to Rails API controller? in combination with: find record closest to a given time in ruby on rails

and thought maybe I need to be updating the scope in the comment model to do something like this

  scope :by_updated_at, ->updated_at { where('updated_at>= ? AND updated_at <= ?', updated_at - 600, updated_at + 600).order('ABS(updated_at - updated_at)'), updated_at }

this is obviously wrong but now I am confused by the many updated_ats and not sure how to implement

note: there are many more records in this.

score 0 · Answer 1 · answered Aug 22 '22 at 08:11

First, it's pretty rare for an API to find "the closest record to X" rather than "all records between X and Y", and I'm not sure I can think of a use case that wouldn't be served better by a different method.

Additionally, if you're expressing durations in integers (like your +600), that's usually in seconds: so you're only looking for comments within ten minutes of the queried time, while your example is asking for half an hour. While this may be the actual reason the comment isn't showing, it's also a good demonstration of why hardcoding the threshold is probably a bad idea too.

However, I'll assume you have your reasons for doing this.

Most of it looks like it will work, except for the order clause: both those updated_ats inside the ABS function are referring to the column, because they're a literal string, so you're sending them to the database. I'm also guessing you want one of them to be a parameter? You've also left the value to send outside the order clause entirely.

One last point: scopes return multiple records.

I think you want this:

scope :by_updated_at, ->param { 
  where(
    'updated_at >= ? AND updated_at <= ?', param - 600, param + 600
  ).order(
    'ABS(updated_at - ?)', param
  )
}

Rails API with query string time stamp

1 Answers1