From: Ezra Zygmuntowicz <ezmobius@...>
Date: 2006-12-07T05:02:08+09:00
Subject: Re: Lisp comprehensions => SQL


On Dec 5, 2006, at 2:27 PM, Victor Zverok Shepelev wrote:

> From: Logan Capaldo [mailto:logancapaldo@gmail.com]
> Sent: Wednesday, December 06, 2006 12:15 AM
>> To: ruby-talk ML
>> Subject: Re: Lisp comprehensions => SQL
>>
>> On Wed, Dec 06, 2006 at 06:58:49AM +0900, Victor Zverok Shepelev  
>> wrote:
>>> Hi all.
>>>
>>> Random idea, just for fun - using "list comprehensions" for SQL  
>>> queries
>>> generation.
>>>
>>> employees = Table.new(:id, :name, :sec_name, :salary, :age)
>>>
>>> employees.select{|e| e.name == 'John' && e.salary > 50}.sort_by{|e|
>>> e.age}[2,10]
>>>
>>> #generates "select * from Employees where name = 'John' and  
>>> salary > 50
>>> order by age limit 2,10"
>>>
>>> employees.select{|e| e.salary < 150}.count
>>>
>>> #generates "select count(*) from Employees where salary < 150
> [...]
>> The ez_where plugin for rails does this.
>> http://brainspl.at/articles/2006/01/30/i-have-been-busy
>
> Yeah, seems to be close to my proposition.
> What I dislike in the solution (and in Mongoose, which repeats the  
> solution)
> that conditions is NOT plain Ruby, but custom DSL.
>
> #ez_where:
>
> articles = Article.ez_find(:all, :include => :author) do |article,  
> author|
>   article.title =~ "%Foo Title%"
>   author.any do
>     name == 'Ezra'
>     name == 'Fab'
>   end
> end
>
> #my idea, thus lacking knowledge about tables relationships:
>
> (articles+authors).select{|article, author|
>    article.author_id == author.id &&
>    article.title =~ "%Foo Title%" &&
>    (author.name == 'Ezra' || author.name == 'Fab')
> }
>
> Seems to read as "just ruby".
> What do you think about?


	There are quite a few more features in ez-where then the example  
above. ez-where is only a where clause generator. It doesnt ourput  
complete selects. But there are many ways to use it besides the above:

You can make it write the :include statements for you

articles = Article.find_where(:all) do |article|
   article.title =~ 'Lorem%'
   article.author.name == 'Ezra'
   article.comments.user.name == 'Fab'
end

The above just creates the following hashes and passes them to a  
normal AR#find

:include => { :author => {}, :comments => { :user => {} } }
:conditions => ["(articles.title LIKE ? AND (authors.name = ?) AND  
(users.name = ?))", "Lorem%", "Ezra", "Fab"]


But you can use it without active record at all to generate where  
clauses for you:


 >> a = c(:authors) { name == 'jimbob' }
<snip>

 >> b = c(:articles) { title =~ '%ruby%' }
<snip>

 >> (a + b).to_sql
# => ["(authors.name = ?) AND (articles.title LIKE ?)", "jimbob", "% 
ruby%"]

 >> (a | b).to_sql
# => ["(authors.name = ?) OR (articles.title LIKE ?)", "jimbob", "% 
ruby%"]

 >> (a - b).to_sql
# => ["(authors.name = ?) AND NOT (articles.title LIKE ?)", "jimbob",  
"%ruby%"]

 >> c = c(:tags) {any_of(:name, :comment, :metadata) =~ '%ruby%'}
<snip>

 >> c.to_sql
# => ["(tags.name LIKE ? OR tags.comment LIKE ? OR tags.metadata  
LIKE ?)", "%ruby%", "%ruby%", "%ruby%"]

 >> (a + b | c).to_sql
# => ["((authors.name = ?) AND (articles.title LIKE ?)) OR (tags.name  
LIKE ? OR tags.comment LIKE ? OR tags.metadata LIKE ?)", "jimbob", "% 
ruby%", "%ruby%", "%ruby%", "%ruby%"]


Make sure you get this from the rubyforge repository and not the old  
version. This plugin is heavily test driven.

svn://rubyforge.org//var/svn/ez-where

Cheers
-- Ezra Zygmuntowicz 
-- Lead Rails Evangelist
-- ez@engineyard.com
-- Engine Yard, Serious Rails Hosting
-- (866) 518-YARD (9273)