On Sep 16, 3:15 pm, Michael Granger <[email protected]> wrote:
> Joe Van Dyk wrote:
> > I often need to do something like:
>
> > def search *keywords
> > select * from something where column ilike '%first-keyword%' OR
> > column ilike '%second-keyword%' ......;
> > end
>
> > So, the query would contain an arbitrary amount of OR'd conditions,
> > depending on how many keywords were passed in to the search function.
>
> > I'm having trouble getting sequel to do the right syntax. If I wanted
> > AND instead of ORs, I could do something like:
>
> > query = DB[:table]....
> > keywords.each { |k| query = query.filter("column ilike ?, "%#{k}%" }
>
> The Dataset#or method can add ORs:
>
> query = keywords.inject( DB[:table] ) do |query,kw|
> pat = '%' + kw
> if query.send( :simple_select_all? )
> query.filter( :column.ilike(pat) )
> else
> query.or( :column.ilike(pat) )
> end
> end
>
> It's violating the 'protected' visibility of the
> Dataset#simple_select_all? method, but I'm not sure how to know when to
> use #or and when to use #filter otherwise. Suggestions, Jeremy?
>
> If you want to avoid the #send, you could always just unshift the first
> keyword, start your query with that, and inject the rest.
Aha!
query.filter(keywords.map { |k| ["products.name ilike ?", "%#{k}
%"] }.sql_or)
Given keywords ["this", "rocks", "a lot"], this results in:
... AND (('products.name ilike ?' = '%this%') OR ('products.name
ilike ?' = '%rocks%') OR ('products.name ilike ?' = '%a lot%'))
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
