For my application I have some data display requirements that will necessitate querying some statistics about products. In this particular case I want to know how "popular" a product is in comparison to all other products. One way I can do this is to determine some usage quartiles, that is, the three values that split the data into four equal parts. I can then compare the usage count for a single product to see which quartile it fits into, giving them a ranking name from "none", "lowest", "low", "medium" to "high".
Here are some examples of the user interface to represent this:
Postgres has a function that can calculate these numbers for me called PERCENTILE_CONT. An example of what this looks like is as follows:
SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY catalogue_count) AS low,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY catalogue_count) AS medium
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY catalogue_count) AS high
FROM
products
Turning this into a query in Ruby therefore requires using custom SQL, so I could add something like this to my Product class:
app/models/product.rb
class Product < ApplicationRecord
...
def catalogue_usage_quartiles
select(
'PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY catalogue_count) AS low,' \
'PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY catalogue_count) AS medium,' \
'PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY catalogue_count) AS high'
)
.take
end
...
end
And then I could use those quartiles to generate my named rankings, something like the following:
app/models/product.rb
class Product < ApplicationRecord
...
def catalogue_usage_ranking
case catalogue_count
when 0
'none'
when 1..catalogue_usage_quartiles.low
'lowest'
when (catalogue_usage_quartiles.low + 1)..catalogue_usage_quartiles.medium
'low'
when (catalogue_usage_quartiles.medium + 1)..catalogue_usage_quartiles.high
'medium'
else
'high'
end
end
...
end
One downside to this approach is that now there is hard coded SQL in my Product model that isn't really related to a Product's responsibilities. Determining the quartiles should be the responsibility of it's own class. One way to do this (and the subject of this blog) is the use of Query Objects. The folks over at Thoughtbot have an excellent article on Query Objects here. I encourage anyone to have a read of that.
So how can I use a Query Object here? Here is what it might look like:
app/models/queries/catalogue_usage_quartiles.rb
module Queries
class CatalogueUsageQuartiles
class << self
def call(scope: nil, options: {})
new(scope: scope).call(options: options)
end
end
def initialize(scope: nil)
super(scope: scope || Product.all)
end
def call(*)
@scope
.select(
"PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY #{sum_of_catalogues}) AS low," \
"PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY #{sum_of_catalogues}) AS medium," \
"PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY #{sum_of_catalogues}) AS high"
)
.find_by(
"(#{sum_of_catalogues}) > 0"
)
end
def sum_of_catalogues
'catalogue_count + recipes_count + inventory_stock_levels_count + inventory_derived_period_balances_count'
end
end
end
What is this doing then?
First of all there is a class method with a name of call. I can pass in a custom scope to this, maybe I want only the products that have certain criteria met. I could potentially be passing in other options for my query too. I do not need to in this example however. The class method creates a new instance of my query object and executes the call method on it.
The instance call method is where the work is done. All my custom query logic lives here, and it should return either an instance of a record or another ActiveRecord relation so that these queries can be chained.
I can now remove the quartile calculations from the Product class and do not even need to change my catalogue_usage_ranking method.
app/models/product.rb
class Product < ApplicationRecord
...
def catalogue_usage_ranking
case catalogue_count
when 0
'none'
when 1..catalogue_usage_quartiles.low
'lowest'
when (catalogue_usage_quartiles.low + 1)..catalogue_usage_quartiles.medium
'low'
when (catalogue_usage_quartiles.medium + 1)..catalogue_usage_quartiles.high
'medium'
else
'high'
end
end
private
def catalogue_usage_quartiles
@catalogue_quartiles ||= Queries::CatalogueUsageQuartiles.call
end
...
end
So what have I gained? Well now I can hide away the complexity of these quartile calculations into a class that has executing this as its only responsibility. The Product class is simplified as it has less code in it. I can also further DRY up these rankings by converting my catalogue_usage_ranking method into a usage_ranking method that takes the quartiles to use and a count so I can easily expand this to do my transaction and settings usage rankings I need. Also, if there is a need to calculate these quartile values anywhere else in my application (maybe I want to display them somewhere?) then I do not need to get the Product class involved at all.