I posted an announcement for this once before, but I thought it
useful enough that it would be worth re-posting in the [REUSE]
section. This code adds a ''search'' capability to ActiveRecord
models--in other words, you need simply call ''User.search
"mytext"''
to search all of the text/varchar fields within the users table.
I''ve found this especially handy for the admin section of sites where
the administrator needs a powerful way of filtering records.
# This is an add-on to the ActiveRecord::Base class.
# It allows simple searching to be accomplished by
# using, for example, @movies = Movie.search("text")
module ActiveRecord
class Base
# Allow the user to set the default searchable fields
def self.searches_on(*args)
if not args.empty? and args.first != :all
@searchable_fields = args.collect { |f| f.to_s }
end
end
# Return the default set of fields to search on
def self.searchable_fields(tables = nil, klass = self)
# If the model has declared what it searches_on, then use that...
return @searchable_fields unless @searchable_fields.nil?
# ... otherwise, use all text/varchar fields as the default
fields = []
tables ||= []
string_columns = klass.columns.select { |c| c.type == :text or
c.type == :string }
fields = string_columns.collect { |c| klass.table_name + "." +
c.name }
if not tables.empty?
tables.each do |table|
klass = eval table.to_s.classify
fields += searchable_fields([], klass)
end
end
return fields
end
# Search the database for the given parameters:
# text = a string to search for
# :only => an array of fields in which to search for the text;
# default is ''all text or string columns''
# :except => an array of fields to exclude from the default
searchable columns
# :case => :sensitive or :insensitive
# :include => an array of tables to include in the joins.
Fields that
# have searchable text will automatically be included in the
default
# set of :search_columns.
# :join_include => an array of tables to include in the joins,
but only
# for joining. (Searchable fields will not automatically be
included.)
# :conditions => a string of additional conditions (constraints)
# :offset => paging offset (integer)
# :limit => number of rows to return (integer)
# :order => sort order (order_by SQL snippet)
def self.search(text = nil, options = {})
validate_options([:only, :except, :case, :include,
:join_include, :conditions, :offset,
:limit, :order], options.keys)
case_insensitive = true unless options[:case] == :sensitive
# The fields to search (default is all text fields)
fields = options[:only] || searchable_fields(options[:include])
fields -= options[:except] if not options[:except].nil?
# Now build the SQL for the search if there is text to search for
condition_list = []
unless text.nil?
text_condition = if case_insensitive
fields.collect { |f| "UCASE(#{f}) LIKE ''%#{text.upcase}
%''" }.join " OR "
else
fields.collect { |f| "#{f} LIKE
''%#{text}%''" }.join " OR "
end
# Add the text search term''s SQL to the conditions string
unless
# the text was nil to begin with.
condition_list << "(" + text_condition + ")"
end
condition_list << "#{sanitize_sql(options[:conditions])}"
if
options[:conditions]
conditions = condition_list.join " AND "
conditions = nil if conditions.empty?
includes = (options[:include] || []) + (options[:join_include]
|| [])
includes = nil if includes.size == 0
find :all, :include => includes, :conditions => conditions,
:offset => options[:offset], :limit => options
[:limit], :order => options[:order]
end
end
end
Duane Johnson
(canadaduane)
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails
Duane Johnson wrote:> I posted an announcement for this once before, but I thought it useful > enough that it would be worth re-posting in the [REUSE] section. This > code adds a ''search'' capability to ActiveRecord models--in other > words, you need simply call ''User.search "mytext"'' to search all of > the text/varchar fields within the users table. I''ve found this > especially handy for the admin section of sites where the > administrator needs a powerful way of filtering records. > > >://lists.rubyonrails.org/mailman/listinfo/rails > >Awesome! This is just what I need for my current project. -Matt Margolis
Does someone want to setup a copy of Peter Cooper''s snippets just for this REUSE thing? There are already plenty of Rails entries in the official Snippets site, but I''m wondering if a tailored version would be more appropriate? Matt On Jun 14, 2005, at 10:19 AM, Duane Johnson wrote:> I posted an announcement for this once before, but I thought it > useful enough that it would be worth re-posting in the [REUSE] > section. This code adds a ''search'' capability to ActiveRecord > models--in other words, you need simply call ''User.search "mytext"'' > to search all of the text/varchar fields within the users table. > I''ve found this especially handy for the admin section of sites > where the administrator needs a powerful way of filtering records. > > # This is an add-on to the ActiveRecord::Base class. > # It allows simple searching to be accomplished by > # using, for example, @movies = Movie.search("text") > module ActiveRecord > class Base > # Allow the user to set the default searchable fields > def self.searches_on(*args) > if not args.empty? and args.first != :all > @searchable_fields = args.collect { |f| f.to_s } > end > end > > # Return the default set of fields to search on > def self.searchable_fields(tables = nil, klass = self) > # If the model has declared what it searches_on, then use > that... > return @searchable_fields unless @searchable_fields.nil? > > # ... otherwise, use all text/varchar fields as the default > fields = [] > tables ||= [] > string_columns = klass.columns.select { |c| c.type == :text > or c.type == :string } > fields = string_columns.collect { |c| klass.table_name + "." > + c.name } > > if not tables.empty? > tables.each do |table| > klass = eval table.to_s.classify > fields += searchable_fields([], klass) > end > end > > return fields > end > > # Search the database for the given parameters: > # text = a string to search for > # :only => an array of fields in which to search for the text; > # default is ''all text or string columns'' > # :except => an array of fields to exclude from the default > searchable columns > # :case => :sensitive or :insensitive > # :include => an array of tables to include in the joins. > Fields that > # have searchable text will automatically be included in > the default > # set of :search_columns. > # :join_include => an array of tables to include in the > joins, but only > # for joining. (Searchable fields will not automatically be > included.) > # :conditions => a string of additional conditions (constraints) > # :offset => paging offset (integer) > # :limit => number of rows to return (integer) > # :order => sort order (order_by SQL snippet) > def self.search(text = nil, options = {}) > validate_options([:only, :except, :case, :include, > :join_include, :conditions, :offset, > :limit, :order], options.keys) > case_insensitive = true unless options[:case] == :sensitive > > # The fields to search (default is all text fields) > fields = options[:only] || searchable_fields(options[:include]) > fields -= options[:except] if not options[:except].nil? > > # Now build the SQL for the search if there is text to search > for > condition_list = [] > unless text.nil? > text_condition = if case_insensitive > fields.collect { |f| "UCASE(#{f}) LIKE ''%#{text.upcase} > %''" }.join " OR " > else > fields.collect { |f| "#{f} LIKE ''%#{text}%''" }.join " OR " > end > > # Add the text search term''s SQL to the conditions string > unless > # the text was nil to begin with. > condition_list << "(" + text_condition + ")" > end > condition_list << "#{sanitize_sql(options[:conditions])}" if > options[:conditions] > conditions = condition_list.join " AND " > conditions = nil if conditions.empty? > > includes = (options[:include] || []) + (options > [:join_include] || []) > includes = nil if includes.size == 0 > > find :all, :include => includes, :conditions => conditions, > :offset => options[:offset], :limit => options > [:limit], :order => options[:order] > end > end > end > > Duane Johnson > (canadaduane) > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >Matt Pelletier pelletierm-A1PILTyJ15gXhy9q4Lf3Ug@public.gmane.org _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 6/14/05, Matt Pelletier <pelletierm-A1PILTyJ15gXhy9q4Lf3Ug@public.gmane.org> wrote:> Does someone want to setup a copy of Peter Cooper''s snippets just for this > REUSE thing? There are already plenty of Rails entries in the official > Snippets site, but I''m wondering if a tailored version would be more > appropriate?I personally vote for stricking with the idea of a Wiki. Wiki''s give you the ability to provide documentation, easily update the code, etc. Also I think that the goal with this isn''t just reusable Snippets, but larger modules of code as well. The snippet presentation seems less conducive to this sort of project. That said, I do like the tags, so possibly some sort of merger between Snippets & a Wiki would be the ideal solution.
I''ll second that. Editability is key. We want this to be a living document. Tags are always fun. On Jun 14, 2005, at 10:44 PM, Mike Payson wrote:> On 6/14/05, Matt Pelletier <pelletierm-A1PILTyJ15gXhy9q4Lf3Ug@public.gmane.org> wrote: >> Does someone want to setup a copy of Peter Cooper''s snippets just for >> this >> REUSE thing? There are already plenty of Rails entries in the official >> Snippets site, but I''m wondering if a tailored version would be more >> appropriate? > > I personally vote for stricking with the idea of a Wiki. Wiki''s give > you the ability to provide documentation, easily update the code, etc. > Also I think that the goal with this isn''t just reusable Snippets, but > larger modules of code as well. The snippet presentation seems less > conducive to this sort of project. That said, I do like the tags, so > possibly some sort of merger between Snippets & a Wiki would be the > ideal solution. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Duane Johnson
2005-Jun-15 13:43 UTC
How about a "How the Experts Think" column? (was: Text search for ActiveRecord)
> On 6/14/05, Matt Pelletier <pelletierm-A1PILTyJ15gXhy9q4Lf3Ug@public.gmane.org> wrote: > >> Does someone want to setup a copy of Peter Cooper''s snippets just >> for this >> REUSE thing? There are already plenty of Rails entries in the >> official >> Snippets site, but I''m wondering if a tailored version would be more >> appropriate? >> > > I personally vote for stricking with the idea of a Wiki. Wiki''s give > you the ability to provide documentation, easily update the code, etc. > Also I think that the goal with this isn''t just reusable Snippets, but > larger modules of code as well. The snippet presentation seems less > conducive to this sort of project. That said, I do like the tags, so > possibly some sort of merger between Snippets & a Wiki would be the > ideal solution.On Jun 15, 2005, at 12:13 AM, Ben Jackson wrote:> I''ll second that. Editability is key. We want this to be a living > document. > > Tags are always fun.As long as we publish these re-usable pieces of code around via the mailing list, I''m happy. I want to see how other people are doing things that I may not immediately be interested in (kind of like a subscription to "how the experts think"). There''s a big difference between "having a problem, then finding a solution" and "being aware of several approaches, then picking a solution". In the first case, explicit research is required. In the second, possibilities are immediately available to the mind during the critical step of designing an application''s foundation. This gives me an idea--what if we use the [TIPS] tag (or something just as suitable) as a publishing mechanism? This tag (along with the [REUSE] tag) could become "columns" (as in newspaper columns) in our mailing list. The [TIPS] tag would be where previously published content (either on the mailing list or wiki) are shared with the community, while the [REUSE] tag would be for new and unpublished content. Each "column" could have a maintainer. The maintainer of the [REUSE] column would be responsible for taking the new material and posting it on the wiki, while the maintainer of the [TIPS] column would be responsible for publishing ideas (probably already available somewhere deep in the bowels of the wiki) on a regular basis. If this is something people are interested in, I''d be willing to head something up. I''m particularly interested in beginning a [TIPS] column. Duane Johnson (canadaduane) _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Duane Johnson wrote:> I posted an announcement for this once before, but I thought it useful > enough that it would be worth re-posting in the [REUSE] section. This > code adds a ''search'' capability to ActiveRecord models--in other > words, you need simply call ''User.search "mytext"'' to search all of > the text/varchar fields within the users table. I''ve found this > especially handy for the admin section of sites where the > administrator needs a powerful way of filtering records. >that''s pretty cool. Thanks for sharing! However, I''m concerned with the possibility of a SQL-insertion attack, as it seems you directly insert the search term into the SQL you generate. I''ve to admit I haven''t tested this myself, so maybe I''m wrong, just wanted to be sure you''re aware of it. Sebastian
On Jun 18, 2005, at 11:17 AM, Sebastian Kanthak wrote:> that''s pretty cool. Thanks for sharing! > > However, I''m concerned with the possibility of a SQL-insertion attack, > as it seems you directly insert the search term into the SQL you > generate. I''ve to admit I haven''t tested this myself, so maybe I''m > wrong, just wanted to be sure you''re aware of it. > > Sebastian >You''re absolutely right. I haven''t quite made it a habit of protecting against that. Thanks for point it out--if I get around to fixing it, I''ll post it as a snippet on http://www.bigbold.com/snippets/ Duane Johnson (canadaduane)