One of my biggest frustrations with ActiveRecord and many other ORMs (looking at you, node-orm2) is the lack of a solid query builder to join various, dynamic query filters into a single SQL statement. It seems obvious that this should be the meat-and-potatoes of the entire query language abstraction framework, and yet there is still no clean way to build a SQL statement containing a variable combination of attribute filters. Below we’ll examine some different functional (yet still ugly) techniques for doing so.
Imagine for a second you are building an app to best match prospective guitar players with the guitar of their dreams. To do so, you build a slick web interface with a large number of filter inputs for future rockers to drill-down based on what they deem important, and want to return a ranked list of guitars matching this criteria. You begin by defining a basic schema (oversimplified for our purposes) and writing the migrations.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
class
CreateGuitars
<
ActiveRecord
::
Migration
def
change
create
_table
"guitars"
do
|
t
|
t
.
string
"type"
,
:
null
=
>
false
# Guitar, Banjo, Bass Guitar, Ukelele, etc
t
.
string
"make"
,
:
null
=
>
false
# Gibson, Fender, PRS, etc
t
.
string
"model"
,
:
null
=
>
false
# SG, Stratocaster, Flying-V, etc
t
.
string
"color"
# Hot-rod Red, Lightning Blue
t
.
integer
"price"
,
:
null
=
>
false
t
.
integer
"year"
,
:
null
=
>
false
t
.
boolean
"used"
,
:
def
ault
=
>
false
t
.
timestamps
end
end
end
|
|
1
2
3
4
5
6
7
8
9
10
11
|
class
CreateMerchants
<
ActiveRecord
::
Migration
def
change
create
_table
"merchants"
do
|
t
|
t
.
string
"name"
,
:
null
=
>
false
t
.
string
"city"
,
:
null
=
>
false
t
.
string
"state"
t
.
string
"email"
t
.
string
"phone"
end
end
end
|
After gathering and inserting a substantial amount of guitar inventory & spec data from Guitar Center and local pawn shops, you begin drafting the API. Rock enthusiasts should be able to filter on all guitar model attributes, though they may only choose to filter on one or two. Seems easy, right? This brings us to Ugly Solution #1:
|
1
2
3
4
5
6
7
8
9
10
|
@
guitars
=
Guitar
.
limit
(
25
)
.
includes
(
[
:
merchants
]
)
.
order
(
"#{params[:order] || 'price'} #{params[:sort] || 'DESC'}"
)
@
guitars
=
@
guitars
.
where
(
'type'
,
params
[
:
type
]
)
if
params
[
:
type
]
@
guitars
=
@
guitars
.
where
(
'make'
,
params
[
:
make
]
)
if
params
[
:
make
]
@
guitars
=
@
guitars
.
where
(
'model'
,
params
[
:
model
]
)
if
params
[
:
model
]
@
guitars
=
@
guitars
.
where
(
'color'
,
params
[
:
color
]
)
if
params
[
:
color
]
@
guitars
=
@
guitars
.
where
(
'price < ?'
,
params
[
:
price
]
)
if
params
[
:
price
]
@
guitars
=
@
guitars
.
where
(
'merchants.city'
,
params
[
:
city
]
)
if
params
[
:
city
]
render
:
json
=
>
@
guitars
.
as_json
(
:
include
=
>
:
merchants
)
|
ActiveRecord evaluates each lazily, so the query will not execute until it is fully built and returned. That means @guitars will contain the proper runtime clause, though it is rather annoying to have to write the same overriding logic to sub-select and drill into the query. And the lazy evaluation isn’t all that intuitive. So let’s try writing a key-value abstraction function to join parameters with their filter attribute.
|
1
2
3
4
5
6
|
filters
=
[
:
type
,
:
make
,
:
model
,
:
color
]
wheres
=
filters
.
map
{
|
key
|
params
.
has_key
?
(
key
)
?
{
key
=
>
params
[
key
]
}
:
{
}
}
.
inject
(
{
}
)
{
|
hash
,
injected
|
hash
.
merge
!
(
injected
)
}
@
guitars
=
Guitar
.
where
(
wheres
)
.
limit
(
25
)
render
:
json
=
>
@
guitars
.
as_json
(
:
include
=
>
:
merchants
)
|
Sure does look prettier, and way less code. But what about the price and merchant city filter? These cannot be achieved with the simple hash matching above. Well crap. Let’s try that again, this time building the SQL query a bit more manually.
|
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
|
conditions
=
String
.
new
wheres
=
Array
.
new
if
params
.
has_key
?
(
:
type
)
conditions
<<
" AND "
unless
conditions
.
length
==
0
conditions
<<
"type = ?"
wheres
<<
params
[
:
type
]
end
if
params
.
has_key
?
(
:
make
)
conditions
<<
" AND "
unless
conditions
.
length
==
0
conditions
<<
"make = ?"
wheres
<<
params
[
:
make
]
end
if
params
.
has_key
?
(
:
model
)
conditions
<<
" AND "
unless
conditions
.
length
==
0
conditions
<<
"model = ?"
wheres
<<
params
[
:
model
]
end
if
params
.
has_key
?
(
:
color
)
conditions
<<
" AND "
unless
conditions
.
length
==
0
conditions
<<
"color = ?"
wheres
<<
params
[
:
color
]
end
if
params
.
has_key
?
(
:
price
)
conditions
<<
" AND "
unless
conditions
.
length
==
0
conditions
<<
"price < ?"
wheres
<<
params
[
:
price
]
end
if
params
.
has_key
?
(
:
city
)
conditions
<<
" AND "
unless
conditions
.
length
==
0
conditions
<<
"merchants.city = ?"
wheres
<<
params
[
:
city
]
end
wheres
.
insert
(
0
,
conditions
)
@guitars
=
Guitar
.
includes
(
[
:
merchants
]
)
.
where
(
wheres
)
.
order
(
"#{params[:sort] or 'price'} #{params[:order] or 'DESC'}"
)
.
limit
(
25
)
render
:
json
=
>
@guitars
.
as_json
(
:
include
=
>
:
merchants
)
|
The DRY in you hates all of the copy-paste. It gets the job done, but what if we want to add new filterable attributes down the road (things like neck shape, fret count, pickup style, etc)? Using the above convention thats +5 lines of code for every filterable attribute! And it sits in the API controller layer which is supposed to be thin! OK, fine. Let’s sober up and try that again. Time to consult the rails guides and refactor this using model scopes, their recommended way of handling this kind of dynamic chaining.
|
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
|
# Model
class
Guitar
<
ActiveRecord
::
Base
scope
:
by_type
,
lambda
{
|
type
|
return
scoped
unless
type
.
present
?
where
(
:
type
=
>
type
)
}
scope
:
by_color
,
lambda
{
|
color
|
return
scoped
unless
color
.
present
?
where
(
:
color
=
>
color
)
}
scope
:
by_make
,
lambda
{
|
make
|
return
scoped
unless
make
.
present
?
where
(
:
make
=
>
make
)
}
scope
:
by_model
,
lambda
{
|
model
|
return
scoped
unless
model
.
present
?
where
(
:
model
=
>
model
)
}
scope
:
by_price
,
lambda
{
|
price
|
return
scoped
unless
price
.
present
?
where
(
"price < ?"
,
price
)
}
scope
:
by_city
,
lambda
{
|
city
|
return
scoped
unless
city
.
present
?
where
(
"merchants.city = ?"
,
city
)
}
end
# Controller
class
GuitarsController
<
ApplicationController
def
index
@
guitars
=
Guitar
.
includes
(
[
:
merchants
]
)
.
by_type
(
params
[
:
type
]
)
.
by_color
(
params
[
:
color
]
)
.
by_make
(
params
[
:
make
]
)
.
by_model
(
params
[
:
model
]
)
.
by_price
(
params
[
:
price
]
)
.
by_city
(
params
[
:
city
]
)
.
order
(
order
)
.
limit
(
limit
)
render
:
json
=
>
@
guitars
.
as_json
(
:
include
=
>
:
merchants
)
end
end
|
Or using class methods:
|
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
|
# Model
class
Guitar
<
ActiveRecord
::
Base
def
self
.
by_type
(
type
)
return
scoped
unless
type
.
present
?
where
(
:
type
=
>
type
)
end
def
self
.
by_color
(
color
)
return
scoped
unless
color
.
present
?
where
(
:
color
=
>
color
)
end
def
self
.
by_make
(
make
)
return
scoped
unless
make
.
present
?
where
(
:
make
=
>
make
)
end
def
self
.
by_model
(
model
)
return
scoped
unless
model
.
present
?
where
(
:
model
=
>
model
)
end
def
self
.
by_price
(
price
)
return
scoped
unless
price
.
present
?
where
(
"price < ?"
,
price
)
end
def
self
.
by_city
(
city
)
return
scoped
unless
city
.
present
?
where
(
"merchants.city = ?"
,
city
)
end
end
# Controller
class
GuitarsController
<
ApplicationController
def
index
@guitars
=
Guitar
.
includes
(
[
:
merchants
]
)
.
by_type
(
params
[
:
type
]
)
.
by_color
(
params
[
:
color
]
)
.
by_make
(
params
[
:
make
]
)
.
by_model
(
params
[
:
model
]
)
.
by_price
(
params
[
:
price
]
)
.
by_city
(
params
[
:
city
]
)
.
order
(
order
)
.
limit
(
limit
)
render
:
json
=
>
@guitars
.
as_json
(
:
include
=
>
:
merchants
)
end
end
|
Really, Rails? We can’t group filters inside a scope without knowing beforehand which filters a rocker will select. As these filters are each independent of each other (except for maybe ‘make’ and ‘model’), we need to create a separate model scope for each. So for each new filterable attribute, we need to switch between model and controller to create the scope and call it. This is not only annoying, it is hardly maintainable. Yet this is the recommended way of chaining WHERE clauses – a very common use case. Am I missing something? Hopefully. Comments appreciated.
1087
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
