Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

PG::Error: ERROR: missing FROM-clause entry for table "tags" #109

Closed
chuckweinberger opened this Issue Apr 10, 2013 · 14 comments

Comments

7 participants

I'm using pg_search 0.6.1 and RR 3.1.10. I have been following issue #14 in hopes of being able to chain together a pg_search_scope with a named scope that uses includes. Here's what I am trying to do:

  pg_search_scope   :with_text, 
                    :against => :title, 
                    :using => { :tsearch => { :dictionary => "english" }},
                    :associated_against => { :posts => :contents }

  scope :in_tags, lambda {|tag_array|
                            Story.includes(:tags).
                            where("tags.name in (:tag_array)", :tag_array => tag_array )
                          }

story = Story.in_tags(array_of_tags).with_text("search text goes here")

When I was using pg_search 5.7 I would receive the following error: ActiveRecord::StatementInvalid: PG::Error: ERROR: column "pg_search_rank" does not exist. This error is consistnet with what was reported in issue #14.

Now that I've updated the gem to pg_search 6.1, I'm getting this error: ActiveRecord::StatementInvalid: PG::Error: ERROR: missing FROM-clause entry for table "tags".

Note, Story.in_tags(array_of_tags) executes as expected. The problem occurs only when I try to link that named scope with a pg_search_scope. The order of chaining doesn't make a difference with this error

I should mention that Tag is in a habtm association with Story.

Has anyone been able to confirm this bug? I'm currently working-around it by making my joins by hand (instead of using includes, but I really want to eager-load the associated tags, and can't do that without includes (as @gregolson correctly points out in his comments to #110)

maxwell commented May 23, 2013

I've been caught by this bug as well. Anyone have insight into where in the code needs to be updates to make this work. I'm thinking I might need to take a stab but any guidance would be helpful

maxwell commented May 27, 2013

I think I found a decent work around, @chuckweinberger

I've found ActiveRecord#preload, which helps us be more explicit.

I used to do this, which blew up.

  scope :has_not_ordered, lambda{ includes(:order).where('orders.id IS NULL')  }

Then call Model.has_not_ordered.search('foo')

I've changed this to:

  scope :has_not_ordered, lambda{ 
joins('LEFT OUTER JOIN "orders" ON "orders"."backer_id" = "backers"."id" ').where('orders.id is null').preload(:order) }

This does not blow up, but works as expected. The Rails includes call does a left outer join, and the preload does one query for all the associated models.

Hope that helps.

Excellent @maxwell! This is the perfect work-around.

Thanks so much!

Although I'm now hitting up against a related problem. I've had to add a .group("stories.id") to my .in_tags scope because a story can belong to multiple tags, and in such a case I only want to respond with one Story instance. And now I'm receiving the following error: ActiveRecord::StatementInvalid: PG::Error: ERROR: column "pg_search_79dd68cf7f962ac568b3d7.pg_search_c5f43d73058486e1799d26" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...e"::text, '')) || to_tsvector('english', coalesce(pg_search_...

Switched the .group() to a select('DISTINCT stories.id') and everything seems to be working as desired. Thanks again @maxwell.

phlegx commented Dec 13, 2014

I have described the same problem on #206. @maxwell any idea how can I solve this problem? It is very similar.

I was able to change:

.includes(:store).where.not(stores: { tier_name: nil })

to:

.joins(:store).where.not(stores: { tier_name: nil })

and it fixed the issue for me.

I'm also having trouble with .group() (similar to @chuckweinberger), but I'm not able to use DISTINCT since I have an aggregate function...

User.select("users.*, COUNT(skills) AS skill_count").joins(:skills).group("users.id")

I'm getting a little frustrated... any help or idea will be appreciated...

Nevermind... I solved it using the hack described in #206 by @phlegx, anyways, if it ever gets integrated I would suggest adding an option :grouped => true, to include the grouping in the search scope.

phlegx commented Feb 18, 2015

Hi @Mackaber actually I use this method override hack:

class << self
  # Overwrite alias method to get the right subselect alias
  def alias(*strings)
    #name = Array(strings).compact.join("_")
    # By default, PostgreSQL limits names to 32 characters, so we hash and limit to 32 characters.
    #"pg_search_#{Digest::SHA2.hexdigest(name)}"[0,32]
    Array(strings).second
  end
end

Try it out and give me a feedback please. I have found many things to change in the gem. I think to do a fork.

Collaborator

nertzy commented May 4, 2015

This is not an error in pg_search. Instead it's a limitation of how Active Record works with joins or includes chained with a String where condition.

You should use references to ensure that the SQL JOIN is not built with an alias.

You can read a short description in this Rails Guide.

Here's an example of how to rewrite the :in_tags scope using references:

scope :in_tags, lambda { |tag_array|
  includes(:tags)
    .references("tags")
    .where("tags.name in (:tag_array)", :tag_array => tag_array )
}

Or, more simply, you can use a Hash which will work whether or not there are aliases:

scope :in_tags, lambda { |tag_array| includes(:tags).where(:tags => {:name => tag_array}) }

Also, you shouldn't have a reference to Story inside the lambda defining your scope. The default receiver is already the current class. This will help if you end up using inheritance to share this code with other classes later.

As for the other issues in this thread, it appears that many of them would probably be fixed with a call to references as well. If this is not the case, then please open a separate thread for each new issue.

@nertzy nertzy closed this May 4, 2015

scope :has_sizes, -> { includes(:available_sizes).where().not(available_sizes: {quantity: 0}).where("products.published IS true").where("products.inactive IS NOT true") }

breaks the search for me

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment