Better way to generate a query?

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.

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: http://api.rubyonrails.org/classes/ActiveRecord/Base.html

regards, _jan

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.

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

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.

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