rails 中 几种动态 查询 的实现方式

转载 ：

http://alexehrnschwender.com/2013/10/dynamic-activerecord-querying-rails-4/

Dynamic ActiveRecord querying in Rails 4 by example

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.

Guitars table migration

Ruby

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", :default => false

      t.timestamps

    end

  end

end

 

Merchants table migration

Ruby

1

2

3

4

5

6

7

8

9

10

11

classCreateMerchants<ActiveRecord::Migration

  defchange

    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:

Ugly Solution #1 - a whole bunch of conditionals

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.

Ugly Solution 2 - key abstraction and injection

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.

 

Ugly Solution 3 - query building thru array aggregation

Ruby

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.

 

Ugly Solution #4 - individually chained scopes

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

classGuitar<ActiveRecord::Base

  scope:by_type,lambda{|type|

    returnscopedunlesstype.present?

    where(:type=>type)

  }

 

  scope:by_color,lambda{|color|

    returnscopedunlesscolor.present?

    where(:color=>color)

  }

 

  scope:by_make,lambda{|make|

    returnscopedunlessmake.present?

    where(:make=>make)

  }

 

  scope:by_model,lambda{|model|

    returnscopedunlessmodel.present?

    where(:model=>model)

  }

  scope:by_price,lambda{|price|

    returnscopedunlessprice.present?

    where("price < ?",price)

  }

  scope:by_city,lambda{|city|

    returnscopedunlesscity.present?

    where("merchants.city = ?",city)

  }

end

 

# Controller

classGuitarsController<ApplicationController

  defindex

    @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:

Ugly Solution #4 - individually chained scopes

Ruby

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

classGuitar<ActiveRecord::Base

 

  defself.by_type(type)

    returnscopedunlesstype.present?

    where(:type=>type)

  end

  defself.by_color(color)

    returnscopedunlesscolor.present?

    where(:color=>color)

  end

  defself.by_make(make)

    returnscopedunlessmake.present?

    where(:make=>make)

  end

  defself.by_model(model)

    returnscopedunlessmodel.present?

    where(:model=>model)

  end

  defself.by_price(price)

    returnscopedunlessprice.present?

    where("price < ?",price)

  end

  defself.by_city(city)

    returnscopedunlesscity.present?

    where("merchants.city = ?",city)

  end

 

end

 

# Controller

classGuitarsController<ApplicationController

  defindex

    @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.