Function draws Error with Postgres Databse but not on Sqlite

Question

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

The particular line that gets highlighted is this ids = @tutor.merge(tutor).map(&:id)

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.

Here's the error that is coming up

ActiveRecord::StatementInvalid in TutorsController#index

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

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.

Here's what the tutor model looks like

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

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

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.

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.

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)

operator does not exist: integer = character varying You are comparing (or joining) a varchar field to an integer field. — wildplasser
– wildplasser, Commented Oct 26, 2016 at 10:19
So if i cant do @tutor.merge(tutor) which seems to be whats triggering the error (the merging) is there a way around it? Or do i have to restructure my controller code in an entirely different manner? — angkiki
– angkiki, Commented Oct 26, 2016 at 12:11
Your current code is probably horrible in terms of performance - you should be building a scope instead of fetching a bunch of ids separately and adding them together. Can you add the Tutor model to the question so that we can see what all those search methods do? — max
– max, Commented Oct 26, 2016 at 12:18
I've added it in. To be honest i dont actually need it to function in the exact same manner anymore since i no longer need to be able to pass all different subject searches simultaneously. I'm more concerned with the reason behind the error coming up though — angkiki
– angkiki, Commented Oct 26, 2016 at 13:07

max · Accepted Answer · 2016-10-26 12:33:28Z

-1

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

Thats a start at least.

This line is pretty problematic:

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

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

answered Oct 26, 2016 at 12:33

max

103k15 gold badges113 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

angkiki Over a year ago

Doesn't seem to be working. I get ActiveRecord::StatementInvalid in Tutors#Index and then it shows

PG::UndefinedFunction: ERROR:  operator does not exist: integer = character varying LINE 1: ...M "tutors" INNER JOIN "profiles" ON "tutors"."id" = "profile...

although the line of code highlighted that is throwing the error is the <%= will_paginate @tutor %> that is in my index view. Btw why does .sort_by pull all the records out if its working with the @tutor instance variable? I've also updated my code to include my model if that helps. Thank you!

max Over a year ago

Quite frankly I'm not quite sure where to begin helping you. Your search functions should be returning scopes that can be chained together so that you can fetch the data effectively without a zillion database queries. What your are doing may work (kind of) but its extremely inefficient.

max Over a year ago

The will_paginate error is most likely because you should be passing it a collection of records and you're passing it something else like an array of ids.

max Over a year ago

.sort_by loads all the records from the database and then loops through them as an array. If you have assigned the collection to a instance variable or not is irrelevant.

angkiki Over a year ago

could you point me in the right direction with regards to the search functions? i could always start from scratch but that was sort of what i arrived at which i guess as pointed out is not optimal. doing find_by_name is "wrong"? should i be using .where instead?

|

Collectives™ on Stack Overflow

Function draws Error with Postgres Databse but not on Sqlite

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related