-
Notifications
You must be signed in to change notification settings - Fork 0
/
products_and_categories.rb
210 lines (178 loc) · 6.76 KB
/
products_and_categories.rb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
require "active_record"
require "pg"
require_relative "../lib/searchcraft"
# Connection to PostgreSQL server
DATABASE_URL = ENV.fetch("DATABASE_URL", "postgres://localhost:5432")
ActiveRecord::Base.establish_connection(DATABASE_URL)
# Database name
database_name = "searchcraft_example_products_and_categories"
# Drop the database if it already exists
if ActiveRecord::Base.connection.execute("SELECT 1 FROM pg_database WHERE datname = '#{database_name}'").any?
ActiveRecord::Base.connection.drop_database(database_name)
puts "Dropped existing database '#{database_name}'"
end
at_exit do
ActiveRecord::Base.connection_pool.disconnect!
ActiveRecord::Base.establish_connection(DATABASE_URL)
ActiveRecord::Base.connection.drop_database(database_name)
puts "\nExiting... Dropped database '#{database_name}'"
end
# Create the database
ActiveRecord::Base.connection.create_database(database_name)
puts "Created new database '#{database_name}'"
ActiveRecord::Base.establish_connection("#{DATABASE_URL}/#{database_name}")
# Migration for products table
class CreateProducts < ActiveRecord::Migration[7.0]
def change
create_table :products do |t|
t.string :name, null: false
t.boolean :active, default: true, null: false
t.timestamps
end
end
end
# Migration for categories table
class CreateCategories < ActiveRecord::Migration[7.0]
def change
create_table :categories do |t|
t.string :name, null: false
t.boolean :active, default: true, null: false
t.timestamps
end
end
end
# Migration for product_categories join table
class CreateProductCategories < ActiveRecord::Migration[7.0]
def change
create_table :product_categories do |t|
t.references :product, null: false, foreign_key: true
t.references :category, null: false, foreign_key: true
t.timestamps
end
end
end
# Running migrations
CreateProducts.new.change
CreateCategories.new.change
CreateProductCategories.new.change
class Product < ActiveRecord::Base
has_many :product_categories
has_many :categories, through: :product_categories
end
class Category < ActiveRecord::Base
has_many :product_categories
has_many :products, through: :product_categories
end
class ProductCategory < ActiveRecord::Base
belongs_to :product
belongs_to :category
end
# Inserting seed data
laptop = Product.create!(name: "Laptop 3")
iphone = Product.create!(name: "iPhone 15")
inactive_iphone = Product.create!(name: "iPhone 2", active: false)
monopoly = Product.create!(name: "Monopoly")
electronics = Category.create!(name: "Electronics")
phones = Category.create!(name: "Phones")
inactive_category = Category.create!(name: "Board Games", active: false)
ProductCategory.create!(product: laptop, category: electronics)
ProductCategory.create!(product: iphone, category: electronics)
ProductCategory.create!(product: iphone, category: phones)
ProductCategory.create!(product: inactive_iphone, category: electronics)
ProductCategory.create!(product: monopoly, category: inactive_category)
# Printing all three models' rows
puts "Products:"
Product.all.each { |p| puts p.name }
puts "\nCategories:"
Category.all.each { |c| puts c.name }
puts "\nProduct Categories:"
ProductCategory.all.each { |pc| puts "Product: #{pc.product.name}, Category: #{pc.category.name}" }
# Our model for the materialized view created by ProductSearchBuilder below
class ProductSearch < ActiveRecord::Base
include SearchCraft::Model
belongs_to :product, foreign_key: :product_id, primary_key: :id
belongs_to :category, foreign_key: :category_id, primary_key: :id
scope :within_category, ->(category) { where(category: category) }
end
class ProductSearchBuilder < SearchCraft::Builder
def view_scope
Product
.joins(:categories)
.where(active: true) # only active products
.where(categories: {active: true}) # only active categories
.order(:product_name)
.select(
"products.id AS product_id",
"products.name AS product_name",
"categories.id AS category_id",
"categories.name AS category_name"
# Or could use Arel:
# Product.arel_table[:id].as("product_id"),
# Product.arel_table[:name].as("product_name"),
# Category.arel_table[:id].as("category_id"),
# Category.arel_table[:name].as("category_name")
)
end
end
SearchCraft::Builder.rebuild_any_if_changed!
puts "\nViewHashStore now contains:"
pp SearchCraft::ViewHashStore.all
puts "\nDoes ProductSearch have a table/view in database? #{ProductSearch.table_exists?}"
puts "\nWhat does the SQL look like?"
puts ProductSearchBuilder.new.view_select_sql
puts "\nProductSearch rows only include active products and their active categories:"
pp ProductSearch.all
puts "\nSearch for Electronics:"
pp ProductSearch.within_category(electronics)
puts "\nChange 'Board Games' to active"
inactive_category.update!(active: true)
board_games = ProductSearch.within_category(inactive_category)
puts "Initially there are #{board_games.count} board games as we have not refreshed the view."
pp board_games
puts "\nRefresh the view..."
ProductSearch.refresh!
puts "Now, there are #{board_games.count} board games in the refreshed view."
pp board_games.reload
puts "\nWe can add indexes to materialized views, say for lookup on category_id:"
class ProductSearchBuilder < SearchCraft::Builder
def view_indexes
{
# index_name: {columns: ["column1", "column2"], unique: false}
category_id: {columns: ["category_id"]}
}
end
end
# TODO: include index SQL in view_sql_hash
SearchCraft::ViewHashStore.destroy_all
SearchCraft::Builder.rebuild_any_if_changed!
puts "\nViewHashStore now contains:"
pp SearchCraft::ViewHashStore.all
puts "\nShow indexes in for table:"
results = ActiveRecord::Base.connection.execute("SELECT indexname FROM pg_indexes WHERE tablename = '#{ProductSearch.table_name}';")
results.each do |row|
puts "* #{row["indexname"]}"
end
puts "\nRedefine builder to use nextval and sequence for an id column:"
class ProductSearchBuilder < SearchCraft::Builder
def view_scope # standard:disable Lint/DuplicateMethods
Product
.joins(:categories)
.where(active: true) # only active products
.where(categories: {active: true}) # only active categories
.order(:product_name)
.select(
"nextval('#{view_id_sequence_name}') AS id, " \
"products.id AS product_id, " \
"products.name AS product_name, " \
"categories.id AS category_id, " \
"categories.name AS category_name"
)
end
end
# Manually drop + create the materialized view
SearchCraft::Builder.rebuild_any_if_changed!
puts "\nViewHashStore now contains:"
pp SearchCraft::ViewHashStore.all
ProductSearch.reset_column_information # Not required in development or test environments
puts "\nProductSearch now has an id column:"
pp ProductSearch.all.reload