On Mar 16, 2011, at 3:02 PM, Tuishimi wrote:
> I have a model that contains mostly ID values that link it to other
> tables. Users want to be able to view the "pretty" data, not IDs.
> That is easy to do (in table-plus, view fields things like
> "xyz.name").
>
> But, they also want to search and sort on those "pretty values." For
> example. FOO_ID points to table FOO, which has a field called BAR
> that contains a nice long string. My table plus field contains a
> reference to "foo.bar" to pull that data into the table view. Let's
> say out of 1000 rows, 10 contain the string "squirrels eat nuts". The
> user wants to be able to type in "squirrels" in the search box and
> find all rows with that value. But what happens is the search fails
> because there is no such field called foo.bar, it's a magical
> reference that rails works hard to fill in for me during the process
> of displaying the page. The REAL value is FOO_ID which is some
> numeric value.
>
> What is the best way to approach this? How do I even begin? I also
> need to sort on these same fields (and I want to sort by the text, not
> the ID values).
Here's a scope I've used in some live apps to search by associated fields:
# TODO: merge this logic back to Hobo core
# TODO: this will die nastily if searching on associations that don't match
table names; need to use reflections
named_scope :search, lambda { |query, *fields|
return { :conditions => '(1=1)' } if query.blank?
words = query.split
args = []
associated = fields.select { |f| f.to_s.include?('.') }.map { |f|
f.to_s.gsub(/\..*$/,'') }
word_queries = words.map do |word|
field_query = '(' + fields.map { |field| field.to_s =~ /^([^.]+)\.(.*)$/
? "(#{$1.pluralize}.#{$2} like ?)" : "(#{@klass.table_name}.#{field} like ?)"
}.join(" OR ") + ')'
args += ["%#{word}%"] * fields.length
field_query
end
if associated.empty?
{ :conditions => [word_queries.join(" AND ")] + args }
else
{ :conditions => [word_queries.join(" AND ")] + args, :include =>
associated.*.to_sym }
end
}
Note that this isn't entirely generic: it assumes that you're using the default
naming conventions for your belongs_to associations, and will generate invalid
SQL if you aren't. That's why it hasn't been added to Hobo yet. :)
On the sorting front, you can already pass a table-qualified reference to
parse_sort_param:
parse_sort_param('users.first_name', 'users.last_name', :registered_on,
:graduated_on, :inactive)
(for the standard order_by Hobo scope). The only gotcha is that the field name
here is NOT the one that table-plus wants (users.first_name vs.
user.first_name), so you'll need to help it out:
<table-plus fields="user.last_name, user.first_name, registered_on,
graduated_on, inactive" sort-columns="&{'user.last_name' => 'users.last_name',
'user.first_name' => 'users.first_name'}">
Yes, it's messy - but it works! Ideally we'd get this working so table-plus
knows how to do this itself.
Hope this helps!
--Matt Jones
--
You received this message because you are subscribed to the Google Groups "Hobo
Users" 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/hobousers?hl=en.
