Better way to generate a query?

Rails
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

On 8/4/07, jeanmartin [email protected] wrote:

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.

e.g.

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

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
6

On 8/4/07, jeanmartin [email protected] wrote:

No. The query may be complex. for example, include several tables. I
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.

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