# Better way to generate a query?

**URL:** <https://www.ruby-forum.com/t/better-way-to-generate-a-query/111189>\
**Category:** Rails\
**Created:** [August 3, 2007, 5:30pm UTC](https://www.ruby-forum.com/t/better-way-to-generate-a-query/111189 "2007-08-03T17:30:25Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ye\_Dingding](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/49beb7/32.png) [@Ye\_Dingding](https://www.ruby-forum.com/u/Ye_Dingding)\
**Post date:** [August 3, 2007, 5:30pm UTC](https://www.ruby-forum.com/t/better-way-to-generate-a-query/111189/1 "2007-08-03T17:30:25Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Ye\_Dingding](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/49beb7/32.png) [@Ye\_Dingding](https://www.ruby-forum.com/u/Ye_Dingding)\
**Post date:** [August 3, 2007, 6:26pm UTC](https://www.ruby-forum.com/t/better-way-to-generate-a-query/111189/2 "2007-08-03T18:26:32Z")

</div>

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

regards, \_jan

---

<div class="post-metadata">

**Author:** ![Ye\_Dingding](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/49beb7/32.png) [@Ye\_Dingding](https://www.ruby-forum.com/u/Ye_Dingding)\
**Post date:** [August 3, 2007, 6:43pm UTC](https://www.ruby-forum.com/t/better-way-to-generate-a-query/111189/3 "2007-08-03T18:43:34Z")

</div>

On 8/4/07, jeanmartin [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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.

---

<div class="post-metadata">

**Author:** ![Ye\_Dingding](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/49beb7/32.png) [@Ye\_Dingding](https://www.ruby-forum.com/u/Ye_Dingding)\
**Post date:** [August 3, 2007, 6:54pm UTC](https://www.ruby-forum.com/t/better-way-to-generate-a-query/111189/4 "2007-08-03T18:54:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Ye\_Dingding](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/49beb7/32.png) [@Ye\_Dingding](https://www.ruby-forum.com/u/Ye_Dingding)\
**Post date:** [August 3, 2007, 8:51pm UTC](https://www.ruby-forum.com/t/better-way-to-generate-a-query/111189/5 "2007-08-03T20:51:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Ye\_Dingding](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/49beb7/32.png) [@Ye\_Dingding](https://www.ruby-forum.com/u/Ye_Dingding)\
**Post date:** [August 3, 2007, 8:02pm UTC](https://www.ruby-forum.com/t/better-way-to-generate-a-query/111189/6 "2007-08-03T20:02:43Z")

</div>

On 8/4/07, jeanmartin [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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… ☹ 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