Skip to content

Search queries containing a period break the generated SQL in some versions of Active Record #14

Closed
tommeier opened this Issue Oct 12, 2011 · 33 comments
@tommeier

Hi,

Eager loading associations seems to cause an error:

SomeSearch.pgsearch_scope('SomethingWith.com')

This works. However, once you add eager loading, will cause it to fail :

SomeSearch.pgsearch_scope('SomethingWith.com').includes(:some_association)

ActiveRecord::StatementInvalid: PGError: ERROR:  column "pg_search_rank" does not exist
LINE 1: ...SomethingWith.com' || ' ''' || ':*')))) ORDER BY pg_search_...

Has anyone else faced this issue?

@nertzy
nertzy commented Oct 12, 2011

Interesting. I think it's because we add a select() to the scope to get the pg_search_rank to be attached to each record, and calling includes() overrides PgSearch's select().

@nertzy
nertzy commented Oct 12, 2011

If you reverse the scope calls does it have the same error?

In other words, try this:

SomeSearch.includes(:some_association).pgsearch_scope('SomethingWith.com')
@nertzy
nertzy commented Oct 12, 2011

I think that Stack Overflow bug is actually unrelated. Over there, Rails is generating the SQL when you pass a hash into where(), but within PgSearch, we are generating SQL directly and passing a string to where().

I think that our bug is more related to this issue (on the Pivotal Tracker project) https://www.pivotaltracker.com/story/show/9808733

@tommeier

Hi,

Sorry didn't see the updates, yeah its the same regardless of the order of placement of includes.

@tommeier

But its not an ambigious error like pivotal tracker error suggests, whenever a period is added to the query, the whole query changes and loses the appended pg_search_rank.

@tommeier

Is it an option to put an includes writer on the scopes? That would be similar to how its passed in gems like Sunspot, with a dsl on the model, kinda makes sense.

So if we had access to an includes hash, in a similar way to column names here :
https://github.com/Casecommons/pg_search/blob/master/lib/pg_search/configuration/association.rb#L20

Then includes gets applied by the scope instead of handling externally before/after.

@tcocca
tcocca commented Oct 13, 2011

I am also seeing this issue:

Say I have the following:

class Property << ActiveRecord::Base

  has_many :open_houses

  include PgSearch
    pg_search_scope :search_by_keywords, 
      :against => :description, 
      :using => {
        :tsearch => {
          :dictionary => "english", 
          :any_word => true
        }
      }

end

And I try and do a query like so:

Property.search_by_keywords('brownstone').paginate(:conditions => ["(opt_out = false) AND (price >= ? AND price <= ?) AND (open_houses.start_time BETWEEN ? AND ?)", 50000, 350000, '2011-10-13 00:00:00', '2011-10-16 23:59:59'], :includes => [:open_houses], :page => 1, :per_page => 25)

it is generating the following query:


SELECT * FROM (
  SELECT DISTINCT ON ("properties".id) "properties".id, pg_search_rank AS alias_0, "properties"."id" AS alias_1 
  FROM "properties"  
  LEFT OUTER JOIN "open_houses" ON open_houses.property_id = properties.id   
  WHERE (
    (
      (properties.opt_out = false) AND 
      (properties.price >= 50000) AND (properties.price <= 350000) AND 
      (open_houses.start_time BETWEEN '2011-10-13 00:00:00' AND '2011-10-16 23:59:59') AND 
    ) AND 
    (
      ((to_tsvector(E'english', coalesce("properties"."description", ''))) @@ (to_tsquery(E'english', E''' ' || E'brownstone' || E' ''')))
    ) 
  ) AS id_list 
  ORDER BY id_list.alias_0 DESC, id_list.alias_1  
  LIMIT 25 OFFSET 0
)

with the error:

ActiveRecord::StatementInvalid (PGError: ERROR: column "pg_search_rank" does not exist
LINE 1: ...CT DISTINCT ON ("properties".id) "properties".id, pg_search_...

Is there any way to go it to not try and add the ranking when doing eager loading?

~ Tom

@tommeier

Tom - Remove the period in your conditions : 'open_houses.start_time', and i bet it will work.

@tommeier

@nertzy : definitely think this is related to this : rails/rails#950 , so looks like a rails thing not a pg_search thing.

@tcocca
tcocca commented Oct 14, 2011

@tommeier i changed my :includes => [:open_houses] to a :joins => "LEFT JOIN open_houses on open_houses.property_id = properties.id" and the query worked

unforuntately I also want the open houses to eager load so I have both the :joins hard coded definition as well of the :includes => [:open_houses] and its now working ...

@tommeier

@tcocca yup, pretty much the same here, for now, written some breaking specs in active record, but the fix is eluding me.

You can also setup your own eager loading, effectively doing the same as an includes :

ActiveRecord::IdentityMap.use do
       properties = Property.search_by_keywords('brownstone').paginate(:conditions => ["(opt_out = false) AND (price >= ? AND price <= ?) AND (open_houses.start_time BETWEEN ? AND ?)", 50000, 350000, '2011-10-13 00:00:00', '2011-10-16 23:59:59'], :page => 1, :per_page => 25)

       includes = OpenHouse.where(:property_id => properties).all

end
@tcocca
@nertzy
nertzy commented Oct 22, 2011

@tcocca I discovered that someone is maintaining a fork of pg_search for Rails 2.3 support.

https://github.com/bloomfire/pg_search

I can't vouch for the quality of their efforts, but it's worth a try if you think you'll be stuck on Rails 2.3 for a while.

@avinasha
avinasha commented Nov 3, 2011

I am also encountering this error on eager loading using includes... any solution for this problem?

@avinasha
avinasha commented Nov 3, 2011

I observe that the error for me is happening when count is called on the query.. else the query generated works fine...

@tommeier
tommeier commented Nov 3, 2011

No fix yet : rails/rails#950 , aiming for rails 3.2

@daniel2d2art

Using RoR 3.2.1, pg_search 0.4.1

"PG::Error: ERROR: column "pg_search_rank" does not exist ..." eager loading join query

No solutions for this???? work around on 3.2.1?? seems that solution around references are include in unreleased RoR 4.0
But we can do anythink in 3.2.x versions?

--- following the problem I found this results:

This works properly

my_full_text_search_scope("Pedro").includes(:addresses)

But this not

my_full_text_search_scope("Pedro.com").includes(:addresses)

Note the dot at search string. If a dot is included the query "pg_search_rank" does not exist apears.
The dot is not involved on left side field condition, is affected on right value side condition.
If full text search without dot in strings search working ok.

Perhaps a previous text dot deleting for search will be a solution, but I'm looking for emails.

@daniel2d2art

I'm looking for more elegant solutions about it, but not way actually (RoR 3.2.1).
I'm think about workaround, based on avoid RoR (dot) problem, some like this:

    query = query.gsub(".", "' || chr(46) || '") 
    my_pg_full_text_search(query).includes(:foo, :bar)

But some sanitizing removes the hack.
Please Nertzy can you tell us how override the code to avoid the sanitization for this hack. here? https://github.com/Casecommons/pg_search/blob/master/lib/pg_search/scope_options.rb#L29
Can you provide best way to do?
Thks!

@tommeier
tommeier commented Mar 4, 2012

daniel2d2art : the fix is this : rails/rails#950 no easy solution will work.

I got around it by doing an IdentityMap. See all the comments further up.

@daniel2d2art

But IdentityMap don't be stable, as far I read about it.
I saw your work around proposal before, but I don't understand how implement it in more horizontal approach, because I use FTS with includes in several use cases, not in only one method or model.
The problem appears when FTS query includes a (dot), I'm trying to replace with ' || chr(46) || ' but it's sanitized before.

@tommeier
tommeier commented Mar 4, 2012

Yes, you;re not going to be able to get around it, without changing rails itself, and its not an easy task.

So currently your 3 options are IdentityMap or wait for Rails 4, or attempt to patch in the 'references' changes (good luck).

IdentityMap is stable, just used in moderation, we're using it successfully in several large apps.

@nertzy
nertzy commented Mar 5, 2012

FYI, looks like IdentityMap will be removed from Rails soon:

rails/rails#5261

@tommeier
tommeier commented Mar 5, 2012

Yup in Rails4 , but then the includes/references fix will be in (also Rails 4) : rails/rails@4c4760a

@daniel2d2art

I still believe that in cases where the problem only is produced for the inclusion of a dot in the query, is more easy replace the dot, as aproach #14 (comment)
Netrzy need your appointments for best how override to do it .

Is possible that Rails issue rails/rails#950, affects in other use cases, but I think that the dot query inclusion produces the major problems when Rails builds the query.

@nertzy
nertzy commented Aug 10, 2012

Looks like this has been biting people for a while now, I'm going to make this my next priority.

@nertzy nertzy was assigned Aug 10, 2012
@michald
michald commented Sep 4, 2012

How looks progress on the task?

@tesserakt

@nertzy yes how goes the progress?

@memoht
memoht commented Dec 7, 2012

I have been reading this thread and the Rails #950 issue and think I am having the problem described above. I have a Notes model that belongs_to a Contact model

In my Notes model I have:

include PgSearch
pg_search_scope :search, against: [:posted_by],
  using: {tsearch: {dictionary: "english", :prefix => true}},
  associated_against: {contact: :company}

In my notes_controller, I have:

def index
  @notes = Note.includes(:contact).text_search(params[:query]).page(params[:page]).per(50)
end

I am searching for a posted_by name which happens to have a "." dot. If i do a partial word search and stop before the . it seems ok, but if I type the full search name with the dot (example: mr.spock), I am seeing:

PG::Error: ERROR:  invalid reference to FROM-clause entry for table "contacts"
LINE 1: ...dated_at" AS t0_r4, "notes"."posted_by" AS t0_r5, "contacts"...
                                                         ^
HINT:  Perhaps you meant to reference the table alias "contacts_notes".

If i understand correctly, the problem is the dot itself as part of the search query, so for now the best work around is to perhaps change the posted_by field to not have a dot. I am using PG_Search 0.5.7 on a Rails 3.2.9 app.


I can live with work around for all of the usefulness I have gotten from this gem. Hope it will continue to be developed for a long time to come. Very helpful for someone like me with less coding experience.

@alexbrand

Any updates on this issue? What are the recommended fixes?

@nertzy nertzy added a commit that referenced this issue Apr 2, 2013
@nertzy nertzy Add failing spec for Issue #14
Thanks to John Shahid for pairing with me and helping to isolate this
issue.
4f700e8
@nertzy nertzy added a commit that referenced this issue Apr 2, 2013
@nertzy nertzy Add failing spec for Issue #14
Thanks to John Shahid for pairing with me and helping to isolate this
issue.
170d06a
@nertzy nertzy added a commit that closed this issue Apr 2, 2013
@nertzy nertzy Disable eager loading, fixes #14
Eager loading doesn't work because of the way pg_search_rank is added
into the SELECT for the query.
47b2a0b
@nertzy nertzy closed this in 47b2a0b Apr 2, 2013
@nertzy
nertzy commented Apr 2, 2013

I'm going to release this fix as part of 0.6.0 today.

@nertzy nertzy added a commit that referenced this issue Apr 2, 2013
@nertzy nertzy Disable eager loading, fixes #14
Eager loading doesn't work because of the way pg_search_rank is added
into the SELECT for the query.
3f39c11
@jaredbeck

Thanks @nertzy. Upgrading to the latest (0.7.3) fixed this issue for me.

@mockdeep
mockdeep commented May 6, 2014

@nertzy We seem to be having this problem in our application. Using gem version 0.7.3 and Rails 3.2.17.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.