README.md in wise_gopher-0.1.0 vs README.md in wise_gopher-0.2.0

- old
+ new

@@ -48,11 +48,11 @@ SELECT title, AVG(ratings.stars) AS average_rating, published_at, author_username FROM articles INNER JOIN ratings ON ratings.article_id = articles.id WHERE author_username = {{ username }} GROUP BY articles.id - HAVING average_rating > {{ mininum_rating }} + HAVING AVG(ratings.stars) > {{ mininum_rating }} ORDER BY averating_rating SQL param :minimum_rating, :integer param :username, :string, transform: :strip @@ -73,18 +73,18 @@ Which you would use this way: ```ruby result = PopularArticle.execute_with(minimum_rating: 3, username: "PageHey ") # => [#<PopularArticle::Row:0x0000560c37e9de48 @title="My first gem is out!", @average_rating=3.5 ...>, ...] puts result.first -# => Article 'My first gem is out!' by PageHey is rated 3.5/5. +# => Article 'My first gem is out!' by PageHey is rated 3.50/5. result.first.class # => PopularArticle::Row ``` ------ -So, basically what you need to do is make your class inherits from `WiseGopher::Base` and provide your SQL with `.query`. You can then declare what columns will be present in result with `.column` in a block given to `row`. +So, basically what you need to do is make your class inherits from `WiseGopher::Base` and provide your SQL with `query`. You can then declare what columns will be present in result with `column` in a block given to `row`. If your query doesn't need any parameter like this one: ```ruby class PopularArticle < WiseGopher::Base @@ -93,11 +93,11 @@ row do column :title, :string end end ``` -You can simply get result with `.execute`: +You can simply get result with `execute`: ```ruby PopularArticle.execute ``` If your query **does need** parameter like this one: @@ -114,60 +114,148 @@ row do column :title, :string end end ``` -You should declare the params with `.param` so you can pass the parameters as a hash to `.execute_with`: +You should declare the params with `param` so you can pass the parameters as a hash to `execute_with`: ```ruby PopularArticle.execute_with(author_name: "PageHey", published_after: Date.today - 1.month) ``` -If any parameter is missing or if you call `.execute` for a class that needs some, it will raise `WiseGopher::ArgumentError`. +If any parameter is missing or if you call `execute` for a class that needs some, it will raise `WiseGopher::ArgumentError`. Before query execution, the placeholders will be replaced with the standard `?` placeholder or with the `$1`, `$2` ... numbered placeholders for PostgreSQL database. -To declare the column in result, you should use `.row` and pass it a block. Calling this method will create a `Row` class nested into your query class. The block will be then executed in `Row` class context. In this context you can use `.column` but also define method, include module, basicaly write any code you would find in a class delacration. +To declare the column in result, you should use `row` and pass it a block. Calling this method will create a `Row` class nested into your query class. The block will be then executed in `Row` class context. In this context you can use `column` but also define method, include module, basicaly write any code you would find in a class delacration. -The goal of this syntax is to gather in the same file the input and output logic of the query while keeping dedicated classes for each logic. -You can provide a custom class to `.row` if you prefer. If you still pass the block to the method, the `WiseGopher::Row` module will be included in the class before evaluating it, so you can have this syntax: +The goal of this syntax is to gather in the same file the inputs and outputs of the query while keeping dedicated classes for each subject. +You can provide a custom class to `row` if you prefer. If you still pass the block to the method, the `WiseGopher::Row` module will be included in the class before evaluating it, so you can have this syntax: ```ruby -_/my_custom_row.rb_ +# /my_custom_row.rb class MyCustomRow def some_custom_method # [...] end end -_/my_query_class.rb +# /my_query_class.rb class MyQueryClass < WiseGopher::Base query "SELECT title FROM articles" row MyCustomRow do column :title, :string end end ``` -**If you don't give any block to `.row`, make sure you include `WiseGopher::Row` in your class.** +**If you don't give any block to `row`, make sure you include `WiseGopher::Row` in your class.** +------ +## Raw params +If you need to dinamically interpolate raw SQL in your query, you can use `raw_param`. The value passed with `execute_with` will be interpolated in the base query before inserting the other params. +```ruby +class AnnualReport < WiseGopher::Base + query <<-SQL + SELECT month, revenue + FROM heavy_computations + WHERE employee_id = {{ id }} + {{ order_by }} + SQL + + param :id, :integer + + raw_param :order_by +end +AnnualReport.execute_with(id: 1, order_by: "ORDER BY id ASC") +``` +executed query will look like this: +```SQL +SELECT month, revenue +FROM heavy_computations +WHERE employee_id = ? +ORDER BY id ASC +``` +By default, `raw_param` is required but you can pass `optional: true`. You can then omit the param and the placeholder will be remove for this query instance. +```ruby +AnnualReport.execute_with(id: 1, order_by: "ORDER BY id ASC") +``` +```SQL +SELECT month, revenue +FROM heavy_computations +WHERE employee_id = ? +``` + +You can also provide _prefix_ and/or _suffix_ to `raw_param` to make the `raw_param` clearer and the argument lighter. +```ruby +class AnnualReport < WiseGopher::Base + query <<-SQL + SELECT month, revenue + FROM heavy_computations + WHERE employee_id = {{ id }} + {{ order_by }} + SQL + + param :id, :integer + + raw_param :order_by, prefix: "ORDER BY ", suffix: " ASC" # note the spacings +end +AnnualReport.execute_with(id: 1, order_by: "id") +``` +Finally, a default option is also supported, thus making the param optional: +```ruby +class AnnualReport < WiseGopher::Base + query <<-SQL + SELECT month, revenue + FROM heavy_computations + WHERE employee_id = {{ id }} + {{ order_by }} + SQL + + param :id, :integer + + raw_param :order_by, prefix: " ORDER BY ", suffix: " ASC ", default: "id" +end +AnnualReport.execute_with(id: 1) +``` +executed query: +```SQL +SELECT month, revenue +FROM heavy_computations +WHERE employee_id = ? +ORDER BY id ASC +``` + ------ ## Methods documentation ### WiseGopher::Base (class) -#### .param +#### ::param ```ruby param(name, type, transform: nil) ``` Argument | Required | Descrition ------------ | ------------- | ------------- name | true | The name of the parameter as written in the `{{ placeholder }}` type | true | The type of the column. It can be any type registred as ActiveRecord::Type. Including yours -transform: | false | `Proc` or `Symbol`. An operation that will be call before creating the bind parameter when you call `.execute_with`. +transform: | false | `Proc` or `Symbol`. An operation that will be call before creating the bind parameter when you call `execute_with`. +#### ::raw_param +```ruby +raw_param(name, prefix: nil, suffix: nil, default: nil, optional: false) +``` + +Argument | Required | Descrition +------------ | ------------- | ------------- +name | true | The name of the parameter as written in the `{{ placeholder }}` +prefix: | false | The string to be inserted **before** the value passed as argument. No spaces will be added around to allow maximum customization. +suffix: | false | The string to be inserted **after** the value passed as argument. No spaces will be added around to allow maximum customization. +default: | false | The default value used if none is passed when calling the query +optional: | false | an empty string will be inserted in place of the placeholder if neither argument or default is provided. + ### WiseGopher::Row (module) -#### .column +#### ::column ```ruby column(name, type, transform: nil, as: nil) ``` Argument | Required | Descrition @@ -178,14 +266,14 @@ as: | false | The name of the getter you want on the row instance for this column (getter with original name won't be created!) ------ ## Tips #### transform: argument as proc -If you provide a proc to the `transform:` argument (either on `.column` or `.param`), you can expect one argument or none. If one argument is expected the value of the param or column will be passed. +If you provide a proc to the `transform:` argument (either on `column` or `param`), you can expect one argument or none. If one argument is expected the value of the param or column will be passed. #### Prepare query for later execution -You can prepare the query with param without executing it by simply calling `.new` on your class and providing the params an later call `.execute`. +You can prepare the query with param without executing it by simply calling `new` on your class and providing the params an later call `execute`. ```ruby class PopularArticle < WiseGopher::Base query <<-SQL SELECT title FROM articles WHERE published_at > {{ published_after }} @@ -201,11 +289,11 @@ # [...] last_month_articles.execute # => [#<PopularArticle::Row:0x0000560c37e9de48 ...>] ``` #### Ignore column in result -If for some reason, you have a column in your result that you don't want to retrieve on the row instances, you can use `.ignore`. +If for some reason, you have a column in your result that you don't want to retrieve on the row instances, you can use `ignore`. ```ruby class MyQuery < WiseGopher::Base query "SELECT title, rating FROM articles" row do @@ -213,9 +301,48 @@ ignore :rating end end MyQuery.execute # => no error raised +``` + +#### Array of value as parameter +You can pass an array as parameter value. The will then make a comma separated list of placeholders and pass the arguments as many bind parameters. +```ruby +class MyQuery < WiseGopher::Base + query "SELECT title FROM articles WHERE rating in ({{ ratings }})" + + param :ratings, :integer + + row do + column :title, :string + end +end + +MyQuery.execute_with(ratings: [1, 2]) +# query will be "SELECT title FROM articles WHERE rating in (?, ?)" +``` + +#### Classic param in raw_param +As the raw_params are interpolated before the classic params, you can have placeholders in them: +```ruby +class MyQuery < WiseGopher::Base + query <<-SQL + SELECT title FROM articles + WHERE rating > ({{ ratings }})" + SQL + + param :min_rating, :integer + + raw_param :or_condition, prefix: " OR " + + row do + column :title, :string + end +end + +MyQuery.execute_with(min_rating: 1, or_condition: "rating = {{ min_rating }}") +# query will be "SELECT title FROM articles WHERE rating > ? OR rating = ?" ``` ------ ## Contributing