Skip to content

Instantly share code, notes, and snippets.

@jpcody
Last active August 29, 2015 14:04
Show Gist options
  • Save jpcody/a52a9d344f350cf75bc3 to your computer and use it in GitHub Desktop.
Save jpcody/a52a9d344f350cf75bc3 to your computer and use it in GitHub Desktop.
A refactoring of a hairy Arel query in 5 steps.
# Bean.find_by_sql(query)
query = Bean.
arel_table.project(Bean.arel_table[Arel.star]).
join(Roaster.arel_table).on(Roaster.arel_table[:id].eq(Bean.arel_table[:roaster_id])).
join(City.arel_table).on(City.arel_table[:id].eq(Roaster.arel_table[:city_id])).
join(Rating.arel_table.alias("bean_ratings")).on(
Arel::Table.new(:bean_ratings)[:type].eq("Bean"),
Arel::Table.new(:bean_ratings)[:target_id].eq(Bean.arel_table[:id])
).
join(Rating.arel_table.alias("roaster_ratings")).on(
Arel::Table.new(:roaster_ratings)[:type].eq("Roaster"),
Arel::Table.new(:roaster_ratings)[:target_id].eq(Roaster.arel_table[:id])
).
where(Bean.arel_table[:roasted_at].gteq(3.days.ago)).
where(Roaster.arel_table[:city_id].not_eq(5)).
where(
Bean.arel_table[:flavors].matches("%sweet%").or(
Bean.arel_table[:flavors].eq(nil))
).
having(
Arel::Table.new(:bean_ratings)[:value].average(nil).gteq(90).or(
Arel::Table.new(:roaster_ratings)[:value].average(nil).gteq(90).or(
Arel::Table.new(:bean_ratings)[:id].count.eq(0).or(
Arel::Table.new(:roaster_ratings)[:id].count.eq(0))))
).
group(
Bean.arel_table[:id],
Roaster.arel_table[:name]
).
order(Roaster.arel_table[:name].desc)
# Bean.find_by_sql(query)
def beans; Bean.arel_table; end
def ratings; Rating.arel_table; end
def roasters; Roaster.arel_table; end
def cities; City.arel_table; end
def bean_ratings; alias_table(:bean_ratings); end
def roaster_ratings; alias_table(:roaster_ratings); end
def self.alias_table(name)
Arel::Table.new(name.to_s).alias(name.to_s)
end
def query
beans.
project(beans[Arel.star]).
join(roasters).on(roasters[:id].eq(beans[:roaster_id])).
join(cities).on(cities[:id].eq(roasters[:city_id])).
join(bean_ratings).on(
bean_ratings[:type].eq("Bean"),
bean_ratings[:target_id].eq(beans[:id])
).
join(roaster_ratings).on(
roaster_ratings[:type].eq("Roaster"),
roaster_ratings[:target_id].eq(roasters[:id])
).
where(beans[:roasted_at].gteq(3.days.ago)).
where(roasters[:city_id].not_eq(5)).
where(beans[:flavors].matches("%sweet%").or(beans[:flavors].eq(nil))).
having(
bean_ratings[:value].average(nil).gteq(90).or(
roaster_ratings[:value].average(nil).gteq(90).or(
bean_ratings[:id].count.eq(0).or(
roaster_ratings[:id].count.eq(0))))
).
group(beans[:id], roasters[:name]).
order(roasters[:name].desc)
end
# Bean.find_by_sql(query)
def beans; Bean.arel_table; end
def ratings; Rating.arel_table; end
def roasters; Roaster.arel_table; end
def cities; City.arel_table; end
def bean_ratings; alias_table(:bean_ratings); end
def roaster_ratings; alias_table(:roaster_ratings); end
def alias_table(name)
Arel::Table.new(name.to_s).alias(name.to_s)
end
def flavor_matches(term)
beans[:flavors].matches("%#{term}%")
end
def blank_flavor
beans[:flavors].eq(nil)
end
def high_ratings(threshold = 90)
bean_ratings[:value].average(nil).gteq(90).or(
roaster_ratings[:value].average(nil).gteq(90))
end
def no_ratings(threshold = 90)
bean_ratings[:value].count.eq(0).or(
roaster_ratings[:value].count.eq(0))
end
def beans_to_roasters
roasters[:id].eq(beans[:roaster_id])
end
def cities_to_roasters
cities[:id].eq(roasters[:city_id])
end
def beans_to_ratings
[
bean_ratings[:type].eq("Bean"),
bean_ratings[:target_id].eq(beans[:id])
]
end
def roasters_to_ratings
[
roaster_ratings[:type].eq("Roaster"),
roaster_ratings[:target_id].eq(roasters[:id])
]
end
def roasted_within(roasted_since)
beans[:roasted_at].gteq(roasted_since.days.ago)
end
def query
beans.
project(beans[Arel.star]).
join(roasters).on(beans_to_roasters).
join(cities).on(cities_to_roasters).
join(beans.alias(bean_ratings.name)).on(*beans_to_ratings).
join(roasters.alias(roaster_ratings.name)).on(*roasters_to_ratings).
where(roasted_within(3)).
where(roasters[:city_id].not_eq(5)).
where(flavor_matches("sweet").or(blank_flavor)).
having(high_ratings.or(no_ratings)).
group(beans[:id], roasters[:name]).
order(roasters[:name].desc)
end
# Bean.query
class Bean
class << self
def beans; Bean.arel_table; end
def ratings; Rating.arel_table; end
def roasters; Roaster.arel_table; end
def cities; City.arel_table; end
def bean_ratings; alias_table(:bean_ratings); end
def roaster_ratings; alias_table(:roaster_ratings); end
def alias_table(name)
Arel::Table.new(name.to_s).alias(name.to_s)
end
def high_ratings(threshold = 90)
bean_ratings[:value].average(nil).gteq(90).or(
roaster_ratings[:value].average(nil).gteq(90))
end
def no_ratings(threshold = 90)
bean_ratings[:value].count.eq(0).or(
roaster_ratings[:value].count.eq(0))
end
def roasted_since(roasted_since)
where(beans[:roasted_at].gteq(roasted_since))
end
def not_city(city_id)
where(roasters[:city_id].not_eq(5))
end
def not_flavor(flavor_name, options = {})
options.reverse_merge allow_blank: true
predicate = beans[:flavors].matches("%sweet%")
if options[:allow_blank]
predicate = predicate.or(beans[:flavors].eq(nil))
end
where(predicate)
end
def bean_ratings_join
beans.join(ratings.alias(bean_ratings.name)).on(
bean_ratings[:type].eq("Bean"),
bean_ratings[:target_id].eq(beans[:id])
).join_sources
end
def roaster_ratings_join
roasters.join(ratings.alias(roaster_ratings.name)).on(
roaster_ratings[:type].eq("Roaster"),
roaster_ratings[:target_id].eq(roasters[:id])
).join_sources
end
def query
Bean.
joins({roaster: :city}, bean_ratings_join, roaster_ratings_join).
having(high_ratings.or(no_ratings)).
roasted_since(3.days.ago).
not_city(5).
not_flavor("sweet").
group(beans[:id], roasters[:name]).
order(roasters[:name].desc)
end
end
end
# user = Struct.new(:city_id).new(5)
# BeanFinder.new(user).query
class BeanFinder
attr_reader :user, :options, :scope
def initialize(user, options = {})
@user = user
@options = options.reverse_merge minimum_rating: 90,
exclude_local: true,
include_unrated: true,
freshness_threshold: 3.days.ago,
flavor: "sweet"
end
def query
set_base_scope
scope_by_ratings
scope_by_freshness
scope_excluding_city if options[:exclude_local]
scope_by_flavor
@scope.
group(beans[:id], roasters[:name]).
order(roasters[:name].desc)
end
private
def relation; Bean.all; end
def beans; Bean.arel_table; end
def ratings; Rating.arel_table; end
def roasters; Roaster.arel_table; end
def cities; City.arel_table; end
def bean_ratings; alias_table(:bean_ratings); end
def roaster_ratings; alias_table(:roaster_ratings); end
def alias_table(name)
Arel::Table.new(name.to_s).alias(name.to_s)
end
def set_base_scope
@scope = Bean.all.
joins({roaster: :city}, bean_ratings_join, roaster_ratings_join)
end
def scope_by_ratings
min = options[:minimum_rating]
high_ratings = bean_ratings[:value].average(nil).gteq(min).or(
roaster_ratings[:value].average(nil).gteq(min))
no_ratings = bean_ratings[:value].count.eq(0).or(
roaster_ratings[:value].count.eq(0))
predicate = high_ratings
if options[:include_unrated]
predicate = predicate.or(no_ratings)
end
@scope = scope.having(predicate)
end
def scope_by_freshness
@scope = scope.where(beans[:roasted_at].gteq(options[:freshness_threshold]))
end
def scope_excluding_city
@scope = scope.where(roasters[:city_id].not_eq(user.city_id))
end
def scope_by_flavor
flavor_match = beans[:flavors].matches("%#{options[:flavor]}%")
flavorless = beans[:flavors].eq(nil)
@scope = scope.where(flavor_match.or(flavorless))
end
def bean_ratings_join
beans.join(ratings.alias(bean_ratings.name)).on(
bean_ratings[:type].eq("Bean"),
bean_ratings[:target_id].eq(beans[:id])
).join_sources
end
def roaster_ratings_join
roasters.join(ratings.alias(roaster_ratings.name)).on(
roaster_ratings[:type].eq("Roaster"),
roaster_ratings[:target_id].eq(roasters[:id])
).join_sources
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment