Scoops of scopes
Hegedüs Zoltán
Ordinary scopes
class Foo < ActiveRecord::Base
scope :published, -> { where(published: true) }
scope :published_and_commented, -> { published.where("comments_count > 0") }
end
irb(main):005:0> Foo.published
=> #<ActiveRecord::Relation [#<Foo id: 1>, #<Foo id: 5>]>
irb(main):006:0> Foo.published.joins(bar: :baz).where(id: [1,9])
=> #<ActiveRecord::Relation [#<Foo id: 1>]>
Ordinary scopes cont'd
class Foo < ActiveRecord::Base
scope :published, -> { where(published: true) }
scope :published_and_commented, -> { published.where("comments_count > 0") }
scope :wanted, -> (good_ids = nil) {
join_clause = <<-JOINS
LEFT JOIN (SELECT
sessions.id AS session_id,
articles.person_id AS person_id,
COUNT(articles.id) AS session_activity_count
FROM sessions
INNER JOIN session_inclusions
ON session_inclusions.session_id = sessions.id) tmp1 ON
tmp1.session_id = foos.session_id
JOINS
relation = published
.joins(join_clause)
if good_ids
relation.where(id: good_ids)
else
relation
end
}
end
irb(main):005:0> Foo.wanted([1,9])
=> #<ActiveRecord::Relation [#<Foo id: 1>]>
Ordinary scopes cont'd
SELECT
people.*,
routing_paths.title AS routing_path_title,
teams.title AS team_title
FROM (SELECT
people.id,
people.name,
people.team_id,
scv.routing_path_id,
start_date,
end_date,
session_description,
session_value * session_length_ratio AS session_value,
session_per_capita_value * session_length_ratio AS session_per_capita_value,
COALESCE(session_activity_count, 0) AS session_activity_count,
COALESCE(session_activity_value, 0) AS session_activity_value,
0 AS out_of_contract_activity_value,
0 AS out_of_contract_activity_count
FROM "people"
INNER JOIN "teams" ON "teams"."id" = "people"."team_id"
JOIN (SELECT
sessions.id AS session_id,
sessions.description AS session_description,
sessions.start_date,
sessions.end_date,
sessions.routing_path_id,
(UPPER(daterange(sessions.start_date, sessions.end_date) * '[2019-01-01, 2019-12-31)' :: DATERANGE)
- LOWER(daterange(sessions.start_date, sessions.end_date) *
'[2019-01-01, 2019-12-31)' :: DATERANGE)) :: FLOAT / (364) AS session_length_ratio,
sessions.value_amount / 100 AS session_value,
session_inclusions.subject_id AS person_id,
session_inclusions.weight AS session_per_capita_value
FROM "sessions"
INNER JOIN "session_inclusions" ON "session_inclusions"."session_id" = "sessions"."id" AND
"session_inclusions"."deleted_at" IS NULL
WHERE "sessions"."deleted_at" IS NULL AND "sessions"."id" IN (SELECT "sessions"."id"
FROM "sessions"
WHERE "sessions"."deleted_at" IS NULL AND "sessions"."investor_id" = 851 AND (
(sessions.start_date >= '2019-01-01' AND
'2019-12-31' BETWEEN sessions.start_date AND sessions.end_date)
OR (sessions.end_date <= '2019-12-31' AND
'2019-01-01' BETWEEN sessions.start_date AND sessions.end_date)
OR (sessions.start_date > '2019-01-01' AND sessions.end_date < '2019-12-31')
OR (sessions.start_date < '2019-01-01' AND sessions.end_date > '2019-12-31')
)) AND "session_inclusions"."subject_type" = 'Person') AS scv ON scv.person_id = people.id
LEFT JOIN (SELECT
sessions.id AS session_id,
articles.person_id AS person_id,
sessions.routing_path_id,
SUM(articles.value_amount) / 100 AS session_activity_value,
COUNT(articles.id) AS session_activity_count
FROM "sessions"
INNER JOIN "session_inclusions" ON "session_inclusions"."session_id" = "sessions"."id" AND
"session_inclusions"."deleted_at" IS NULL
JOIN articles
ON session_inclusions.subject_id = articles.person_id AND
articles.status IN ('granted', 'not_attend')
JOIN activities
ON articles.activity_id = activities.id
AND activities.routing_path_id = sessions.routing_path_id
AND activities.deleted_at IS NULL
AND activities.aggregate_status = 'consumed'
AND activities.start_date BETWEEN sessions.start_date AND sessions.end_date
AND activities.classification_id IN (SELECT classification_id
FROM session_classifications
WHERE session_classifications.session_id = sessions.id
AND session_classifications.deleted_at IS NULL)
WHERE "sessions"."deleted_at" IS NULL AND "sessions"."id" IN (SELECT "sessions"."id"
FROM "sessions"
WHERE "sessions"."deleted_at" IS NULL AND "sessions"."investor_id" = 851 AND (
(sessions.start_date >= '2019-01-01' AND
'2019-12-31' BETWEEN sessions.start_date AND sessions.end_date)
OR (sessions.end_date <= '2019-12-31' AND
'2019-01-01' BETWEEN sessions.start_date AND sessions.end_date)
OR (sessions.start_date > '2019-01-01' AND sessions.end_date < '2019-12-31')
OR (sessions.start_date < '2019-01-01' AND sessions.end_date > '2019-12-31')
)) AND "session_inclusions"."subject_type" = 'Person'
LEFT JOIN (SELECT
SUM(session_activity_value) AS session_activity_value,
SUM(session_activity_count) AS session_activity_count,
scv.routing_path_id,
people.id AS person_id
FROM "people"
INNER JOIN "teams" ON "teams"."id" = "people"."team_id"
JOIN (SELECT
sessions.id AS session_id,
sessions.description AS session_description,
sessions.start_date,
sessions.end_date,
sessions.routing_path_id,
(UPPER(
daterange(sessions.start_date, sessions.end_date) * '[2019-01-01, 2019-12-31)' :: DATERANGE) -
LOWER(daterange(sessions.start_date, sessions.end_date) *
'[2019-01-01, 2019-12-31)' :: DATERANGE)) :: FLOAT / (364) AS session_length_ratio,
sessions.value_amount / 100 AS session_value,
session_inclusions.subject_id AS person_id,
session_inclusions.weight AS session_per_capita_value
FROM "sessions"
INNER JOIN "session_inclusions"
ON "session_inclusions"."session_id" = "sessions"."id" AND
"session_inclusions"."deleted_at" IS NULL
WHERE "sessions"."deleted_at" IS NULL AND "sessions"."id" IN (SELECT "sessions"."id"
FROM "sessions"
WHERE "sessions"."deleted_at" IS NULL AND "sessions"."investor_id" = 851 AND (
(sessions.start_date >= '2019-01-01' AND
'2019-12-31' BETWEEN sessions.start_date AND sessions.end_date)
OR (sessions.end_date <= '2019-12-31' AND
'2019-01-01' BETWEEN sessions.start_date AND sessions.end_date)
OR (sessions.start_date > '2019-01-01' AND sessions.end_date < '2019-12-31')
OR (sessions.start_date < '2019-01-01' AND sessions.end_date > '2019-12-31')
)) AND "session_inclusions"."subject_type" = 'Person') AS scv ON scv.person_id = people.id
LEFT JOIN (SELECT
sessions.id AS session_id,
articles.person_id AS person_id,
sessions.routing_path_id,
SUM(articles.value_amount) / 100 AS session_activity_value,
COUNT(articles.id) AS session_activity_count
FROM "sessions"
INNER JOIN "session_inclusions"
ON "session_inclusions"."session_id" = "sessions"."id" AND
"session_inclusions"."deleted_at" IS NULL
JOIN articles
ON session_inclusions.subject_id = articles.person_id AND
articles.status IN ('granted', 'not_attend')
JOIN activities
ON articles.activity_id = activities.id
AND activities.routing_path_id = sessions.routing_path_id
AND activities.deleted_at IS NULL
AND activities.aggregate_status = 'consumed'
AND activities.start_date BETWEEN sessions.start_date AND sessions.end_date
AND activities.classification_id IN (SELECT classification_id
FROM session_classifications
WHERE session_classifications.session_id = sessions.id
AND session_classifications.deleted_at IS NULL)
WHERE "sessions"."deleted_at" IS NULL AND "sessions"."id" IN (SELECT "sessions"."id"
FROM "sessions"
WHERE "sessions"."deleted_at" IS NULL AND "sessions"."investor_id" = 851 AND (
(sessions.start_date >= '2019-01-01' AND
'2019-12-31' BETWEEN sessions.start_date AND sessions.end_date)
OR (sessions.end_date <= '2019-12-31' AND
'2019-01-01' BETWEEN sessions.start_date AND sessions.end_date)
OR (sessions.start_date > '2019-01-01' AND sessions.end_date < '2019-12-31')
OR (sessions.start_date < '2019-01-01' AND sessions.end_date > '2019-12-31')
)) AND "session_inclusions"."subject_type" = 'Person'
GROUP BY sessions.id, articles.person_id) AS sav
ON sav.person_id = people.id AND sav.session_id = scv.session_id
WHERE "people"."id" IN (SELECT "people"."id"
FROM "people"
INNER JOIN "teams" ON "teams"."id" = "people"."team_id"
INNER JOIN "offices" ON "offices"."id" = "people"."office_id"
WHERE "offices"."firm_id" = 851)
GROUP BY people.id, scv.routing_path_id) AS sessions
ON sessions.person_id = people.id AND sessions.routing_path_id = rpav.routing_path_id
Enter Query objects
class Query
class << self
def create(**params)
new(**params).tap(&:save)
end
end
def save
@relation = @scopes.inject(base_relation) { |relation, scope| scope.call(relation) }
end
def results
@relation
end
end
class Query::WantedFoos < Query
def initialize(ids: nil)
@scopes = [bars_scope]
@scopes << id_scope(ids) if ids
end
private
def base_relation
Foo.all
end
def bars_scope
join_clause = <<-JOINS
LEFT JOIN bars ON bars.id < 5
JOINS
->(relation) { relation.joins(join_clause)}
end
def id_scope(ids)
->(relation) { relation.where(id: ids)}
end
end
Scope objects
class BarScope
def call(relation)
join_clause = <<-JOINS
LEFT JOIN bars ON bars.id < 5
JOINS
relation.joins(join_clause)
end
end
class IdScope
def initialize(ids)
@ids = ids
end
def call(relation)
relation.where(id: @ids)
end
end
class Query::WantedFoos < Query
def initialize(ids: nil)
@scopes = [bars]
@scopes << id_scope(ids) if ids
end
private
def base_relation
Foo.all
end
def bars
BarScope.new
end
def id_scope(ids)
IdScope.new(ids)
end
end
Scope objects cont'd
class BarScope
def call(relation)
join_clause = <<-JOINS
LEFT JOIN bars ON bars.id < 5
JOINS
relation.joins(join_clause)
end
end
class IdScope
def initialize(ids)
@ids = ids
end
def call(relation)
relation.where(id: @ids)
end
end
class Query::WantedFoos < Query
def initialize(ids: nil)
@scopes = [bars]
@scopes << id_scope(ids) if ids
end
private
def base_relation
Foo.all
end
def bars
BarScope.new
end
def id_scope(ids)
IdScope.new(ids)
end
end
require 'rails_helper'
describe IdScope do
let(:scope) { described_class.new(params) }
describe "#call" do
subject { scope.call(relation) }
let(:relation) { Foo.all }
context "with existing id" do
let(:params) { [Foo.first.id] }
it "should return that foo" do
subject.should include(Foo.first)
end
end
end
end
class Query::InterestingBar < Query
def initialize(ids: nil)
@scopes = [id_scope]
end
private
def base_relation
Bar.all
end
def id_scope(ids)
IdScope.new(ids)
end
end
Scope objects cont'd
class Scope::Consumption
def initialize(investor:, date_range:, user: )
@user = user
@investor = investor
@date_range = date_range
@scopes = [managed_teams_scope]
@scopes << subscriptions_scope
@scopes << activities_scope
end
def call(relation)
scopes.inject(relation) { |relashun, scope| scope.call(relashun) }
end
private
attr_reader :scopes, :date_range, :investor, :user
def managed_teams_scope
if user.administrator?
scope = Scope::ManagedTeams.new(user.person)
->(relation) { relation.where(people: { id: Person.where(team_id: scope.call(Team.all)) }) }
elsif user.manager?
->(relation) { relation }
else
->(relation) { relation.where(people: { id: user.person.id }) }
end
end
def subscriptions_scope
Scope::Consumption::Subscriptions.new(investor: investor, date_range: date_range)
end
def activities_scope
Scope::Consumption::Activities.new(investor: investor)
end
end
Thank you!
Questions?
scopes
By hegedus-zoltan
scopes
- 537