ActiveRecord::StatementInvalid（PG :: SyntaxError：ERROR：语法错误，或在“。”附近）

Question

我不确定为什么我的查询在本地主机上可以工作，但在服务器上失败了。当我尝试创建一个路由到QuizzesController#new的测验时会出现这种情况。

# GET /quizzes/new
  def new
    @quiz = current_user.quizzes.new
  end

这是查询语句：

SELECT COUNT(*) FROM "questions" INNER JOIN "question_categories" ON "question_categories"."question_id" = "questions"."id" WHERE "questions"."deleted_at" IS NULL AND (`question_categories`.`category_id` IN (87,1))

(1.0ms)  ROLLBACK
Completed 500 Internal Server Error in 58ms (ActiveRecord: 13.4ms)

我遇到了如下错误。

ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near "." LINE 1: ...s"."deleted_at" IS NULL AND (`question_categories`.`category...

quiz.rb 在创建之前，我会运行build_parts，它应该随机选择问题并将其放入测验中。 class Quiz < ActiveRecord::Base 属于 :user 属于 :subject has_many :quiz_categories 通过 :quiz_categories, has_many :categories has_many :quiz_parts

  accepts_nested_attributes_for :categories
  accepts_nested_attributes_for :quiz_parts

  validates :user, :subject, :number_of_questions, presence: true
  validates :number_of_questions, numericality: { only_integer: true, greater_than_or_equal_to: 1 }

  before_create :build_parts
  before_save :set_completed_at, if: -> { completeness == 100.00 }

  def completeness
    answerable_quiz_parts = 0
    quiz_parts.each do |q_part|
      answerable_quiz_parts += 1 if q_part.answerable.answers.present?
    end
    quiz_parts.joins(:choice).count.to_f * 100 / answerable_quiz_parts
  end

  def score
    quiz_parts.joins(:choice).where('choices.correct = ?', true).count { |qp| qp.choice.correct? }
  end

  private

  # select random questions
  def build_parts
    category_ids = self.categories.map(&:id)
    question_pool = Question.joins(:question_categories).where('`question_categories`.`category_id` IN (?)', category_ids)

    #self.number_of_questions = [number_of_questions, question_pool.size].min

    puts question_pool.size

    if number_of_questions > question_pool.size
      errors.add(:number_of_questions, 'is too high. Please select a lower question count or increase category selections')
      return false
    end

    number_of_questions.times do |i|
      question_pool.inspect
      self.quiz_parts << question_pool[i].quiz_parts.new
      question_pool[i].question_parts.each do |question_part|
        self.quiz_parts << question_part.quiz_parts.new
      end
    end
  end

  def set_completed_at
    self.completed_at = Time.zone.now
  end

end

quizzes_controller.rb

class QuizzesController < ApplicationController
  before_action :authenticate_user!
  before_action :set_quiz, only: [:show, :edit, :update, :destroy]

  # GET /quizzes
  # GET /quizzes.json
  def index
    @quizzes = current_user.quizzes.order(created_at: :desc)
  end

  # GET /quizzes/1
  # GET /quizzes/1.json
  def show
  end

  # GET /quizzes/new
  def new
    @quiz = current_user.quizzes.new
  end

  # GET /quizzes/1/edit
  def edit

  end

  # POST /quizzes
  # POST /quizzes.json
  def create
    @quiz = current_user.quizzes.new(quiz_create_params)
    respond_to do |format|
      if @quiz.save
        format.html { redirect_to edit_quiz_path(@quiz), notice: 'Quiz was successfully created.' }
        format.json { render :show, status: :created, location: @quiz }
      else
        format.html { render :new }
        format.json { render json: @quiz.errors, status: :unprocessable_entity }
      end
    end
  end

  # PATCH/PUT /quizzes/1
  # PATCH/PUT /quizzes/1.json
  def update
    respond_to do |format|
      if @quiz.update(quiz_update_params)
        format.html { redirect_to @quiz, notice: 'Quiz was successfully updated.' }
        format.json { render :show, status: :ok, location: @quiz }
      else
        format.html { render :edit }
        format.json { render json: @quiz.errors, status: :unprocessable_entity }
      end
    end
  end

  # DELETE /quizzes/1
  # DELETE /quizzes/1.json
  def destroy
    @quiz.destroy
    respond_to do |format|
      format.html { redirect_to quizzes_url, notice: 'Quiz was successfully destroyed.' }
      format.json { head :no_content }
    end
  end

  private
  # Use callbacks to share common setup or constraints between actions.
  def set_quiz
    @quiz = current_user.quizzes.find(params[:id])
  end

  # For quiz setup
  def quiz_create_params
    params.require(:quiz).permit(:subject_id, :number_of_questions, category_ids: [])
  end

  # For quiz answering
  def quiz_update_params
    params.require(:quiz).permit(quiz_parts_attributes: [:id, choice_attributes: [:id, :content, :answer_id, :_destroy]])
  end
end

schema.rb:

ActiveRecord::Schema.define(version: 20150726180000) do

  create_table "admins", force: :cascade do |t|
    t.string   "email"
    t.string   "encrypted_password",     default: "", null: false
    t.string   "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.integer  "sign_in_count",          default: 0,  null: false
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.string   "current_sign_in_ip"
    t.string   "last_sign_in_ip"
    t.string   "confirmation_token"
    t.datetime "confirmed_at"
    t.datetime "confirmation_sent_at"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  add_index "admins", ["confirmation_token"], name: "index_admins_on_confirmation_token", unique: true
  add_index "admins", ["email"], name: "index_admins_on_email", unique: true
  add_index "admins", ["reset_password_token"], name: "index_admins_on_reset_password_token", unique: true

  create_table "answers", force: :cascade do |t|
    t.integer  "number"
    t.text     "content"
    t.boolean  "correct",         default: false, null: false
    t.integer  "answerable_id"
    t.string   "answerable_type"
    t.datetime "created_at",                      null: false
    t.datetime "updated_at",                      null: false
  end

  add_index "answers", ["answerable_type", "answerable_id"], name: "index_answers_on_answerable_type_and_answerable_id"

  create_table "categories", force: :cascade do |t|
    t.string   "name"
    t.integer  "subject_id"
    t.integer  "category_id"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
  end

  add_index "categories", ["category_id"], name: "index_categories_on_category_id"
  add_index "categories", ["subject_id"], name: "index_categories_on_subject_id"

  create_table "choices", force: :cascade do |t|
    t.string   "content"
    t.integer  "quiz_part_id"
    t.integer  "answer_id"
    t.boolean  "correct"
    t.datetime "created_at",   null: false
    t.datetime "updated_at",   null: false
  end

  add_index "choices", ["answer_id"], name: "index_choices_on_answer_id"
  add_index "choices", ["quiz_part_id"], name: "index_choices_on_quiz_part_id"

  create_table "ckeditor_assets", force: :cascade do |t|
    t.string   "data_file_name",               null: false
    t.string   "data_content_type"
    t.integer  "data_file_size"
    t.integer  "assetable_id"
    t.string   "assetable_type",    limit: 30
    t.string   "type",              limit: 30
    t.integer  "width"
    t.integer  "height"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  add_index "ckeditor_assets", ["assetable_type", "assetable_id"], name: "idx_ckeditor_assetable"
  add_index "ckeditor_assets", ["assetable_type", "type", "assetable_id"], name: "idx_ckeditor_assetable_type"

  create_table "levels", force: :cascade do |t|
    t.string   "name"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "question_categories", force: :cascade do |t|
    t.integer  "question_id"
    t.integer  "category_id"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
  end

  add_index "question_categories", ["category_id"], name: "index_question_categories_on_category_id"
  add_index "question_categories", ["question_id"], name: "index_question_categories_on_question_id"

  create_table "question_parts", force: :cascade do |t|
    t.text     "content"
    t.string   "type"
    t.integer  "question_id"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
    t.datetime "deleted_at"
  end

  add_index "question_parts", ["deleted_at"], name: "index_question_parts_on_deleted_at"
  add_index "question_parts", ["question_id"], name: "index_question_parts_on_question_id"

  create_table "questions", force: :cascade do |t|
    t.text     "content"
    t.string   "type"
    t.integer  "level_id"
    t.integer  "subject_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.datetime "deleted_at"
    t.string   "source"
  end

  add_index "questions", ["deleted_at"], name: "index_questions_on_deleted_at"
  add_index "questions", ["level_id"], name: "index_questions_on_level_id"
  add_index "questions", ["subject_id"], name: "index_questions_on_subject_id"

  create_table "quiz_categories", force: :cascade do |t|
    t.integer  "category_id"
    t.integer  "quiz_id"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
  end

  add_index "quiz_categories", ["category_id"], name: "index_quiz_categories_on_category_id"
  add_index "quiz_categories", ["quiz_id"], name: "index_quiz_categories_on_quiz_id"

  create_table "quiz_parts", force: :cascade do |t|
    t.integer  "quiz_id"
    t.datetime "created_at",      null: false
    t.datetime "updated_at",      null: false
    t.integer  "answerable_id"
    t.string   "answerable_type"
  end

  add_index "quiz_parts", ["answerable_type", "answerable_id"], name: "index_quiz_parts_on_answerable_type_and_answerable_id"
  add_index "quiz_parts", ["quiz_id"], name: "index_quiz_parts_on_quiz_id"

  create_table "quizzes", force: :cascade do |t|
    t.integer  "user_id"
    t.datetime "completed_at"
    t.datetime "created_at",          null: false
    t.datetime "updated_at",          null: false
    t.integer  "subject_id"
    t.integer  "number_of_questions"
  end

  add_index "quizzes", ["subject_id"], name: "index_quizzes_on_subject_id"
  add_index "quizzes", ["user_id"], name: "index_quizzes_on_user_id"

  create_table "subjects", force: :cascade do |t|
    t.string   "name"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "users", force: :cascade do |t|
    t.string   "email",                  default: "", null: false
    t.string   "encrypted_password",     default: "", null: false
    t.string   "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.integer  "sign_in_count",          default: 0,  null: false
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.string   "current_sign_in_ip"
    t.string   "last_sign_in_ip"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  add_index "users", ["email"], name: "index_users_on_email", unique: true
  add_index "users", ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true

end

- Henry

---

你可以发布本地和服务器数据库架构吗？ - adamliesko

去掉所有表格和列名中的双引号。 - Abhik Chakraborty

你在开发中使用的是Postgres还是SQLite？这是您自己编写的查询吗？否则，请添加触发查询的代码以获得更有用的答案。 - max

我在开发中使用SQLite，我没有手写查询语句，所以不确定如何更改它。 - Henry

Answer 1

我认为您使用了错误的引号：

SELECT COUNT(*) ....... (`question_categories`.`category_id` IN (87,1))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

使用" "代替``

更新:

是的，我在你的测验模型中正确地指出了你使用了错误的引号：

def build_parts
  category_ids = self.categories.map(&:id)
  question_pool = Question.joins(:question_categories).where('`question_categories`.`category_id` IN (?)', category_ids)
                                                                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

请将它修复为：

 def build_parts
   category_ids = self.categories.map(&:id)
   question_pool = Question.joins(:question_categories).where('"question_categories"."category_id" IN (?)', category_ids)
                                                               ^^^^^^^^^^^^^^^^^^^^^

- Philidor

---

谢谢你的回复！在开发中我使用sqlite，我没有手写查询语句，所以不确定如何更改它。 - Henry

另外，根据错误信息显示，您正在使用 postgres。 (PG :: SyntaxError：ERROR：语法错误在或附近“。” PG 是 postgres。 - Roman Kiselenko