Rails, how to query database for associated entries-CodePudding

I am creating a query interface for a small archive and need to understand how I can query the database for associated entries such as keywords, authors and so on.

I have a Resource model with a series of columns for each entry as well as a series of associations such as authors, keywords, etc.

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
...

In my Controller I define an action for the database query

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

In my model I created different search enquiries based on the fact that the user chooses any, title, author or keyword as mask for his search.

  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

How can I query the database and get all resources displayed where part of the author name contains "Mich" or which has a certain keyword "X" associated?

I suppose something such as this

    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" %>

CodePudding user response：

You're need to target the correct table and use LIKE instead of =.

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