Rails，如何查詢資料庫以獲取相關條目

我正在為一個小型檔案創建一個查詢界面，并且需要了解如何在資料庫中查詢相關條目，例如關鍵字、作者等。

我有一個資源模型，其中每個條目都有一系列列以及一系列關聯，例如作者、關鍵字等。

class Resource < ApplicationRecord

# Associations
  belongs_to :bibliographic_level
  belongs_to :editor
  belongs_to :fund
  belongs_to :land
  belongs_to :parent, class_name: "Resource"
  belongs_to :resource_collocation
  belongs_to :resource_type
  has_many :resource_authors, dependent: :destroy
  has_many :authors, through: :resource_authors
  has_many :items, class_name: "Resource", foreign_key: "parent_id"
  has_many :resource_keywords, dependent: :destroy
  has_many :keywords, through: :resource_keywords
...

在我的控制器中，我為資料庫查詢定義了一個操作

  def opac    
    @resources = Resource.page(params[:page]).order('id ASC').per_page(10).search(params)
  end

在我的模型中，我根據用戶選擇任何標題、作者或關鍵字作為其搜索的掩碼這一事實創建了不同的搜索查詢。

  def self.restricted_search(params)
    if params[:searchAny].present?
      all.where(['title LIKE ? OR description LIKE ?', "%#{params[:searchAny]}%", "%#{params[:searchAny]}%"])
    elsif params[:searchTitle].present?
      where('title LIKE ?', "%#{params[:searchTitle]}%")
    ...
  end

如何查詢資料庫并顯示所有資源，其中作者姓名的一部分包含“Mich”或與某個關鍵字“X”相關聯？

我想像這樣的東西

    elsif params[:searchAuthor].present?
      joins(:resource_authors).where('resource_authors.authors_name = ?', "%#{params[:searchAuthor]}%")
    elsif params[:searchKeyword].present?
      joins(:resource_keywords).where('resource_keywords.keyword_word = ?', "%#{params[:searchKeyword]}%")
  end

The syntax is not clear to me, since in both cases a resource can have multiple authors and I would have to ask for all resources where one author contains a certain string.

How can i get this working?

Thank you in advance.

Addition

When querying for author I get the following output in my log:

  Resource Load (1.7ms)  SELECT `resources`.* FROM `resources` INNER JOIN `resource_authors` ON `resource_authors`.`resource_id` = `resources`.`id` INNER JOIN `authors` ON `authors`.`id` = `resource_authors`.`author_id` INNER JOIN `resource_authors` `resource_authors_authors_join` ON `resource_authors_authors_join`.`author_id` = `authors`.`id` INNER JOIN `resources` `resources_authors` ON `resources_authors`.`id` = `resource_authors_authors_join`.`resource_id` WHERE (resource_type_id != '7') AND (resource_authors_resources_join.author_name = '%test%') ORDER BY id ASC LIMIT 10 OFFSET 0

and this error message:

ActionView::Template::Error (Mysql2::Error: Column 'resource_type_id' in where clause is ambiguous):
     99:             <%= will_paginate @restricted_resources, previous_label: h("<"), next_label: h(">"), class: "pagination" %>

When querying for keywords I get:

  Resource Load (1.7ms)  SELECT `resources`.* FROM `resources` INNER JOIN `resource_keywords` ON `resource_keywords`.`resource_id` = `resources`.`id` WHERE (resource_type_id != '7') AND (resource_keywords.keyword_word = '%test%') ORDER BY id ASC LIMIT 10 OFFSET 0

and the error message:

ActionView::Template::Error (Mysql2::Error: Unknown column 'resource_keywords.keyword_word' in 'where clause'):
     99:             <%= will_paginate @restricted_resources, previous_label: h("<"), next_label: h(">"), class: "pagination" %>

uj5u.com熱心網友回復：

您需要定位正確的表并使用LIKE而不是=.

elsif params[:searchAuthor].present?
  joins(:authors)
     .where('authors.authors_name LIKE ?', "%#{params[:searchAuthor]}%")
elsif params[:searchKeyword].present?
  joins(:keywords)
     .where('keywords.keyword_word LIKE ?', "%#{params[:searchKeyword]}%")
end

標籤：mysql ruby-on-rails activerecord

上一篇：需要如何設定rails記錄器以發送所有錯誤和致命事件的電子郵件？

下一篇：主鍵陣列中鍵的順序是否重要？