Second Order Association Search
chris
I have a simple second-order belongs_to association:
codestatistic belongs_to rule belongs_to priority
Now I want to list the codestatistics with a virtual column "priority"
and allow a search on the priorities (which should yield any entries
with rules of given prio).
We actually managed to come up with a solution, but it is _ugly_ and
not really worth keeping.
Any ideas ?
Cheers,
Chris
Iñaki Baz Castillo
For something similar I create a virtual column "priority" in
codestatistic model:
def priority
self.rule.priority.to_lable
end
However it doesn't allow search based on it.
In fact my case is different as I have:
client has_many :routes
client has_many :addresses, :though => :routes
route has_many :addresses
route belongs_to :client
address belongs_to :route
To list the client of each address I create a virtual column in Address model:
def client
if self.route
self.route.client ? self.route.client.to_label : nil
else
nil
end
end
Same problem as above, I cannot do a search in addresses list based on
client as there is not JOIN relationship between client and addresses
(or perhaps I miss something).
--
Iñaki Baz Castillo
<i...@aliax.net>
chris
the listing is not a problem, we have such a method "priority" in
"codestatistics" - but thanks anyhow.
The search is the problem, as you point out - and I don't see anything
you're missing in your situation either ;-)
We solved the search, but it's really not nice:
1. The codestatistic model gets a fake association (one that is simply
WRONG):
belongs_to :priority, :class_name => 'Priority', :foreign_key =>
'rule_id'
(note that the foreign key is wrong)
2. The codestatistics controller has config.field_search.columns
<< :priority
and also the form_ui is "select" for priority column
3. The codestatistics controller has an overwritten method
"do_search", which adds the proper sql in case the association is
the (fake) association defined above:
search_conditions << "rule_id in (select id from rules where
priority_id = #{value.to_s})"
(we're iterating through the search_params here with the value)
4. The codestatistics helper has an overwritten function to provide
the priorities in a list:
def options_for_association(association, include_all = false)
if (association.name == :priority)
available_records = Priority.find(:all)
available_records ||= []
available_records.collect { |model| [ model.to_label,
model.id ] }
....
With these 4 steps, we got the search working. But this is a lot of
code for something so simple and the fake association is
actually very very ugly.
A cleaner solution would be very welcome. This must be a very common
problem, am I missing something obvious here ?
Cheers,
Chris
On 26 Mai, 21:23, Iñaki Baz Castillo <i...@aliax.net> wrote:
> 2010/5/26 chris <c...@gmx.de>:
Iñaki Baz Castillo
> With these 4 steps, we got the search working. But this is a lot of
> code for something so simple and the fake association is
> actually very very ugly.
Wow, this is a very good way to ensure that the client or your company
will depend on you for future changes :)
(joking...)
> A cleaner solution would be very welcome. This must be a very common
> problem, am I missing something obvious here ?
It's nice to know that I'm not the only one with this tables
relationship problem :)
Sergio Cambra .:: entreCables S.L. ::.
Add the virtual column to search action, define the search_sql as
'priorites.some_field', and set includes in rule column to {:rule =>
:priority}:
config.search.columns << :priority
config.columns[:priority].search_sql = 'priorities.field_name'
config.columns[:rule].includes = {:rule => :priority}
I haven't tried, but I think it should work. If it doesn't work you can try to
add includes defining active_scaffold_includes method in the controller, or
defining joins_for_collection which returns a string with the sql left join.
--
Sergio Cambra .:: entreCables S.L. ::.
Mariana Pineda 23, 50.018 Zaragoza
T) 902 021 404 F) 976 52 98 07 E) ser...@entrecables.com
Iñaki Baz Castillo
> Add the virtual column to search action, define the search_sql as
> 'priorites.some_field', and set includes in rule column to {:rule =>
> :priority}:
>
> config.search.columns << :priority
> config.columns[:priority].search_sql = 'priorities.field_name'
> config.columns[:rule].includes = {:rule => :priority}
Oh my gods! It works!!! amazing!
Iñaki Baz Castillo
Let me say again: amazing !!!!!
Could I suggest it to be included in the wiki please? It's 300% useful
information.
Thanks a lot.
Sergio Cambra .:: entreCables S.L. ::.
You can add a question in FAQ or make a new page called tips or advanced
configuration, with this tip and previous one.
>
>
> --
> Iñaki Baz Castillo
> <i...@aliax.net>
--
Sergio Cambra .:: entreCables S.L. ::.
Iñaki Baz Castillo
> configuration, with this tip and previous one.
Hi, for now I've created a "Tips" page describing the subject of this thread:
http://wiki.github.com/activescaffold/active_scaffold/tips
If you validate its content I would add more content in the next days.
Thanks a lot.
Sergio Cambra .:: entreCables S.L. ::.
> 2010/5/27 Sergio Cambra .:: entreCables S.L. ::. <ser...@entrecables.com>:
> > You can add a question in FAQ or make a new page called tips or advanced
> > configuration, with this tip and previous one.
>
> Hi, for now I've created a "Tips" page describing the subject of this
> thread:
>
> http://wiki.github.com/activescaffold/active_scaffold/tips
>
> If you validate its content I would add more content in the next days.
It's ok, although virtual column can be done with delegate:
class Task < ActiveRecord::Base
delegate :employee, :to => :job, :allow_nil => true
end
--
Sergio Cambra .:: entreCables S.L. ::.
Iñaki Baz Castillo
> class Task < ActiveRecord::Base
> delegate :employee, :to => :job, :allow_nil => true
> end
Seems to be more ellegant :
Iñaki Baz Castillo
>> It's ok, although virtual column can be done with delegate:
>> class Task < ActiveRecord::Base
>> delegate :employee, :to => :job, :allow_nil => true
>> end
>
> Seems to be more ellegant :
Just one limitation (which also occurs with virtual column):
The search for :employee doesn't include "IS NULL" options, even if
:allow_nil => true in the delegate.
Is it possible to implement it?
Iñaki Baz Castillo
> class Task < ActiveRecord::Base
> delegate :employee, :to => :job, :allow_nil => true
> end
I've updated the wiki with your proposal, thanks.
Sergio Cambra .:: entreCables S.L. ::.
I have added another option as string_comparators. Setting null_comparators to
true will add those options. Also, setting it to false remove them in columns
where were added by default, so you can remove them from null columns or
associations which can be null.
>
>
> --
> Iñaki Baz Castillo
> <i...@aliax.net>
--
Sergio Cambra .:: entreCables S.L. ::.
Iñaki Baz Castillo
>> The search for :employee doesn't include "IS NULL" options, even if
>>
>> :allow_nil => true in the delegate.
>>
>> Is it possible to implement it?
>
> I have added another option as string_comparators. Setting null_comparators to
> true will add those options. Also, setting it to false remove them in columns
> where were added by default, so you can remove them from null columns or
> associations which can be null.
wow, great!
Thanks.
Kerry Foley
database view of the parent (rule in this case) and it's associations
(priorities) so that the association data becomes denormalized. Then
the association data can be accessed simply via a virtual column (since
it's now just a column of the parent). You should be able to just set
the search_sql as I show in this thread for virtual columns in a
has_many...through relationship.
http://groups.google.com/group/activescaffold/browse_thread/thread/5851f73519293e1/
Views are read-only though so as mentioned further in the thread you'd
have to do some other gymnastics for full CRUD support. It's probably
not really any cleaner than what you are doing although it does avoid
the "wrong" column definition.
Regards,
Kerry Foley
chris
I had not considered using a view. This works for search + list, as
expected, but we really need to create (write) into
the tables. So, as you already wrote, for full CRUD, we'd need
something else.
Cheers,
Christian
chris
it does not work for me, unfortunately, out of the box. Not sure what
else I need to do, I have not tried what you
suggested last ... (I don't fully understand yet what I need to do
exactly, to be honest).
"If it doesn't work you can try to add includes defining
active_scaffold_includes method in the controller, or
defining joins_for_collection which returns a string with the sql left
join. "
like
config.columns[:priority].form_ui = :select
then, I get nil access problems (see below).
Thanks anyhow - if I succeed with this, I'll definitely post again.
Cheers,
Chris
-------------------
You have a nil object when you didn't expect it!
You might have expected an instance of Array.
The error occurred while evaluating nil.inject
Extracted source (around line #21):
18: <label for="<%= "search_#{column.name}" %>"><%=
column.label %></label>
19: </dt>
20: <dd>
21: <%= active_scaffold_search_for(column) %>
22: </dd>
23: </dl>
24: </li>
Iñaki Baz Castillo
> Sergio,
>
> it does not work for me, unfortunately, out of the box. Not sure what
> else I need to do, I have not tried what you
> suggested last ... (I don't fully understand yet what I need to do
> exactly, to be honest).
>
> "If it doesn't work you can try to add includes defining
> active_scaffold_includes method in the controller, or
> defining joins_for_collection which returns a string with the sql left
> join. "
>
> The search comes up, but with no drop-down box. If I add something
> like
>
> config.columns[:priority].form_ui = :select
>
> then, I get nil access problems (see below).
>
> Thanks anyhow - if I succeed with this, I'll definitely post again.
Hi chris, did you follow the step I added in the wiki? I have it
working right now:
http://wiki.github.com/activescaffold/active_scaffold/tips
Well, our cases are different as mine involves "has_many -> has_many"
while your involves "belongs_to -> belongs_to".
chris
thanks for the tip - I am now very very close it seems.
I found that it works, provided I use your type of searching:
config.columns[:priority].search_ui = :string
config.columns[:priority].options[:string_comparators] = true
It breaks again, if I try to use a drop-down list by using instead:
config.columns[:priority].search_ui = :select
It complains about a nil access:
"You have a nil object when you didn't expect it!
You might have expected an instance of Array.
The error occurred while evaluating nil.inject"
Chris
Iñaki Baz Castillo
> thanks for the tip - I am now very very close it seems.
> I found that it works, provided I use your type of searching:
>
> config.columns[:priority].search_ui = :string
> config.columns[:priority].options[:string_comparators] = true
>
> It breaks again, if I try to use a drop-down list by using instead:
>
> config.columns[:priority].search_ui = :select
>
> It complains about a nil access:
>
> "You have a nil object when you didn't expect it!
> You might have expected an instance of Array.
> The error occurred while evaluating nil.inject"
>
> Probably, I am missing something stupid here ...
Well, I didn't test with :select ui. Let me test it next week to confirm it.
Sergio Cambra .:: entreCables S.L. ::.
> Hi Iñaki,
>
> thanks for the tip - I am now very very close it seems.
> I found that it works, provided I use your type of searching:
>
> config.columns[:priority].search_ui = :string
> config.columns[:priority].options[:string_comparators] = true
>
> It breaks again, if I try to use a drop-down list by using instead:
>
> config.columns[:priority].search_ui = :select
>
> It complains about a nil access:
>
> "You have a nil object when you didn't expect it!
> You might have expected an instance of Array.
> The error occurred while evaluating nil.inject"
You can't use select form_ui in a virtual column as an association column. To
use it with a virtual column you will have to set the options for the select
in the controller. You can do it with a before_filter:
before_filter :set_priority_options, :only => [:new, :create, :edit, :update]
def set_priority_options
config.columns[:priority].options[:options] = Model.all.map {|r| [r.to_label,
r.id]}
end
Or you can use a search form override.
>
> Probably, I am missing something stupid here ...
>
> Chris
>
> > Hi chris, did you follow the step I added in the wiki? I have it
> > working right now:
> > http://wiki.github.com/activescaffold/active_scaffold/tips
> >
> > Well, our cases are different as mine involves "has_many -> has_many"
> > while your involves "belongs_to -> belongs_to".
> >
> > --
> > Iñaki Baz Castillo
> > <i...@aliax.net>
--
Sergio Cambra .:: entreCables S.L. ::.
chris
What I did was to add the before_filter way of doing it:
-----------------
before_filter :set_priority_options, :only => [:show_search]
def set_priority_options
CodestatisticsController.active_scaffold_config.columns[:priority].options[:options]
= Priority.all
end
------------------
I need the before_filter for the show_search, or am I missing
something again ;-) ??
Also, the "config" is not in scope if I add the method outside the
active_scaffold configuration block,
so I need to reference the controller, right ?
Anyhow, with the above, it works, and it is much cleaner than before.
Thanks again for all the help.
Cheers,
Christian
Sergio Cambra .:: entreCables S.L. ::.
> Thanks - this is what was missing.
>
> What I did was to add the before_filter way of doing it:
> -----------------
> before_filter :set_priority_options, :only => [:show_search]
> def set_priority_options
>
> CodestatisticsController.active_scaffold_config.columns[:priority].options[
>:options] = Priority.all
> end
> ------------------
>
> I need the before_filter for the show_search, or am I missing
> something again ;-) ??
Yes, you need the before_filter. You have to set options for each request.
> Also, the "config" is not in scope if I add the method outside the
> active_scaffold configuration block,
> so I need to reference the controller, right ?
config is not in scope outside the block, but you can use active_scaffold_config
without referencing the class.
>
> Anyhow, with the above, it works, and it is much cleaner than before.
> Thanks again for all the help.
>
> Cheers,
> Christian
--
chris
we had before.
To sum up, we had 2 problems:
1. Realise and search for a second order association (codestatistic
belongs_to rule belongs_to priority )
2. The search should be done using search_ui = :select
The solutions are:
1. Realise the virtual column in the model via a delegate method +
configure controller to search by virtual column.
In the model :
delegate :priority, :to => :rule, :allow_nil => true
In the controller:
config.columns << [ :priority]
config.columns[:priority].search_sql = 'priorities.name'
Inaki's tips page : http://wiki.github.com/activescaffold/active_scaffold/tips
2. Configure the controller to search by :select and define a
before_filter to fill the option list.
In the controller:
Priority.all
end
Chris
Iñaki Baz Castillo
Hi, it's great that finally we have this feature 100% working :)
Could you please document your use case in the above tips page? It
would be very useful :)
Thanks a lot.