Column drill-down filtering

[tra...@gmail.com]

I've just discovered this plug-in and it's 99% of what I've been
looking for; it's just missing one little feature I'm hoping somebody
here has already tackled and/or can give me a hint as to how to
implement myself.

What I need is for the values in certain columns to be hyperlinked, and
for those links to cause the list to filter itself based on that column
value when clicked.

For example, say I have a column labeled "Clients" and one of the
clients is "Acme". When I click on "Acme", the list should
refresh/rerender leaving only the lines with "Acme" as the client.

On top of that, the feature needs to be aggregate, so if I _also_ have
a column named "Representatives" and from that column I click on "Tom"
I should now only see line items with "Acme" as the client and "Tom" as
the representative.

Finally, clicking on a value in an already-filtered column should undo
the filtering. So if I'm already drilled down to Acme clients and I
again click on Acme in the clients column, the clients should become
un-filtered and all of the clients should reappear in the list.

I hope I've made it clear what I'm looking for :-) I've already got
this functionality in an old personal Java project but I'm trying to
port it to Rails without reinventing too many wheels.

Thanks to anybody that can help.

[scott_b...@hotmail.com]

I do a similar deal which you can try. In your model add the column
link_to:

@scaffold_columns = [
...
AjaxScaffold::ScaffoldColumn.new(self, { :name => "members", :label =>
"Members", :eval => "link_to(group.members.size, url_for(:controller =>
'users', :action => 'list', :group_id => group.id))", :sortable =>
false}),
...
]

In your controller capture the search param:

def component
@show_wrapper = true if @show_wrapper.nil?
@sort_sql =
Group.scaffold_columns_hash[current_sort(params)].sort_sql rescue nil
@sort_by = @sort_sql.nil? ?
"#{Group.table_name}.#{Group.primary_key} asc" : @sort_sql + " " +
current_sort_direction(params)

# New lines here
search_params = {:order => @sort_by, :per_page => default_per_page}
search_params.merge!(:conditions => ["group_id = ?",
params[:group_id]]) if params[:group_id]
@paginator, @groups = paginate(:groups, search_params)

render :action => "component", :layout => false
end

For it to be cumulative you'll just have to pull each param and merge
it into the conditions clause.

Hope that helps (I reckon there's 50 ways to do it, btw, so if somebody
has a better, please post).

[tra...@gmail.com]

Excellent! Thanks. This works like a charm and gave me exactly the
foot in the door I needed. I'm trying to work out the other two pieces
now (aggregation and reversal). I'll post them here if/when I get them
working :-)

[tra...@gmail.com]

It ain't pretty, and it ain't DRY, but I've got the "undo" part of the
drilldown working on top of what you showed me. Unfortunately I've
misplaced my pastie log-in URL and it's MIA from the channel now so
I've got to post anonymously :-( http://pastie.caboo.se/7385

[scott_b...@hotmail.com]

If you can live with the restriction that columns and params have to
have the same name you could replace this:

# TODO: DRY this up!
!! if params[:user_id]
!! search_params.merge!(:conditions => ["user_id = ?",
params[:user_id]])
!! end
!! if params[:status_id]
!! search_params.merge!(:conditions => ["status_id = ?",
params[:status_id]])
!! end

with this:

[:user_id, :status_id].each { |field|
if params[field]
search_params.merge!(:conditions => ["#{field.to_s} = ?",
params[field]])
end
}

[tra...@gmail.com]

Excellent. Thanks again. FYI I also DRY'd up the big nasty eval block
in the model:

def Project.drill_down_link( model_name, column_name )
# TODO: reverse merge a default parameters hash to aggregate
drilldowns
<<EOF
url_for_params = { :controller => 'projects', :action => 'list' }
url_for_params.merge!( :#{column_name} => project.#{column_name} )
unless params[:#{column_name}]
link_to(
#{model_name}.find( project.#{column_name} ).name,
url_for( url_for_params ) )
EOF
end

[tra...@gmail.com]

Hmm, on second thought that doesn't appear to be working. If I'm
reading it correctly, the "conditions" is getting overwritten for each
match in the array.

[tra...@gmail.com]

I think what I want is something like this http://pastie.caboo.se/7423
but I've botched the syntax somehow (see the error message at the
bottom).

[scott_b...@hotmail.com]

You're right, forgot to give you one of my personal extensions to
handle the AND of the conditions (similar to how its done in the
framework). I get around those with a mixin to the hash object:

module CoreExtensions
module Hash
def merge_sql!(the_new = {}, key = :conditions)
should_merge = the_new[key] && self[key]
if should_merge
raise "merge_sql! requires a sanitize_sql() block" unless
block_given?
self.merge!(key => [self[key], the_new[key]].collect{ |sql|
"( %s )" % yield(sql) }.join(" AND "))
else
self.merge!(the_new)
end
end
end
end

That's called like this (yup, a little ugly, but otherwise no reuse of
sanitize_sql):

opts.merge_sql!(:conditions => ["root_id = ?", id]) {|sql|
sanitize_sql(sql)}

Once again, sure there's probably a better way.

[tra...@gmail.com]

Thanks again. I dind't grok your code so I invented my own :-) I've
got the whole thing working [from my original post] and I've blogged
about it at
http://trak3r.blogspot.com/2006/08/drill-down-filtering-for-ajaxscaffold.html