Second Order Field Search

[Claudio Forain]

Hi. Im using Rails 3.0.9 and Activescaffold with JQuery.
As I read about this here:
http://groups.google.com/group/activescaffold/browse_thread/thread/d96173c2eee1a6?tvc=2&q=+search+second

And Here: https://github.com/activescaffold/active_scaffold/wiki/Search-on-second-order-association

As I tried to do a second order field search, I will tell you my
problem:

I have 3 models: Document, Course and Material:

class Document < ActiveRecord::Base
belongs_to :course
[...]

class Material < ActiveRecord::Base
belongs_to :document
delegate :course, :to => :document, :allow_nil => true
[...]

class Course < ActiveRecord::Base
has_many :documents
[...]


As you can see , Material belongs_to Document belongs_to Course
All I wanted to do is to search a material for something like:
material.document.course.name (in a active record way)

I read about second order search on the links at the beginning of this
e-mail, and did the following:

Put the following line in app/models/material.rb
delegate :course, :to => :document, :allow_nil => true

And the following in app/controllers/materials_controller.rb
[...]
conf.field_search.columns = [:discipline, :document_due_date, :course]
conf.columns << :course
conf.list.columns.add :course
conf.columns[:course].search_sql = 'courses.name'
conf.columns[:document].includes = {:document => :course}
conf.columns[:course].search_ui = :string

The primary problem is that when I click in courses search field and
actually put some value in it, it returns me the following error in
log:

SQL (0.6ms) SELECT COUNT(DISTINCT `materials`.`id`) FROM
`materials` LEFT OUTER JOIN `disciplines` ON `disciplines`.`id` =
`materials`.`discipline_id` LEFT OUTER JOIN `documents` ON
`documents`.`id` = `materials`.`document_id` LEFT OUTER JOIN `users`
ON `users`.`id` = `materials`.`user_id` WHERE (((courses.name LIKE '%a
%')) AND (disciplines.id IN
(2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,24,25)) AND
((`materials`.`document_id` = '76')))
Mysql2::Error: Unknown column 'courses.name' in 'where clause': SELECT
COUNT(DISTINCT `materials`.`id`) FROM `materials` LEFT OUTER JOIN
`disciplines` ON `disciplines`.`id` = `materials`.`discipline_id` LEFT
OUTER JOIN `documents` ON `documents`.`id` = `materials`.`document_id`
LEFT OUTER JOIN `users` ON `users`.`id` = `materials`.`user_id` WHERE
(((courses.name LIKE '%a%')) AND (disciplines.id IN
(2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,24,25)) AND
((`materials`.`document_id` = '76')))
Completed 500 Internal Server Error in 446ms

ActiveRecord::StatementInvalid (Mysql2::Error: Unknown column
'courses.name' in 'where clause': SELECT COUNT(DISTINCT
`materials`.`id`) FROM `materials` LEFT OUTER JOIN `disciplines` ON
`disciplines`.`id` = `materials`.`discipline_id` LEFT OUTER JOIN
`documents` ON `documents`.`id` = `materials`.`document_id` LEFT OUTER
JOIN `users` ON `users`.`id` = `materials`.`user_id` WHERE
(((courses.name LIKE '%a%')) AND (disciplines.id IN
(2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,24,25)) AND
((`materials`.`document_id` = '76')))):


As you can see, the generated query calls for courses.name in a join
that doesnt yeld that table. Am I missing something obvious here?

At first, I thought that it could have something to do with
field_search, but I also tried with the normal search and had the same
issue.

Also this way, I have a problem in list. Although I have the following
line in app/model/course.rb

def to_label
self.name
end

Instead of having the content of the 'name' field in list, it gives me
the instance id, something like: #<Course:0x00000003b0b640>
I know how to correct this with a virtual column in the material model
(something like self.document.course.name), but Im just saying, for
the sake of completion.

Any help will be greatly appreciated. Thanks in advance.

[Cláudio Forain]

Resolvi o problema da busca. Testa lá em materials na porta 3007.  Só a preciss de 2 coisas agora: ajeitar o data picker e tirar aquelas opcoes da busca. Abs

[Cláudio Forain]

Sorry for the above e-mail, reply to all by accident :p

2011/10/10 Cláudio Forain <claudi...@gmail.com>:

[Atastor]

Just to make sure: 

1. You DO have a name column on the courses table?
   
2. Strikes me odd, that you add the course-column only after defining the field-search columns with course; did you tried the reverse order, i.e. add course column first and then definer field-search columns?

Regards

Michael

[Atastor]

Moreover: Whats the advantage of using the delegate over has_many/belongs_to :through?

RM

[Cláudio Forain]

Thanks for the quick reply. People like you that makes the Open Source
Community so awesome. Anyway its incredible how reading your own
problem sometimes gets you to answer your own question.

I just corrected the following line from:

conf.columns[:document].includes = {:document => :course}

To:
conf.columns[:course].includes = {:document => :course}

And everything went smoothly
Anyway, now my problem is tha I would like that search field to be a
:search (with a dropdown) and not a :text (which it is now). So I
edited app/helpers/materials_helper.rb to populate the dropdown:

def course_search_column(record, input_name)
select :record, :course, options_for_select(Material.course),
{:include_blank => as_('-select-')}, input_name
end

Using that, trying to search gives me the following error:

ActionView::Template::Error (undefined method `course' for
#<Class:0x00000003f41520>):
4: <label for="<%= "search_#{column.name}" %>"><%=
column.label %></label>
5: </dt>
6: <dd>
7: <%= active_scaffold_search_for(column) %>
8: </dd>
9: </dl>
10: </li>
app/helpers/materials_helper.rb:4:in `course_search_column'

It shoud work since now Material has the course field delegated for
document, and if I run the application console (using rails console),
I get:

irb(main):001:0> material = Material.find(123)
=> #<Material id: 123, discipline_id: 5, document_id: 75, user_id: 35,
created_at: "2011-09-27 12:29:11", updated_at: "2011-09-28 15:02:11">

irb(main):002:0> material.course
=> #<Course id: 27, name: "1 Medio Regular - Manhã", code: "1 MEDREG-M">

Which means that material.course is a viable "method" . But maybe the
helper is in a "lower" scope than the model, which means that tthe :
delegate method won't work while in the helper. Any help will be
greatly appreciated.

PS: I will try the :has_one :through approach in a while.

2011/10/10 Cláudio Forain <claudi...@gmail.com>:

[Cláudio Forain]

A workmate came with a brilliant solution:

def course_search_column(record, input_name)
select :record, :course, options_for_select(Course.all.collect {
|c| [c.name, c.name] }), {:include_blank => as_('-select-')},
input_name
end

Everything worked as expected. Thanks for everything.

2011/10/10 Cláudio Forain <claudi...@gmail.com>: