README.md in query_helper-0.0.0 vs README.md in query_helper-0.1.0
- old
+ new
@@ -1,10 +1,10 @@
# QueryHelper
[](https://travis-ci.org/iserve-products/query_helper)
[](https://badge.fury.io/rb/query_helper)
-Ruby Gem developed and used at Pattern to paginate, sort, filter, and include associations on sql and active record queries.
+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:
@@ -18,282 +18,122 @@
Or install it yourself as:
$ gem install query_helper
-## Use
+## Quick Use
-### SQL Queries
+### Step 1: Update Base Controller to use the QueryHelper Concern
-#### Initialize
+```ruby
+class ApplicationController < ActionController::API
+ include QueryHelper::QueryHelperConcern
+ before_action :create_query_helper
+end
+```
-To create a new sql query object run
+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
+
```ruby
-QueryHelper::Sql.new(
- model:, # required
- query:, # required
- query_params: , # optional
- column_mappings: , # optional
- filters: , # optional
- sorts: , # optional
- page: , # optional
- per_page: , # optional
- single_record: , # optional, default: false
- associations: , # optional
- as_json_options: , # optional
- run: # optional, default: true
-)
+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
```
-The following arguments are accepted when creating a new objects
+#### Raw SQL Example
-<table>
-<tr>
-<th>Argument</th>
-<th>Description</th>
-<th>Example</th>
-</tr>
-<tr>
-<td>model</td>
-<td>the model to run the query against</td>
-<td>
-<pre lang="ruby">
-Parent
-</pre>
-</td>
-</tr>
-<tr>
-<td>query</td>
-<td>the custom sql string to be executed</td>
-<td>
-<pre lang="ruby">
-'select * from parents'
-</pre>
-</td>
-</tr>
-<tr>
-<td>query_params</td>
-<td>a hash of bind variables to be embedded into the sql query</td>
-<td>
-<pre lang="ruby">
-{
- age: 20,
- name: 'John'
-}
-</pre>
-</td>
-</tr>
-<tr>
-<td>column_mappings</td>
-<td>A hash that translates aliases to sql expressions</td>
-<td>
-<pre lang="ruby">
-{
- "age" => "parents.age"
- "children_count" => {
- sql_expression: "count(children.id)",
- aggregate: true
- }
-}
-</pre>
-</td>
-</tr>
-<tr>
-<td>filters</td>
-<td>a list of filters in the form of `{"comparate_alias"=>{"operator_code"=>"value"}}`</td>
-<td>
-<pre lang="ruby">
-{
- "age" => { "lt" => 100 },
- "children_count" => { "gt" => 0 }
-}
-</pre>
-</td>
-</tr>
-<tr>
-<td>sorts</td>
-<td>a comma separated string with a list of sort values</td>
-<td>
-<pre lang="ruby">
-"age:desc,name:asc:lowercase"
-</pre>
-</td>
-</tr>
-<tr>
-<td>page</td>
-<td>the page you want returned</td>
-<td>
-<pre lang="ruby">
-5
-</pre>
-</td>
-</tr>
-<tr>
-<td>per_page</td>
-<td>the number of results per page</td>
-<td>
-<pre lang="ruby">
-20
-</pre>
-</td>
-</tr>
-<tr>
-<td>single_record</td>
-<td>whether or not you expect the record to return a single result, if toggled, only the first result will be returned</td>
-<td>
-<pre lang="ruby">
-false
-</pre>
-</td>
-</tr>
-<tr>
-<td>associations</td>
-<td>a list of activerecord associations you'd like included in the payload </td>
-<td>
-<pre lang="ruby">
+```ruby
+class ResourceController < ApplicationController
-</pre>
-</td>
-</tr>
-<tr>
-<td>as_json_options</td>
-<td>a list of as_json options you'd like run before returning the payload</td>
-<td>
-<pre lang="ruby">
+ def index
+ @query_helper.query = Resource.all
+ render json: @query_helper.results()
+ end
-</pre>
-</td>
-</tr>
-<tr>
-<td>run</td>
-<td>whether or not you'd like to run the query on initilization</td>
-<td>
-<pre lang="ruby">
-false
-</pre>
-</td>
-</tr>
-</table>
+end
+```
-### Active Record Queries
+You can also use the `@query_helper.update()` method to update the QueryHelper with an ActiveRecord object
-To run an active record query execute
```ruby
-QueryHelper.run_active_record_query(active_record_call, query_helpers, valid_columns, single_record)
+@query_helper.update(
+ query: Resource.all
+)
```
-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
+### Step 3: Paginate, Sort, Filter, and Include Associations using URL params
-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
+#### Pagination
-## Query Helpers
-query_helpers is a symbolized hash passed in with information about pagination, associations, filtering and sorting.
+`page=1`
-### Pagination
-There are two pagination keys you can pass in as part of the query_helpers objects
+`per_page=20`
-```ruby
-{
- page: 1,
- per_page: 20
-}
-```
+`http://www.example.com/resources?page=1&per_page=25`
-If at least one of these keys is present, paginated results will be returned.
+#### Sorting
-### Sorting
-Sorting is controlled by the `sort` key in the query_helpers object
+`sort=column:direction`
-```ruby
-{
- sort: "column_name:sort_direction"
-}
-```
-Sort direction can be either asc or desc. If you wish to lowercase string before sorting include the following:
-```ruby
-{
- sort: "name:desc:lowercase"
-}
-```
+Single Sort: `http://www.example.com/resources?sort=resource_name:desc`
-### Filtering
-Filtering is controlled by the `filter` object in the query_helpers hash
+Multiple Sorts: `http://www.example.com/resources?sort=resource_name:desc,resource_age:asc`
-```ruby
-{
- filter: {
- "column_1" => {
- "gte" => 20,
- "lt" => 40
- },
- "column_2" => {
- "eql" => "my_string"
- },
- "column_3" => {
- "like" => "my_string%"
- },
- "column_4" => {
- "in" => "item1,item2,item3"
- }
-}
-```
+Lowercase Sort: `http://www.example.com/resources?sort=resource_name:desc:lowercase`
-The following operator codes are valid
+#### Filtering
-```
-“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)
-```
+`filter[column][operator_code]=value`
-### Associations
+Single Filter: `http://www.example.com/resources?filter[resource_age][gt]=50`
-To include associated objects in the payload, pass in the following as part of the query_helpers hash:
+Multiple Filters: `http://www.example.com/resources?filter[resource_age][gt]=50&[resource_name][eql]=banana_resource`
-```ruby
-{
- include: ['associated_object_1', 'associated_object_2']
-}
-```
+Operator Code | SQL Operator
+--- | ---
+gte | >=
+lte | <=
+gt | >
+lt | <
+eql | =
+noteql | !=
+like | like
+in | in
+notin | not in
+null | is null *or* is not null
-### Example
+Note: For the null operator code, toggle *is null* operator with true and *is not null* operator with false
-The following is an example of a query_helpers object that can be passed into the sql and active record methods
+#### Associations
-```ruby
-query_helpers = {
- page: 1,
- per_page: 20,
- sort: "name:desc"
- include: ["child"]
- filter: {
- "id" => {
- "gte" => 20,
- "lt" => 40
- }
-}
-```
+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 results in one of three formats
+The QueryHelper gem will return the following payload
### Paginated List Payload
```json
{
"pagination": {
@@ -328,36 +168,45 @@
},
]
}
```
-### List Payload
-```json
-{
- "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.
+
+```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] }
+)
+```
+
+### 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,
@@ -368,10 +217,10 @@
}
```
## Contributing
-Bug reports and pull requests are welcome on GitHub at https://github.com/[USERNAME]/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.
+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).