ruby-on-rails - 如何让我的数据库查询更有效率？

Question

我正在尝试部署我的 Rails 应用程序，但我的 herokuapp 在运行时出错。但是在我的 ubuntu 中，当我运行我的 Rails 应用程序时，它运行得很顺利。

如何提高查询效率？我认为这与查询有关，这就是我的 herokuapp 出错的原因。

在我的events_controller

@forw = Forward.select(:user_id).where(:doc_id => params[:id])
@users = User.where.not(:id => "#{@user.id}").where.not(:id => @forw)
@sent = User.where.not(:id => "#{@user.id}").where(:id => @forw)
#line #41
@status = Forward.select(:status).where(user_id: @sent.ids).where(doc_id: params[:id])

它会导致这个错误

2018-07-13T06:06:59.283437+00:00 app[web.1]: F, [2018-07-13T06:06:59.283386 #4] FATAL -- : [4391812e-d824-4a3d-b87f-28a231dc54cc] ActiveRecord::StatementInvalid (PG::UndefinedFunction: ERROR:  operator does not exist: bigint = text
2018-07-13T06:06:59.283438+00:00 app[web.1]: LINE 1: ...sers" WHERE ("users"."id" != $1) AND "users"."id" IN (SELECT...
2018-07-13T06:06:59.283440+00:00 app[web.1]: ^
2018-07-13T06:06:59.283441+00:00 app[web.1]: HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
2018-07-13T06:06:59.283443+00:00 app[web.1]: : SELECT "users"."id" FROM "users" WHERE ("users"."id" != $1) AND "users"."id" IN (SELECT "forwards"."user_id" FROM "forwards" WHERE "forwards"."doc_id" = $2)):
2018-07-13T06:06:59.283486+00:00 app[web.1]: F, [2018-07-13T06:06:59.283441 #4] FATAL -- : [4391812e-d824-4a3d-b87f-28a231dc54cc]
2018-07-13T06:06:59.283544+00:00 app[web.1]: F, [2018-07-13T06:06:59.283498 #4] FATAL -- : [4391812e-d824-4a3d-b87f-28a231dc54cc] app/controllers/events_controller.rb:41:in `forward'

在我的logins_controller

@forwards = Forward.select(:doc_id).where(:user_id => "#{@user.id}").where(:status => 'FORWARDED')
@received = Document.where(:id => @forwards)

它会导致这个错误

2018-07-13T06:09:42.579716+00:00 app[web.1]: I, [2018-07-13T06:09:42.579651 #4]  INFO -- : [f55de38f-3744-4cec-a2c4-5317efa61462] Completed 500 Internal Server Error in 17ms (ActiveRecord: 7.9ms)
2018-07-13T06:09:42.580566+00:00 app[web.1]: F, [2018-07-13T06:09:42.580498 #4] FATAL -- : [f55de38f-3744-4cec-a2c4-5317efa61462]
2018-07-13T06:09:42.580619+00:00 app[web.1]: F, [2018-07-13T06:09:42.580563 #4] FATAL -- : [f55de38f-3744-4cec-a2c4-5317efa61462] ActionView::Template::Error (PG::UndefinedFunction: ERROR:  operator does not exist: bigint = text
2018-07-13T06:09:42.580621+00:00 app[web.1]: LINE 1: ...uments".* FROM "documents" WHERE "documents"."id" IN (SELECT...
2018-07-13T06:09:42.580623+00:00 app[web.1]: ^
2018-07-13T06:09:42.580625+00:00 app[web.1]: HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
2018-07-13T06:09:42.580627+00:00 app[web.1]: : SELECT "documents".* FROM "documents" WHERE "documents"."id" IN (SELECT "forwards"."doc_id" FROM "forwards" WHERE "forwards"."user_id" = $1 AND "forwards"."status" = $2)):
2018-07-13T06:09:42.580877+00:00 app[web.1]: F, [2018-07-13T06:09:42.580818 #4] FATAL -- : [f55de38f-3744-4cec-a2c4-5317efa61462]     190:                             %th Description
2018-07-13T06:09:42.580880+00:00 app[web.1]: [f55de38f-3744-4cec-a2c4-5317efa61462]     191:                             %th Options
2018-07-13T06:09:42.580881+00:00 app[web.1]: [f55de38f-3744-4cec-a2c4-5317efa61462]     192:                         %tbody{:align => "center"}
2018-07-13T06:09:42.580883+00:00 app[web.1]: [f55de38f-3744-4cec-a2c4-5317efa61462]     193:                           - @received.each do |document|
2018-07-13T06:09:42.580885+00:00 app[web.1]: [f55de38f-3744-4cec-a2c4-5317efa61462]     194:                             %tr
2018-07-13T06:09:42.580886+00:00 app[web.1]: [f55de38f-3744-4cec-a2c4-5317efa61462]     195:                               %td= document.name
2018-07-13T06:09:42.580888+00:00 app[web.1]: [f55de38f-3744-4cec-a2c4-5317efa61462]     196:                               %td= document.author_name
2018-07-13T06:09:42.580923+00:00 app[web.1]: F, [2018-07-13T06:09:42.580872 #4] FATAL -- : [f55de38f-3744-4cec-a2c4-5317efa61462]
2018-07-13T06:09:42.580973+00:00 app[web.1]: F, [2018-07-13T06:09:42.580924 #4] FATAL -- : [f55de38f-3744-4cec-a2c4-5317efa61462] app/views/logins/dashboard.html.haml:193:in `_app_views_logins_dashboard_html_haml__4338535734076852815_41972620'

在我的folders_controller

@doc_type = params[:doc_type]
@doc_id = Document.select(:id).distinct.where(doc_type: params[:doc_type])
#line #48
@doc_year = Event.find_by_sql("SELECT DISTINCT strftime('%Y', event_date) as dates FROM events e JOIN documents d ON  e.event_date = d.date_modified where d.doc_type = '#{@doc_type}'")

它会导致这个错误

2018-07-13T06:08:01.901994+00:00 app[web.1]: I, [2018-07-13T06:08:01.901927 #4]  INFO -- : [28c1cc51-c582-4fcb-8665-62410aa26ef5] Completed 500 Internal Server Error in 7ms (ActiveRecord: 3.9ms)
2018-07-13T06:08:01.902614+00:00 app[web.1]: F, [2018-07-13T06:08:01.902545 #4] FATAL -- : [28c1cc51-c582-4fcb-8665-62410aa26ef5]
2018-07-13T06:08:01.902675+00:00 app[web.1]: F, [2018-07-13T06:08:01.902609 #4] FATAL -- : [28c1cc51-c582-4fcb-8665-62410aa26ef5] ActiveRecord::StatementInvalid (PG::UndefinedFunction: ERROR:  function strftime(unknown, date) does not exist
2018-07-13T06:08:01.902677+00:00 app[web.1]: LINE 1: SELECT DISTINCT strftime('%Y', event_date) as dates FROM eve...
2018-07-13T06:08:01.902679+00:00 app[web.1]: ^
2018-07-13T06:08:01.902681+00:00 app[web.1]: HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2018-07-13T06:08:01.902683+00:00 app[web.1]: : SELECT DISTINCT strftime('%Y', event_date) as dates FROM events e JOIN documents d ON  e.event_date = d.date_modified where d.doc_type = 'Letter'):
2018-07-13T06:08:01.902721+00:00 app[web.1]: F, [2018-07-13T06:08:01.902671 #4] FATAL -- : [28c1cc51-c582-4fcb-8665-62410aa26ef5]
2018-07-13T06:08:01.902773+00:00 app[web.1]: F, [2018-07-13T06:08:01.902724 #4] FATAL -- : [28c1cc51-c582-4fcb-8665-62410aa26ef5] app/controllers/folders_controller.rb:48:in `folder_year'

模型

class Event < ActiveRecord::Base
  belongs_to :document, optional: true
  validates :event_date, presence: true
  validates :event_type, presence: true
  validates :doc_id, presence: true
end

class Forward < ApplicationRecord
  validates :user_id, presence: true
  validates :doc_id, presence: true
end

class Document < ActiveRecord::Base
  has_one :author
  has_many :event

  validates :name, presence: true
  validates :doc_type, presence: true
  validates :description, presence: true
  validates :location, presence: true
end

class Request < ApplicationRecord
  def self.from_omniauth(auth)
    where(provider: auth.provider, uid: auth.uid).first_or_initialize.tap do |user|
      user.provider = auth.provider
      user.uid = auth.uid
      user.first_name = auth.info.first_name
      user.last_name = auth.info.last_name
      user.emailadd = auth.info.email
      user.save!
    end
  end

  validates :emailadd,
   uniqueness: true, presence: true,
   format: {
     message: 'domain must be @up.edu.ph', with: /\A[\w+-.]+@up.edu.ph\z/i}
  validates :first_name, presence: true
  validates :last_name, presence: true
end

Answer 1

让我们从logins_controller开始

@forwards = Forward.select(:doc_id).where(:user_id => "#{@user.id}").where(:status => 'FORWARDED')
@received = Document.where(:id => @forwards)

1. 如果您没有在文档的where 之外的任何地方使用@forwards，那么您不必使用实例变量来定义它。事实上，您根本不必定义它。使用 joins 将多个表连接成一个表并对其调用查询。
2. 我不确定您为什么要这样做:"#{@user.id}" 而不是 @user.id。
3. 您可以将多个条件放在一个where
中

改进的代码:

@received = Document.joins("LEFT JOIN forwards on forwards.doc_id = documents.id").where(forwards: {user_id: @user.id, status: 'FORWARDER'})