Query Mixin by Duane Johnson

Rails
1

Hello

At the start of October, Duane J. announced the Query mixin plugin
on
this list. The code was attached to the announcement email.
Unfortunately,
I’ve not been able to locate the attached code. Goggle has not helped
me
this time. Could somebody forward it to me?

Thanks in advance
Harvey

This e-mail has been scanned for all viruses by MessageLabs.

2

http://article.gmane.org/gmane.comp.lang.ruby.rails/24689/match=query

Followups:
http://article.gmane.org/gmane.comp.lang.ruby.rails/24712/match=query
http://article.gmane.org/gmane.comp.lang.ruby.rails/24754/match=query+plugin

3

Please find attached archive

4

Ezra,

What do you thing about adding a patch for QBE query by example as in
Duane
query mixin?

5

Leon-

Sounds fine to me. But can you explain what a QBE is to me? I

haven’t looked at Duane’s code in a long while so I’m foggy on what
that was. I’m all for adding good features to this thing though.

Cheers-
-Ezra

On Jan 4, 2006, at 5:43 PM, Leon L. wrote:

Thanks in advance
activerecord so you can do the following:
nik <= 23
as a plugin with the new syntax but its in a great state to use as

–
First they laugh at you, then they ignore you, then they fight you.
Then you win.
– Mahatma Karamchand Gandhi

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

-Ezra Z.
WebMaster
Yakima Herald-Republic Newspaper
[email protected]
509-577-7732

6

On Jan 4, 2006, at 8:37 AM, Harvey B. wrote:

Thanks in advance
Harvey

Harvey-

I have been working on something similar that may be a bit easier to

use. You will find the plugin attached to this email for you to play
with. Essentially it allows you to build the :conditions part of the
find query using ruby idioms. Also there is a method added to
activerecord so you can do the following:

#allows you to use the following block form of find_with_conditions:
   Model.find_with_conditons( :all, :limit => ..., :order

=> … ) do
foo == ‘bar’ # exact value
baz <=> (1…100) # ‘between’
woo =~ ‘substri%’ # ‘like’
fiz < 10 # lt, gt, leq, geq, etc., should all
“just work”
nik <= 23
kez >= 65
end
#=> :conditions will look like this:
[“foo = ? AND baz BETWEEN ? AND ? AND woo LIKE ? AND fiz < ? AND nik
<= ? AND kez >= ?”,
“bar”, 1, 100, “substri%”, 10, 23, 65]

I have a few more additions to the syntax to do before i release it

as a plugin with the new syntax but its in a great state to use as
is. Feedback is appreciated.

Cheers-
-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
[email protected]

7

Ezra,

Query By Example. A search method for databases in which the user fills
out
the form to build sql conditions.

Duane does it something like this

users = User.query(params[:user])

(imagine that params[:user] is a hash that looks something like:
{ :first_name => “”, :last_name => “”, :phone => “555-1234”, :email
=> “” } )

forms "where phone = “555-1234”

Your where class could have something like this

were.query_by_example(params[:user]) (whatever!)

one could then then use where.conditions

Hope that i am clear…

regards,
Leon

8

Ezra

Thanks for the code. I’ve just started playing with it and so far so
good.
I do have just one question. Is it possible to use the OR operator
rather
than AND between two conditions?

Thanks
Harvey

On 4/1/06 18:49, “Ezra Z.” [email protected] wrote:

   Model.find_with_conditons( :all, :limit => ..., :order

["foo = ? AND baz BETWEEN ? AND ? AND woo LIKE ? AND fiz < ? AND nik
Yakima Herald-Republic
http://lists.rubyonrails.org/mailman/listinfo/rails
This e-mail has been scanned for all viruses by MessageLabs.

9

Harvey-

That is something I am wrestling with right now. Currently you can

use either all AND’s or all OR’s to join the condition statements
together. I haven’t been able to come up with a syntax I like for
allowing nested AND’s and OR’s. I also added the ability to do IN (?)
queries. Here is what it looks like right now to use the different
booleans:

Model.find_with_conditons( :all, :limit => …, :order => … ) do
foo == ‘bar’ # [“foo = ?”, 'bar]
id <=> (1…100) # [“id BETWEEN ?AND ?”, 1,100]
woo =~ ‘substri%’ # [“woo LIKE ?”, ‘substri%’]
user_id === (1…4) # [“user_id IN (?)”, [1,2,3,4]]
fiz < 10 # lt, gt, leq, geq, etc., should all “just work”
end

Notice that you can do IN (?) clauses now with the === operator. As

it is above it will join all the statements with AND’s. In order to
join the statements with OR’s instead you add :or => true to the
options hash like this:

Model.find_with_conditons( :all, :or => true, :limit => …, :order
=> … ) do
foo == ‘bar’ # [“foo = ?”, 'bar]
baz <=> (1…100) # [“baz BETWEEN ?AND ?”, 1,100]
woo =~ ‘substri%’ # [“woo LIKE ?”, ‘substri%’]
id === (1…4) # [“id IN (?)”, [1,2,3,4]]
fiz < 10 # lt, gt, leq, geq, etc., should all “just work”
end

Any suggestions for how the syntax should look in order to support

arbitrary nesting of AND’s & OR’s would be much appreciated. I am
having a hard time coming up with a syntax thats not fugly :wink: You can
get the latest version from svn here:

http://OpenSVN.csie.org/ezra/rails/plugins/ez_where

Feedback is most welcome.

Cheers-
-Ezra

On Jan 10, 2006, at 8:52 AM, Harvey B. wrote:

activerecord so you can do the following:
nik <= 23
is. Feedback is appreciated.

http://lists.rubyonrails.org/mailman/listinfo/rails

-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
[email protected]

10

Leon-

Sounds good, I will look into adding this feature. Have a look at my

other recent post in this thread to see a few new features I just added.

Cheers-
Ezra

On Jan 10, 2006, at 11:37 AM, Leon L. wrote:

{ :first_name => “”, :last_name => “”, :phone => “555-1234”, :email
Hope that i am clear…
On 1/4/06, Ezra Z. [email protected] wrote: Leon-

Ezra,

#allows you to use the following block form of  
   end

–
Yakima Herald-Republic Newspaper

–
First they laugh at you, then they ignore you, then they fight you.
Then you win.
– Mahatma Karamchand Gandhi

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
[email protected]

11

On Jan 10, 2006, at 2:32 PM, Ezra Z. wrote:

foo == ‘bar’ # [“foo = ?”, 'bar]

Any suggestions for how the syntax should look in order to support
arbitrary nesting of AND’s & OR’s would be much appreciated. I am
having a hard time coming up with a syntax thats not fugly :wink: You
can get the latest version from svn here:

http://OpenSVN.csie.org/ezra/rails/plugins/ez_where

Feedback is most welcome.

Ezra, I think what you’ve done here is amazing. I love to see Ruby
used in such a creative way. As for suggestions, here’s something
that may work:

Model.find_with_conditons( :all, :limit => …, :order => … ) do
published == true
any do
id == 1
name == ‘duane’
end
end

=> “published = 1 AND (id = 1 OR name = ‘duane’)”

Or its opposite:
Model.find_with_conditons( :all, :or => true, :limit => …, :order
=> … ) do
name == ‘duane’
all do
published == true
created_at > 1.day.ago
end
end

=> "name = ‘duane’ OR (published = 1 AND created_at > '2006-01-08

15:30:00’)"

This would also provide nesting possibilities:

Model.find_with_conditons( :all, :or => true, :limit => …, :order
=> … ) do
name == ‘duane’
all do
published == true
created_at > 1.day.ago
any do
id == 1
lastname == ‘johnson’
end
end
end

=> "name = ‘duane’ OR (published = 1 AND created_at > '2006-01-08

15:30:00’ AND (id = 1 OR lastname = ‘johnson’))"

There’d be some tricky stuff in the implementation, no doubt, but do
you think that’s possible?

Duane J.
(canadaduane)
http://blog.inquirylabs.com/

12

On Jan 10, 2006, at 2:33 PM, Duane J. wrote:

to use the different booleans:
As it is above it will join all the statements with AND’s. In
end

end
end
published == true
There’d be some tricky stuff in the implementation, no doubt, but
do you think that’s possible?

Duane J.
(canadaduane)
http://blog.inquirylabs.com/

Ahh nice, I like it! Definitely will require some serious hackage to

get it going but hey, that’s what gets me up in the morning :wink: Thanks
for that. I have been wrestling with trying to find some syntax that
doesn’t suck and this looks good.

The code is already using two classes that implement method_missing

and overide the operators that trade off between each other to buidl
the final output. Maybe if I add one more class to the front of the
chain that manages each subsection and finally gets the results back
from the other two classes and joins them together with the proper
parens and booleans. Just thinking out loud here but it might not be
as hard as i initially thought.

Thanks Duane-
-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
[email protected]