函数在Postgres Databse上绘制错误，但在Sqlite上未绘制 [英] Function draws Error with Postgres Databse but not on Sqlite

问题描述

因此，将我的代码转移到Postgres中的heroku之后，我的功能之一就是出错。

So after shifting my code to heroku which is in Postgres one of my functions is drawing an error.

def index
    @tutor = Tutor.where(:admin => false)
    @tutor_array = []

    @tutor_array << @tutor.fees_search(params[:fees_search]) if params[:fees_search].present?
    @tutor_array << @tutor.subject_search(params[:subject_search]) if params[:subject_search].present?
    @tutor_array << @tutor.lssubject_search(params[:lssubject_search]) if params[:lssubject_search].present?
    @tutor_array << @tutor.ussubject_search(params[:ussubject_search]) if params[:ussubject_search].present?
    @tutor_array << @tutor.jcsubject_search(params[:jcsubject_search]) if params[:jcsubject_search].present?

    @tutor_array.each do |tutor|
      ids = @tutor.merge(tutor).map(&:id)
      @tutor = Tutor.where(id: ids)
    end
    @tutor = @tutor.sort_by { |tutor| tutor.rating.rating }.reverse
    @tutor = @tutor.paginate(:page => params[:page], :per_page => 2)
end

突出显示的特定行是
ids = @ tutor.merge（tutor）。 map（&：id）

我读到某些调用仅适用于sqlite，而不适用于postgres，例如 LIKE？等。但是我对这里出了什么问题一无所知。

I have read that certain calls works with sqlite and not with postgres such as doing LIKE ? and such. But i am pretty clueless as to whats wrong here.

以下是即将出现的错误

ActiveRecord :: StatementInvalid在TutorsController中#index

ActiveRecord::StatementInvalid in TutorsController#index

PG :: UndefinedFunction：错误：运算符不存在：integer =
字符变化LINE 1：... M tutors INNER JOIN profiles ON
tutors。 id = profile ... ^提示：没有运算符匹配给定名称
和参数类型。您可能需要添加显式类型强制转换。
选择 tutors。*从 tutors INNER JOIN profiles到 tutors。 id
= profiles。 tutor_id INNER JOIN profile_ussubjects在 profiles上。 id = profile_ussubjects。 profile_id
tutors。 admin = $ 1 AND profile_ussubjects。 ussubject_id = $ 2

PG::UndefinedFunction: ERROR: operator does not exist: integer = character varying LINE 1: ...M "tutors" INNER JOIN "profiles" ON "tutors"."id" = "profile... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. : SELECT "tutors".* FROM "tutors" INNER JOIN "profiles" ON "tutors"."id" = "profiles"."tutor_id" INNER JOIN "profile_ussubjects" ON "profiles"."id" = "profile_ussubjects"."profile_id" WHERE "tutors"."admin" = $1 AND "profile_ussubjects"."ussubject_id" = $2

我不知道要搜索什么来尝试解决此问题，因为我什至不知道触发错误的Postgres是什么。

I don't know what to search to try and resolve this since i dont even know what is it about postgres that is triggering the error.

因此希望有人可以指出正确的方向。

So hopefully someone can point me in the right direction.

导师模型如下所示

def self.fees_search(n)
    @profile = Profile.fees(n)
    if @profile.empty?
      return Tutor.none
    else
      @profile.map do |y|
        y.tutor
      end
    end
  end

  def self.subject_search(s)
    @subject = Subject.find_by_name(s)
    unless @subject.nil?
      @subject.tutors 
    end
  end

其他主题搜索与 self.subject_search 相同。

The other subject searches are all the same as self.subject_search.

我认为我得出的问题之一就是在我的 self.subject_search（s）方法中，通过在Rails控制台中对其进行测试，该行 @ subject.tutors 绘制了错误。

I think one of the problems i have deduced would be this, in my self.subject_search(s) method, by testing it in the rails console, the line @subject.tutors is drawing the error.

在我的我运行了 subject = Subject.find_by_name（ English），然后是 subject.tutors ，它引发了错误

In my rails console i ran subject = Subject.find_by_name("English") followed by subject.tutors and it threw the error

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does not exist: integer = character varying
LINE 1: ...M "tutors" INNER JOIN "profiles" ON "tutors"."id" = "profile...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

为什么？对不起，我真的很糟糕与postgres一起使用，我不知道发生了什么以及为什么它与sqlite3一起使用。（我读到sqlite3不如postgres严格

Why though? Im sorry but im really quite bad with postgres and i dont understand whats going on and why it worked with sqlite3. (I read that sqlite3 is not as strict as postgres but that doesn't exactly make it clear for me)

推荐答案

def index
  @tutors = Tutor.where(:admin => false).tap do |scope|
    keys = [:fees_search, :subject_search, :lssubject_search, :ussubject_search, :jcsubject_search]
    keys.each do |key|
      scope.merge!( Tutor.send(key, params[key]) ) if params[key].present? 
    end
  end.paginate(:page => params[:page], :per_page => 2)
end

至少这是一个开始。

这行很成问题：

@tutor = @tutor.sort_by { |tutor| tutor.rating.rating }.reverse

.sort_by 将所有记录从数据库中取出，并用Ruby对其进行排序。相反，您应该进行子选择来获取评级的汇总，并在order子句中使用该评级。 （如果您不确定如何执行此操作，请询问一个新问题。这超出了原始问题的范围。）

.sort_by pulls all the records out of the db and sorts them in Ruby. You should instead be doing a sub-select to fetch an aggregate of the ratings and using that in an order clause. (Please ask a new question if you are unsure about how to do this. Its out of scope of your original question).

这篇关于函数在Postgres Databse上绘制错误，但在Sqlite上未绘制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！