Skip to content

Instantly share code, notes, and snippets.

@johnnaegle

johnnaegle/create_products.rb Secret

Created Jan 11, 2014
Embed
What would you like to do?
class CreateProducts < ActiveRecord::Migration
def change
create_table :products do |t|
t.string :name
t.string :brand
end
end
end
class Product < ActiveRecord::Base
BRAND_CAPITIALIZATION_GROUPS_AND_COUNTS_COLUMNS = ["brand_for_grouping", "brand_to_display", "count_per_unique_brand_capitilization", "row_number" ,"total_count"]
MOST_POPULAR_BRAND_CAPITIALIZATION_COLUMNS = ['brand_to_display', 'total_count', 'row_number']
def self.seed
flavors = ['Chocolate', 'Vanilla', 'Strawberry', 'Grape', 'Apple', 'Rocky Road', 'Orange']
brands = ['Whole Foods', 'Safeway', 'Kroger', 'Schnucks']
transforms = [:downcase, :upcase, :titleize]
25.times {Product.create(:name => "#{flavors.shuffle.first} Ice Cream", :brand => brands.shuffle.first.send( transforms.shuffle.first))}
8.times {Product.create(:name => "#{flavors.shuffle.first} Ice Cream", :brand => brands.shuffle.first.titleize)}
end
def self.query_to_md(columns, query)
results = query.to_a
puts columns.map(&:titleize).join(' | ')
puts columns.map{|x| x.gsub(/./, '-')}.join(' | ')
results.each do |row|
puts columns.map{|col| row.send(col)}.join(' | ')
end
end
def self.table_data_md(search)
query_to_md(["name", "brand"], Product.matching_search(search).order("lower(brand)"))
end
def self.brand_capitialization_groups_and_counts_md(search)
query_to_md(
BRAND_CAPITIALIZATION_GROUPS_AND_COUNTS_COLUMNS,
Product.brand_capitialization_groups_and_counts(search).order("brand_for_grouping").order("row_number")
)
end
def self.brand_capitialization_groups_and_counts_interesting_md(search)
query = "with " << brand_capitialization_groups_and_counts_cte(search) <<
" select " << BRAND_CAPITIALIZATION_GROUPS_AND_COUNTS_COLUMNS.join(',') <<
" from brand_capitialization_groups_and_counts" <<
" where row_number = 1"
query_to_md(BRAND_CAPITIALIZATION_GROUPS_AND_COUNTS_COLUMNS, Product.find_by_sql(query))
end
def self.most_popular_brand_capitialization_cte_md(search)
query = "with " << brand_capitialization_groups_and_counts_cte(search) << ", " << most_popular_brand_capitialization_cte <<
" select " << MOST_POPULAR_BRAND_CAPITIALIZATION_COLUMNS.join(',') <<
" from most_popular_brand_capitialization"
query_to_md(MOST_POPULAR_BRAND_CAPITIALIZATION_COLUMNS, Product.find_by_sql(query))
end
def self.matching_brands_md(search)
query_to_md(['brand_to_display', 'total_count'], Product.matching_brands('ice'))
end
scope :brand_capitialization_groups_and_counts, lambda { |search|
Product.select("lower(coalesce(products.brand, '')) as brand_for_grouping,
coalesce(products.brand, '') as brand_to_display,
count(*) over (partition by coalesce(products.brand, '')) as count_per_unique_brand_capitilization,
row_number() over (partition by coalesce(products.brand, '')) as row_number,
count(*) over (partition by lower(coalesce(products.brand, ''))) as total_count").matching_search(search)
}
def self.brand_capitialization_groups_and_counts_cte(search)
"brand_capitialization_groups_and_counts as
(" << Product.brand_capitialization_groups_and_counts(search).to_sql << ")"
end
def self.most_popular_brand_capitialization_cte
"most_popular_brand_capitialization as
(
select
brand_to_display,
total_count,
row_number() over (partition by brand_for_grouping order by count_per_unique_brand_capitilization desc) as row_number
from brand_capitialization_groups_and_counts
where row_number = 1
)"
end
def self.matching_brands(search)
query = "with " << brand_capitialization_groups_and_counts_cte(search) << "," << most_popular_brand_capitialization_cte << "
select brand_to_display, total_count from most_popular_brand_capitialization
where row_number = 1
order by total_count desc"
Product.find_by_sql(query)
end
scope :matching_search, lambda {|search|
where("name ilike ?", "%#{search}%")
}
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.