# QueryHelper [![TravisCI](https://travis-ci.org/iserve-products/query_helper.svg?branch=master)](https://travis-ci.org/iserve-products/query_helper) [![Gem Version](https://badge.fury.io/rb/query_helper.svg)](https://badge.fury.io/rb/query_helper) 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: ```ruby 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 ```ruby 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 #### Raw SQL Example ```ruby 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 ``` #### ActiveRecord Example ```ruby class ResourceController < ApplicationController def index @query_helper.update(query: Resource.all) render json: @query_helper.results() end end ``` *NOTE: Previous documentation stated you could simply run `@query_helper.query = Resource.all`. While this method still works, it will evaluate the ActiveRecord query causing it to hit the database twice. It is recommended that you always use the update method to avoid this inefficiency* ### 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 ```json { "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 }, ] } ``` ## Using in Models or Services If your complex queries are defined in a model or service, you can still use QueryHelper to automatically paginate, filter, and sort api calls that reference the given model/service. ### Example #### Model ```ruby class MyModel < ApplicationRecord def complex_sql_function(query_helper=QueryHelper.new) query = "select * from resource" query_helper.update( model: Resource, query: query, ) end end ``` When calling this model from outside a controller, you will get the full result set without the api wrapping. (i.e. `MyModel.first.complex_sql_function` will return an array. #### Controller ```ruby class MyModelsController < ApplicationController def get_complex_query @object = MyModel.find(params[:id]) response = @object.complex_sql_function(@query_helper) render json: response end end ``` When you pass in the `@query_helper` object from the controller, QueryHelper will paginate, sort, and filter as expected. ## Advanced Options ### Associations You can preload additional and include additional associations in your payload besides what's defined in the `include` url parameter. ```ruby @query_helper.update( associations: ['association1'] ) ``` ### as_json options You can pass in additional as_json options to be included in the payload. ```ruby @query_helper.update( as_json_options: { methods: [:last_ran_at] } ) ``` ### Preload This is handy if you are loading other associations or methods with the as_json config and need to preload associations to avoid n+1 queries. ```ruby @query_helper.update( preload: [:association1, :association2] ) ``` or ```ruby @query_helper.update( preload: [association: [:child_association]] ) ``` ### Custom Sort and Filter mappings QueryHelper will automatically determine which sql aliases to run filtering and sorting on. In cases where this doesn't work, you can provide your own custom mappings so QueryHelper knows how to correctly sort and filter. One common example of this is when you run a `select * from resource1` but pass `resource2` in as the model. ```ruby @query_helper.update( custom_mappings: { "alias" => "complex_sql_function" } ) ``` To indicate that a custom mapping refers to an aggregate function use the following: ```ruby @query_helper.update( custom_mappings: { "alias" => { sql_expression: "MAX(resouce.age)", aggregate: true } } ) ``` ### 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. ```ruby @query_helper.single_record = true ``` or ```ruby @query_helper.update( single_record: true ) ``` ### Single Record Payload ```json { "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](http://contributor-covenant.org) code of conduct. ## License The gem is available as open source under the terms of the [MIT License](https://opensource.org/licenses/MIT). ## 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](https://github.com/[USERNAME]/query_helper/blob/master/CODE_OF_CONDUCT.md).