Better way to generate a query?

rubyonrails-talk
1

In the rails application, when the table structure is complex, i often have many ways to access the database to get the data, such as using several attributes.

For example,

table :person column :nickname

column :hobbies column :constellation …

I have to write several methods to fit for the requirement. For example, get_personal_by_nickname, get_personal_by_nickname_with_hobbies, so on.

So I wrote a function, using a condition map to do the mapping. like

module ActiveRecord class Base class << self # generate the query conditions due to the CMAP

  def generate_query(options = {})
    includes, conditions = Array.new, Array.new
    options.each do |key, value|
      begin
        if (@CMAP.has_key?(key) && !value.nil?)

          case @CMAP[key][0]
            when Symbol
            includes << @CMAP[key][0]
            when Hash
            includes << @CMAP[key][0][value.to_sym]
          end

          case @CMAP[key][1]
            when String
            conditions << @CMAP[key][1] if @CMAP[key][1]
            when Hash
            conditions << @CMAP[key][1][value.to_sym

] if @CMAP[key][1] end end rescue NameError $stderr.print “you should define @CMAP attribute for the query_generator” + $! raise end

    end
    [includes, conditions]
  end

end

In the specific model, @CMAP is defined as, for example, @CMAP = { :nickname => [:include, “nickname = :nickname”],

:hobbies => [:include, “hobbies = :hobbies”], … }

Then using hash as a param and get the generated query.

But this way i need to take care of @CMAP, indeed sometimes it can’t fit my requirement either.

Is there a better way to do the work? How do you guys work this?

thx.

2

I have to write several methods to fit for the requirement. For example, get_personal_by_nickname, get_personal_by_nickname_with_hobbies, so on.

Are you aware of the build-in dynamic attribute-based finders (although this might not cover all possible uses of you method)? e.g.   User.find_by_username_and_password( usrname, pw ) is equal to   User.find :first, :conditions => ["username = ? AND password = ?", usrname, pw]

read more: ActiveRecord::Base

regards, _jan

3

I have to write several methods to fit for the requirement. For example, get_personal_by_nickname, get_personal_by_nickname_with_hobbies, so on.

Are you aware of the build-in dynamic attribute-based finders

(although this might not cover all possible uses of you method)?

No. The query may be complex. for example, include several tables. I need to generate the condition dynamically.

4

> > I have to write several methods to fit for the requirement. For example, > > get_personal_by_nickname, get_personal_by_nickname_with_hobbies, so on.

> Are you aware of the build-in dynamic attribute-based finders > (although this might not cover all possible uses of you method)?

No. The query may be complex. for example, include several tables. I need to generate the condition dynamically.

Then -for me- it depends on the complexity: - For time consuming tasks i try to solve it inside the db: stored procedures, views etc. - If there are not too many different querys I create model methods (like find_most_active_members()) - In general the find method provides almost all possibly needed options for sql queries (are you sure it doesn't fit your needs? You can :include => other_tables, define :conditions, set a :limit and :offset etc.(

Maybe you could provide us with an example of one of your complex queries.

regards, _jan

5

I have to write several methods to fit for the requirement. For example, get_personal_by_nickname, get_personal_by_nickname_with_hobbies, so on.

Are you aware of the build-in dynamic attribute-based finders

(although this might not cover all possible uses of you method)?

No. The query may be complex. for example, include several tables. I need to generate the condition dynamically.

Then -for me- it depends on the complexity:

  • For time consuming tasks i try to solve it inside the db: stored procedures, views etc.
  • If there are not too many different querys I create model methods

(like find_most_active_members())

  • In general the find method provides almost all possibly needed options for sql queries (are you sure it doesn’t fit your needs? You can :include => other_tables, define :conditions, set a :limit

and :offset etc.(

Yes. I can. But i’m lazy… :frowning: I want a common place to combine the query for me.

Let me take a example.

Book Model has_many :authors has_many :tags

belong_to :category

has_many :readers

get_book(params)

params maybe author_name, or tag_name, or category_name, or reader_name, or mixed. With different options, the includes array and conditions are different. I don’t want to repeat the code, instead i want a way to directly turn the params to the condition. So as in other model. That’s why i thought of my last design as in my first post.

6

A strategy I have employed on similar tasks is to model the queries as objects, e.g.:

class EventQuery

def self.from_params(params)

 ...

end

def find_events

 ...

end

end

It tends to encapsulate things fairly nicely. In my app, I also had the need to store the queries in the database, so I went further and subclassed ActiveRecord::Base.

  • donald