Ruby on Rails：表关系搜索

本文介绍了Ruby on Rails：表关系搜索的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我仍然无法搜索存储在单独表中的技术，其中通过名为projecttechnol的表，技术表（technol）和项目表之间存在关系。

这是我试图搜索一个带有技术（tech1）的项目时的日志。

：{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} 用户加载（0.6ms）用户FROM用户WHERE用户。id= 1 LIMIT 1 技术负载（0.3ms）选择technols SELECTprojects。* FROMprojectsORDER BY client 项目加载（0.6ms）SELECTprojects。* FROMprojectsORDER BY行业项目加载。* FROMprojectsORDER BY role 项目加载（0.4ms）SELECTprojects。* FROMprojectsORDER BY tech CACHE（0.0ms）SELECTprojects ORDER BY tech CACHE（0.0ms）SELECTprojects。* FROMprojectsORDER BY tech CACHE（0.0ms）SELECTprojects $ b CACHE（0.0ms）SELECTprojects。* FROMprojectsORDER BY tech CACHE（0.0ms）SELECTprojects。* FROMprojectsORDER BY tech 项目负载ms）SELECTprojects。* FROMprojectsORDER BY business_div 项目加载（0.5ms）SELECTprojects。* FROMprojectsORDER BY project_owner 项目加载项目。* FROMprojectsORDER BY status （0.9ms）SELECT COUNT（*）FROMprojectsINNER JOINprojecttechnolsONprojecttechnols。project_id=projects。 JOINtechnolsONtechnols。id=projecttechnols。technol_idWHEREtechnols。idIS NULL

这是我的project.rb：

; 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： technol，：through => ：projecttechnols def self.like（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）＃以范围查询开始，范围之后 _projects = Project.scoped ＃然后，对于每个参数，仅适用范围，如果存在如果search_client.present？ _projects = _projects.where ['client LIKE？'，like（search_client）] end 如果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） 如果search_business_div.present？ _projects = _projects.where ['business_div LIKE？'，like（search_business_div）] end 如果search_project_owner.present？ _projects = _projects.where ['project_owner LIKE？'，like（search_project_owner）] end 如果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 $ b如果search_start_date_dd.blank？ todays_date = nil end 如果search_start_date_A.present？或search_start_date_B.present？ _projects = _projects.where ['DATE（start_date）BETWEEN？ AND？'，search_start_date_A，search_start_date_B] end 如果search_start_date_dd.present？ _projects = _projects.where ['DATE（start_date）BETWEEN？ AND？'，search_start_date_dd，todays_date] end 如果search_keywords.present？ _projects = _projects.where ['keywords LIKE？'，like（search_keywords）] end ＃现在你只应用了当前范围。返回结果，并在执行时观察＃查询。 _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< ActiveRecord :: Base attr_accessible：tech has_many：projecttechnols has_many：projects，：through => ：projecttechnols end

Projecttechnol.rb

Project Projecttechnol< ActiveRecord :: Base attr_accessible：project_id，：technol_id belongs_to：technol belongs_to：project end pre>

任何人都可以正确地看到此工作的解决方案。我是新的rails，所以请记住这一点，当试图帮助我。搜索技术字段中的任何内容不会返回任何内容。非常感谢。

解决方案

看起来您的搜索参数中有输入错误：

def self.search（search_client，search_industry，search_role，search_tech_id ...

但您在此处使用 search_techs_id ：

_projects = _projects.joins（：technols）.where（technols.id=> search_techs_ids）

如果你检查你的查询，你可以看到它试图加入两个表与一个NULL。

另一个注意，你的搜索查询有点长， ，我可以建议使用这样的东西：

class Project< ActiveRecord :: Base attr_accessible：edited_first_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 |其中['client LIKE？'，like（client）]} scope：industry，{| industry | where ['industry LIKE？'，like（industry）]} scope：role，{| role |其中['role LIKE？'，like（search_role）]} scope：technologies，{| technology_ids | joins（：technols）.where（technols.id}=> technology_ids）} scope：division，{| division |其中['business_div LIKE？'，like（search_business_div）]} scope：owner，{| owner |其中['project_owner LIKE？'，like（search_project_owner）]} scope：status，{| status |其中['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 |其中['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）; ％＃{文本}％; end def self.paginated_for_index（projects_per_page，current_page） paginate（：per_page => projects_per_page，：page => current_page） end $ b b end

这样，你的搜索方法相当短一点，所有这些参数都进入自己的范围，如果你需要重用它们，方法就已经存在了。

希望这有帮助。

I'm still having trouble searching for technologies that are stored in a separate table, where there is a relationship between the technology table (technol) and Project table through a table called projecttechnol.

This is my log when I try to search for a project with a technology (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

Here is my project.rb:

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 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 < ActiveRecord::Base attr_accessible :tech has_many :projecttechnols has_many :projects, :through => :projecttechnols end

Projecttechnol.rb

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

Can anyone see a solution to this work properly. I am new to rails, so please remember this when attempting to help me. Searching for anything in the technology field returns nothing. Thanks a lot

解决方案

It looks like you've got a typo here in your search parameters:

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

But yet you use search_techs_id here:

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

If you examine your queries you can see that its trying to join the two tables with a NULL.

On another note, your search query is a bit long and tough to read, may I suggest using something like this:

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

This way, your search method is quite a bit shorter, and since you've broken out all these parameters into their own scopes, if you need to reuse them, method is already there.

Hope this helps.