PatternQueryHelper

Ruby Gem developed and used at Pattern to paginate, sort, filter, and include associations on sql and active record queries.

Add this line to your application's Gemfile:

gem 'pattern_query_helper'

And then execute:

$ bundle

Or install it yourself as:

$ gem install pattern_query_helper

To run an active record query execute

PatternQueryHelper.run_active_record_query(active_record_call, query_helpers, valid_columns, single_record)

active_record_call: Valid active record syntax (i.e. Object.where(state: 'Active')) query_helpers: See docs below valid_columns: Default is []. Pass in an array of columns you want to allow sorting and filtering on. single_record: Default is false. Pass in true to format payload as a single object instead of a list of objects

To run a custom sql query execute

PatternQueryHelper.run_sql_query(model, query, query_params, query_helpers, valid_columns, single_record)

model: A valid ActiveRecord model query: A string containing your custom SQL query query_params: a symbolized hash of binds to be included in your SQL query query_helpers: See docs below valid_columns: Default is []. Pass in an array of columns you want to allow sorting and filtering on. single_record: Default is false. Pass in true to format payload as a single object instead of a list of objects

query_helpers is a symbolized hash passed in with information about pagination, associations, filtering and sorting.

There are two pagination keys you can pass in as part of the query_helpers objects

{
  page: 1,
  per_page: 20
}

If at least one of these keys is present, paginated results will be returned.

Sorting is controlled by the sort key in the query_helpers object

{
    sort: "column_name:sort_direction"
}

Sort direction can be either asc or desc. If you wish to lowercase string before sorting include the following:

{
    sort: "name:desc:lowercase"
}

Filtering is controlled by the filter object in the query_helpers hash

{
  filter: {
    "column_1" => {
      "gte" => 20,
      "lt" => 40
    },
    "column_2" => {
      "eql" => "my_string"
    },
    "column_3" => {
      "like" => "my_string%"
    },
    "column_4" => {
      "in" => "item1,item2,item3"
    }
}

The following operator codes are valid

“gte”: >=
“lte”: <=
“gt”: >
“lt”: <
“eql”: =
“noteql”: !=
"like": like
“in”: in
“notin” not in
“null”: “is null” or “is not null” (pass in true or false as the value)

To include associated objects in the payload, pass in the following as part of the query_helpers hash:

{
  include: ['associated_object_1', 'associated_object_2']
}

The following is an example of a query_helpers object that can be passed into the sql and active record methods

query_helpers = {
  page: 1,
  per_page: 20,
  sort: "name:desc"
  include: ["child"]
  filter: {
    "id" => {
      "gte" => 20,
      "lt" => 40
    }
}

The PatternQueryHelper gem will return results in one of three formats

{
  "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
    },
  ]
}

{
  "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
    },
  ]
}

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

Bug reports and pull requests are welcome on GitHub at https://github.com/[USERNAME]/pattern_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.

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

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