Ruby on Rails：表关系搜索 [英] Ruby on Rails: Table relationship search

问题描述

我仍然无法搜索存储在单独表中的技术，其中通过名为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.

                        
这篇关于Ruby on Rails：表关系搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！