QueryHelper

QueryHelper is a ruby gem used to paginate, sort, and filter your API calls in Ruby on Rails using URL params in your HTTP requests. It currently only supports Postgres.

Installation

Add this line to your application's Gemfile:

gem 'query_helper'

And then execute:

$ bundle

Or install it yourself as:

$ gem install query_helper

Quick Use

Step 1: Update Base Controller to use the QueryHelper Concern

class ApplicationController < ActionController::API
  include QueryHelper::QueryHelperConcern
  before_action :create_query_helper
end

Adding this code creates a QueryHelper object preloaded with pagination, filtering, sorting, and association information included in the URL. This object can be accessed by using the @query_helper instance variable from within your controllers.

Step 2: Use QueryHelper to run active record and sql queries within your controller

Active Record Example

class ResourceController < ApplicationController

  def index
    @query_helper.update(
      model: UserNotificationSetting,
      query: "select * from resources r where r.user_id = :user_id",
      bind_variables: { user_id: current_user().id }
    )

    render json: @query_helper.results()
  end

end

Raw SQL Example

class ResourceController < ApplicationController

  def index
    @query_helper.query = Resource.all
    render json: @query_helper.results()
  end

end

You can also use the @query_helper.update() method to update the QueryHelper with an ActiveRecord object

@query_helper.update(
  query: Resource.all
)

Step 3: Paginate, Sort, Filter, and Include Associations using URL params

Pagination

page=1

per_page=20

http://www.example.com/resources?page=1&per_page=25

Sorting

sort=column:direction

Single Sort: http://www.example.com/resources?sort=resource_name:desc

Multiple Sorts: http://www.example.com/resources?sort=resource_name:desc,resource_age:asc

Lowercase Sort: http://www.example.com/resources?sort=resource_name:desc:lowercase

Filtering

filter[column][operator_code]=value

Single Filter: http://www.example.com/resources?filter[resource_age][gt]=50

Multiple Filters: http://www.example.com/resources?filter[resource_age][gt]=50&[resource_name][eql]=banana_resource

Operator Code	SQL Operator
gte	>=
lte	<=
gt	>
lt	<
eql	=
noteql	!=
like	like
in	in
notin	not in
null	is null or is not null

Note: For the null operator code, toggle is null operator with true and is not null operator with false

Associations

Include ActiveRecord associations in the payload. The association must be defined in the model.

include=association

Single Association: http://www.example.com/resources?include=child_resource

Multiple Associations: http://www.example.com/resources?include[]=child_resource&include[]=parent_resource

Payload Formats

The QueryHelper gem will return the following payload

Paginated List Payload

{
  "pagination": {
    "count": 18,
    "current_page": 1,
    "next_page": 2,
    "previous_page": null,
    "total_pages": 6,
    "per_page": 3,
    "first_page": true,
    "last_page": false,
    "out_of_range": false
  },
  "data": [
    {
      "id": 1,
      "attribute_1": "string_attribute",
      "attribute_2": 12345,
      "attribute_3": 0.3423212
    },
    {
      "id": 2,
      "attribute_1": "string_attribute",
      "attribute_2": 12345,
      "attribute_3": 0.3423212
    },
    {
      "id": 3,
      "attribute_1": "string_attribute",
      "attribute_2": 12345,
      "attribute_3": 0.3423212
    },
  ]
}

Advanced Options

Associations

You can preload additional and include additional associations in your payload besides what's defined in the include url parameter.

@query_helper.update(
  associations: ['association1']
)

as_json options

You can pass in additional as_json options to be included in the payload.

@query_helper.update(
  as_json_options: { methods: [:last_ran_at] }
)

Single Record Queries

If you only want to return a single result, but still want to be able to use some of the other functionality of QueryHelper, you can set single_record to true in the QueryHelper object.

@query_helper.single_record = true

or

@query_helper.update(
  single_record: true
)

Single Record Payload

{
  "data": {
    "id": 1,
    "attribute_1": "string_attribute",
    "attribute_2": 12345,
    "attribute_3": 0.3423212
  }
}

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/iserve_products/query_helper. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the Contributor Covenant code of conduct.

License

The gem is available as open source under the terms of the MIT License.

Code of Conduct

Everyone interacting in the QueryHelper project’s codebases, issue trackers, chat rooms and mailing lists is expected to follow the code of conduct.