Brainspl.at

(15)

cond = EZ::Where::Condition.new :my_table do
  title =~ "%#{params[:search]}%" unless params[:search].blank?
  user == params[:user] unless params[:user].blank?
end

cond = EZ::Where::Condition.new :my_table do
  title =~ "%#{params[:search]}%"
  user == params[:user]
end

# find all posts where with body LIKE '%rails%'
@posts = Post.find_where(:all) { |p| p.body =~ "%rails%" }
=>  ["posts.body LIKE ?", "%rails%"]

# find all posts where with title, body or extended LIKE '%rails%'
@posts = Post.find_where :all do |post|
  post.any_of(:title, :body, :extened) =~ '%rails%'
end  
=> ["(posts.title LIKE ? OR posts.body LIKE ? 
    OR posts.extended LIKE ?)", "%rails%", "%rails%", "%rails%"]

# find all articles with title, body or extended LIKE "%#{params[:search]}%"
# AND (author.name = params[:author] OR comment.body LIKE "%#{params[:search]}%")
@articles = Article.find_where(:all, :include => [:author, { :comments => :users }]) do
  |article, author, comment|
   article.any_of(:title, :body, :extended) =~ "%#{params[:search]}%"
   any {
     author.name == params[:author]
     comment.body =~ "%#{params[:search]}%"
   }
end
=>["(articles.title LIKE ? OR articles.body LIKE ? 
   OR articles.extended LIKE ?) AND ((authors.name = ?)
   OR (comments.body LIKE ?))", "%foo%", "%foo%", "%foo%", "Ezra", "%foo%"]

     One of the coolest new features is the c method. This promotes conditions
into first class objects. It works like this:

c{ title =~ '%foo%' }.to_sql
=> ["title LIKE ?", "%foo%"]
With a naked block like this it just converts whats inside the block 
into a condition.

c(:posts) {  title =~ '%foo%' }.to_sql  
=> ["posts.title LIKE ?",  "%foo%"] 
When you give it a plural table name as  an arguments it scopes the condition to that
table.

You can also use the c object directly in a find like so:

Post.find :all, :conditions => c{ body =~ '%rails%' }

Now that you can have conditions as objects you can do some really interesting
and complex queries with them. You can use operators to define how 
the conditions get strung together. So:

+ == AND   
| == OR      
- == AND NOT 

For example:

cond1 = c(:posts) { body =~ '%rails%'}
cond2 = c(:comments) { username == 'ezmobius'}
cond3 = c(:posts) { author_id === (1..4) }
(cond1 + cond2 | cond3).to_sql
=> ["((posts.body LIKE ?) AND (comments.username = ?)) 
    OR (posts.author_id IN (?))", "%rails%", "ezmobius", [1, 2, 3, 4]]

Now you can pass these into a find like this and .to_sql will 
automatically be called on the compound condition:

Post.find :all, :conditions => (cond1 + cond2 | cond3)

You can also build up one compound condition use +=, -= or |=

cond = c{ title =~ '%ruby%' }
cond += c{ description =~ '%ez-where%' }
cond |= c{ user_id === (1..5) }
cond -= c{ pub_date > Time.now.to_s(:db) }
Post.find :all, :conditions => cond
=> ["((title LIKE ?) AND (description LIKE ?)) 
    OR ((user_id IN (?)) AND NOT (pub_date > ?))",
    "%ruby%", "%ez-where%", [1, 2, 3, 4, 5], "2006-06-21 01:17:47"]

Welcome to the new improved ez_where plugin for rails. This plugin is meant 
to be used as a nice ruby like syntax for creating the :conditions part of an 
ActiveRecord::Base.find. We also add the ActiveRecord::Base.ez_find method.
This method takes a block to simplify single and multi table queries.

articles = Article.ez_find(:all, :include => :author) do |article, author|
  article.title =~ "%Foo Title%"
  author.any do
    name == 'Ezra'
    name == 'Fab'
  end 
end

This will produce :conditions => ["article.title LIKE ? AND 
                   (authors.name = ? OR authors.name = ?)",
                   "%Foo Title%", "Ezra", "Fab"]

Basically here is the breakdown of how we map ruby operators 
to SQL operators:

foo == 'bar'           #=> ["foo = ?", 'bar']
foo =~ '%bar'          #=> ["foo LIKE ?", '%bar']
foo <=> (1..5)         #=> ["foo BETWEEN ? AND ?", 1, 5]
id === [1, 2, 3, 5, 8] #=> ["id IN(?)", [1, 2, 3, 5, 8]]
<, >, >=, <= et all will just work like you expect.

There is also the ability to create the conditions in stages so 
you can build up a query:

cond = Caboose::EZ::Condition.new do
  foo == 'bar'
  baz <=> (1..5)
  id === [1, 2, 3, 5, 8]
end

@result = Model.find(:all, :conditions=> cond.to_sql)
#=> ["foo = ? AND baz BETWEEN ? AND ? AND id IN (?)",
     "bar", 1, 5, [1, 2, 3, 5, 8]]

You can even do nested sub conditions. condition will use AND 
by default in the sub condition:

cond = Caboose::EZ::Condition.new :my_table do
  foo == 'bar'
  baz <=> (1..5)
  id === [1, 2, 3, 5, 8]
  condition :my_other_table do
    fiz =~ '%faz%'
  end
end

@result = Model.find(:all, :conditions=> cond.to_sql)
#=> ["my_table.foo = ? AND my_table.baz BETWEEN ? AND ? 
     AND my_table.id IN (?) AND (my_other_table.fiz LIKE ?)",
     "bar", 1, 5, [1, 2, 3, 5, 8], "%faz%"]

You can also build multiple Condition objects and join
them together for one monster find:

cond_a = Caboose::EZ::Condition.new :my_table do
  foo == 'bar'
  condition :my_other_table do
    id === [1, 3, 8]
    foo == 'other bar'
    fiz =~ '%faz%'
  end
end
#=> ["my_table.foo = ? AND (my_other_table.id IN (?) AND my_other_table.foo = ?
       AND my_other_table.fiz LIKE ?)", "bar", [1, 3, 8], "other bar", "%faz%"]

cond_b = Caboose::EZ::Condition.new :my_table do
  active == true
  archived == false
end

#=> ["my_table.active = ? AND my_table.archived = ?", true, false]

composed_cond = Caboose::EZ::Condition.new
composed_cond << cond_a
composed_cond << cond_b
composed_cond << 'fuzz IS NULL'

@result = Model.find(:all, :conditions => composed_cond.to_sql)
#=> ["(my_table.foo = ? AND (my_other_table.id IN (?) AND my_other_table.foo = ? 
      AND my_other_table.fiz LIKE ?)) AND (my_table.active = ? AND my_table.archived = ?)
      AND fuzz IS NULL", "bar", [1, 3, 8], "other bar", "%faz%", true, false]   

You can compose a new condition from different sources:

ar_instance = Author.find(1)

other_cond = Caboose::EZ::Condition.new :my_table do 
  foo == 'bar'; baz == 'buzz'
end

cond = Caboose::EZ::Condition.new
# another Condition
cond.append other_cond
# an array in AR condition format
cond.append ['baz = ? AND bar IS NOT NULL', 'fuzz'], :or
# a raw SQL string
cond.append 'biz IS NULL'
# an Active Record instance from DB or as Value Object
cond.append ar_instance

#(append is aliased to << because of syntax issues 
involving multiple args like :or)

@result = Model.find(:all, :conditions=> cond.to_sql)

#=> ["(my_table.foo = ? AND my_table.baz = ?) OR (baz = ? AND bar IS NOT NULL) 
      AND biz IS NULL AND authors.id = ?", "bar", "buzz", "fuzz", 1]

OK there is also other options for doing subconditions. OR is 
aliased to any and any creates a subcondition that uses OR to 
join the sub conditions:

cond = Caboose::EZ::Condition.new :my_table do
  foo == 'bar'
  any :my_other_table do
    baz === ['fizz', 'fuzz']
    biz == 'boz'
  end
end

@result = Model.find(:all, :conditions=> cond.to_sql)

#=> ["my_table.foo = ? AND (my_other_table.baz IN (?) 
     OR my_other_table.biz = ?)",
     "bar", ["fizz", "fuzz"], "boz"]

OK lets look a bit more at ez_find with a few more complex queries:

# all articles written by Ezra. Here you can use a normal AR object
# in the conditions
# session[:user_id] = 2
ezra = Author.find(session[:user_id])    
@articles = Article.ez_find(:all, :include => :author) do |article, author|
  author << ezra # use AR instance to add condition; uses PK value if set: author.id = ezra.id
end 
#=>["(authors.id = ?)", 2]

# all articles written by Ezra, where he himself responds in comments
@articles = Article.ez_find(:all, :include => [:author, :comments]) do |article, author, comment|
  article.author_id == ezra.id
  comment.author_id == ezra.id   
end
#=>["(articles.author_id = ?) AND (comments.author_id = ?)", 2, 2]

# any articles written by Fab or Ezra
@articles = Article.ez_find(:all, :include => :author) do |article, author|
  author.name === ['Fab', 'Ezra']   
end
#=>["(authors.name IN (?))", ["Fab", "Ezra"]]

# any articles written by Fab or Ezra, using subcondition
@articles = Article.ez_find(:all, :include => :author) do |article, author|
  author.any do
    name == 'Ezra'
    name == 'Fab'
  end  
end
#=>["(authors.name = ? OR authors.name = ?)", "Ezra", "Fab"]

# any articles written by or commented on by Fab, using subcondition
@articles = Article.ez_find(:all, :include => [:author, :comments]) do |article, author, comment|
  article.sub { author_id == 1 }
  comment.outer = :or # set :outer for the comment condition, since it defaults to :and
  comment.sub { author_id == 1 }       
end
#=>["(articles.author_id = ?) OR (comments.author_id = ?)", 1, 1]

@articles = Article.ez_find(:all, :include => [:author, :comments],
                           :outer => { :comments => :or }, 
                           :inner => { :article => :or}) do |article, author, comment|
  article.sub { author_id == 1; author_id == 2 }
  comment.sub { author_id == 1 } 
end
["(articles.author_id = ? OR articles.author_id = ?) OR (comments.author_id = ?)", 1, 2, 1]

And finally you can use any and all with ez_condition like this:

cond = Article.ez_condition { active == true; archived == false }
cond.all { body =~ '%intro%'; body =~ '%demo%' }
cond.any { title =~ '%article%'; title =~ '%first%' }

#=>  ["articles.active = ? AND articles.archived = ? 
      AND (articles.body LIKE ? AND articles.body LIKE ?) 
      AND (articles.title LIKE ? OR articles.title LIKE ?)",
      true, false, "%intro%", "%demo%", "%article%", "%first%"]

Brainspl.at

Random Regurgitations by:
Ezra Zygmuntowicz

New release of ez_where plugin

New ez_where plugin.

Links

Categories

Tags

Archives

Syndicate

Brainspl.at

Random Regurgitations by:Ezra Zygmuntowicz

New release of ez_where plugin

New ez_where plugin.

Links

Categories

Tags

Archives

Syndicate

Random Regurgitations by:
Ezra Zygmuntowicz