Ruby on Rails: búsqueda de relaciones de mesa

Todavía tengo problemas para buscar tecnologías que se almacenan en una tabla separada, donde existe una relación entre la tabla de tecnología (technol) y la tabla de proyectos a través de una tabla llamada projecttechnol.

Este es mi registro cuando bash buscar un proyecto con una tecnología (tech1).

Parameters: {"utf8"=>"✓", "client"=>"", "industry"=>"", "role"=>"", "technols"=>{"id"=>["", "1", ""]}, "business_div"=>"", "project_owner"=>"", "start_date_dd"=>"", "start_date_A"=>"", "start_date_B"=>"", "status"=>"", "keywords"=>"", "per_page"=>"10"} User Load (0.6ms) SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 Technol Load (0.3ms) SELECT "technols".* FROM "technols" Project Load (0.5ms) SELECT "projects".* FROM "projects" ORDER BY client Project Load (0.6ms) SELECT "projects".* FROM "projects" ORDER BY industry Project Load (0.4ms) SELECT "projects".* FROM "projects" ORDER BY role Project Load (0.4ms) SELECT "projects".* FROM "projects" ORDER BY tech CACHE (0.0ms) SELECT "projects".* FROM "projects" ORDER BY tech CACHE (0.0ms) SELECT "projects".* FROM "projects" ORDER BY tech CACHE (0.0ms) SELECT "projects".* FROM "projects" ORDER BY tech CACHE (0.0ms) SELECT "projects".* FROM "projects" ORDER BY tech CACHE (0.0ms) SELECT "projects".* FROM "projects" ORDER BY tech Project Load (0.6ms) SELECT "projects".* FROM "projects" ORDER BY business_div Project Load (0.5ms) SELECT "projects".* FROM "projects" ORDER BY project_owner Project Load (0.7ms) SELECT "projects".* FROM "projects" ORDER BY status (0.9ms) SELECT COUNT(*) FROM "projects" INNER JOIN "projecttechnols" ON "projecttechnols"."project_id" = "projects"."id" INNER JOIN "technols" ON "technols"."id" = "projecttechnols"."technol_id" WHERE "technols"."id" IS NULL 

Aquí está mi proyecto.rb:

 class Project  :projecttechnols def self.like(text); "%#{text}%"; end def self.search(search_client, search_industry, search_role, search_tech_id, search_business_div, search_project_owner, search_status, search_start_date_dd, search_start_date_A, search_start_date_B, search_keywords) # start with a scoped query, to apply more scopes on it afterwards _projects = Project.scoped # then, for each of the parameters, apply the scope only if present if search_client.present? _projects = _projects.where ['client LIKE ?', like(search_client)] end if search_industry.present? _projects = _projects.where ['industry LIKE ?', like(search_industry)] end if search_role.present? _projects = _projects.where ['role LIKE ?', like(search_role)] end _projects = _projects.joins(:technols). where("technols.id" => search_techs_ids) if search_business_div.present? _projects = _projects.where ['business_div LIKE ?', like(search_business_div)] end if search_project_owner.present? _projects = _projects.where ['project_owner LIKE ?', like(search_project_owner)] end if search_status.present? _projects = _projects.where ['status LIKE ?', like(search_status)] end todays_date = DateTime.now.to_date if !search_start_date_A.blank? or !search_start_date_B.blank? search_start_date_A = Date.parse(search_start_date_A).strftime("%Y-%m-%d") search_start_date_B = Date.parse(search_start_date_B).strftime("%Y-%m-%d") todays_date = nil search_start_date_dd = nil end if search_start_date_dd.blank? todays_date = nil end if search_start_date_A.present? or search_start_date_B.present? _projects = _projects.where [' DATE(start_date) BETWEEN ? AND ?', search_start_date_A, search_start_date_B] end if search_start_date_dd.present? _projects = _projects.where ['DATE(start_date) BETWEEN ? AND ?', search_start_date_dd, todays_date] end if search_keywords.present? _projects = _projects.where ['keywords LIKE ?', like(search_keywords)] end # now you have applied only the present scopes. return the result, and watch # the query as it executes. _projects end def self.paginated_for_index(projects_per_page, current_page) paginate(:per_page => projects_per_page, :page => current_page) end end 

Technol.rb:

 class Technol  :projecttechnols end 

Projecttechnol.rb

 class Projecttechnol < ActiveRecord::Base attr_accessible :project_id, :technol_id belongs_to :technol belongs_to :project end 

¿Alguien puede ver una solución a este trabajo correctamente. Soy nuevo en los Rails, así que recuerda esto cuando trates de ayudarme. La búsqueda de cualquier cosa en el campo de la tecnología no devuelve nada. Muchas gracias

Parece que tienes un error tipográfico aquí en tus parámetros de búsqueda:

  def self.search(search_client, search_industry, search_role, search_tech_id... 

Pero todavía usas search_techs_id aquí:

  _projects = _projects.joins(:technols).where("technols.id" => search_techs_ids) 

Si examina sus consultas, puede ver que está intentando unir las dos tablas con un valor NULL.

En otra nota, su consulta de búsqueda es un poco larga y difícil de leer, puedo sugerirle usar algo como esto:

 class Project < ActiveRecord::Base attr_accessible :edited_first_name, :edited_last_name, :first_name, :last_name, :business_div, :client, :customer_benifits, :edited_date, :end_date, :entry_date, :financials, :industry, :keywords, :lessons_learned, :project_name, :project_owner, :role, :start_date, :status, :summary, :tech_id validates_presence_of :business_div, :client, :customer_benifits, :end_date, :financials, :industry, :keywords, :lessons_learned, :project_name, :project_owner, :role, :start_date, :status, :summary#, :tech has_many :projecttechnols has_many :technols, :through => :projecttechnols scope :client, { |client| where ['client LIKE ?', like(client) ] } scope :industry, { |industry| where ['industry LIKE ?', like(industry)] } scope :role, { |role| where ['role LIKE ?', like(search_role)] } scope :technologies, { |technology_ids| joins(:technols).where("technols.id}" => technology_ids) } scope :division, { |division| where ['business_div LIKE ?', like(search_business_div)] } scope :owner, { |owner| where ['project_owner LIKE ?', like(search_project_owner)] } scope :status, { |status| where ['status LIKE ?', like(search_status)] } scope :keywords, { |keywords| where ['keywords LIKE ?', like(keywords)] } scope :started_before, { |date| where ['start_date <= ?',date] } scope :started_after, { |date| where ['start_date >= ?',date] } def self.search_fields [:client,:industry,:role,:technologies,:division,:owner,:status,:started_before,:started_after,:keywords] end def self.search(params) search_fields.inject(scoped) do |scopes,key| params[key].present? ? scopes.send(key,params[key]) : scopes end end def self.like(text); "%#{text}%"; end def self.paginated_for_index(projects_per_page, current_page) paginate(:per_page => projects_per_page, :page => current_page) end end 

De esta manera, su método de búsqueda es un poco más corto, y ya que ha desglosado todos estos parámetros en sus propios ámbitos, si necesita reutilizarlos, el método ya está allí.

Espero que esto ayude.

Supongo que en el controlador obtienes el valor correcto del formulario.

Este es tu params :

 {"utf8"=>"✓", "client"=>"", "industry"=>"", "role"=>"", "technols"=>{"id"=>["", "1", ""]}, "business_div"=>"", "project_owner"=>"", "start_date_dd"=>"", "start_date_A"=>"", "start_date_B"=>"", "status"=>"", "keywords"=>"", "per_page"=>"10"} 

por lo tanto, la lista de id de tecnologías seleccionadas es params["technols"]["id"] que es ["", "1", ""] . Si esto está destinado, entonces tienes que eliminar los espacios en blanco para ello

 tech_ids = params["technols"]["id"].reject(&:blank?) # removes empty strings 

Y pasarlo en la función de búsqueda. Si no es lo que pretendía, verifique el código que utiliza para crear el cuadro de selección, ya que debe devolver las ID correctas