Ruby on Rails: Table relationship search

问题:

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

回答1:

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.

回答2:

I suppose that in the controller you get the correct value from the form.

This is your 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"}

so the list of selected technols id is params["technols"]["id"] that is ["", "1", ""]. If this is intended, then you have to remove blanks for it

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

and pass it in the search function. If it's not what intended, then check the code you use to create the select box, because it must return the correct IDs