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

Cannot solve scope-chaining problem, perhaps bug? #71

Open
maxim opened this Issue Oct 18, 2012 · 4 comments

Comments

5 participants

maxim commented Oct 18, 2012

I have a model, say Order, and it has_many say line_items. Each line item has a status field. There are only 5 possible statuses, so I want to make a facet for it.

class Order
  # [snip]
  pg_search_scope :with_status,
    associated_against: { line_items: [ :status ] }
  # [snip]

But I also want to provide a general full text search across orders and line items.

class Order
  # [snip]
  pg_search_scope :search,
    against: [ :order_number, :email ],
    associated_against: { line_items: [ :status, :vendor, :title ] }
  # [snip]
end

This is the easiest attempt. Calling these in any order, whether I try status first.

Order.with_status('new').search('hat')

Or search first.

Order.search('hat').with_status('new')

Leads to the same error.

PG::Error: ERROR: table name "pg_search_a641e4eca88e47c3d12cb0" specified more than once`

The reasons seems to be that I use line_items association in both scopes. Ok, so I tried to use a regular scope for :with_status.

  scope :with_status, -> status {
    joins(:line_items).where(line_items: { status: status })
  }

Now I try to run it again.

Order.with_status('new').search('hat')

And I got a new error.

PG::Error: ERROR: invalid reference to FROM-clause entry for table "line_items"
[...]
HINT: Perhaps you meant to reference the table alias "line_items_orders".

Reversing with_status and search ordering did not change anything. So apparently pg_search establishes some alias and complains that I'm not using it in my scope. I also tried using includes in place of joins - same result.

So lastly, I try to go back to original attempt with pg_search_scope for both, but this time I remove line_items from the associated_against section of search one and only use it in with_status. This is not good, because now I can't search by other fields in line items.

class Order
  # [snip]
  pg_search_scope :with_status,
    associated_against: { line_items: [ :status ] }

  pg_search_scope :search,
    against: [ :order_number, :email ]
  # [snip]
end

This far from ideal case didn't work either, complaining about ordering issue. So I called a .reorder("placed_on DESC") at the end of the scope chain and now things showed up, but I lost important fields from the search.

Any ideas how to solve this or fix the bugs that cause this?

Collaborator

Peeja commented Nov 28, 2012

I'd say these are two different bugs. If I understand what you're trying to do, you don't really need full-text search for the status field, you want exact matches; is that right? If so, let's ignore the first version.

What's does this give you?

Order.with_status('new').search('hat').to_sql

That should give a hint.

tyre commented Aug 29, 2015

Seeing this as well. pg_scope names its table aliases the same thing regardless of scope or other calls so they conflict. Could we alias by name of the method call?

E.G.

pg_search_scope :search # => table alias = pg_search_search
pg_search_scope :search_by_name # => table alias = pg_search_search_by_name

benlieb commented Feb 6, 2016

Seeing this as well.

This works:
Lesson.by_fuzzy_name('slide').by_instructor_fullname('bill')

But this dies:
Lesson.by_fuzzy_name('slide').merge(Lesson.by_instructor_fullname('bill'))PG::DuplicateAlias: ERROR

The SQL generated for the intrepid:

SELECT "lessons".* FROM "lessons" INNER JOIN ( SELECT "lessons"."id" AS pg_search_id, (Ts_rank((To_tsvector('english', COALESCE("lessons"."name"::text, ''))), (To_tsquery('english', ''' ' || 'slide' || ' ''')), 0)) AS rank FROM "lessons" WHERE ((( To_tsvector('english', COALESCE("lessons"."name"::text, ''))) @@ (to_tsquery('english', ''' ' || 'slide' || ' '''))))) AS pg_search_314a6b49660562c305aaf8 ON "lessons"."id" = pg_search_314a6b49660562c305aaf8.pg_search_id INNER JOIN ( SELECT "lessons"."id" AS pg_search_id, (ts_rank((to_tsvector('simple', COALESCE(pg_search_3138627c6ff5f1061c3349.pg_search_fca40fda173177b8bf2019::text, '')) || to_tsvector('simple', COALESCE(pg_search_3138627c6ff5f1061c3349.pg_search_4423a1a8c85415388ab0f0::text, ''))), (to_tsquery('simple', ''' ' || 'bill' || ' ''')), 0)) AS rank FROM "lessons" LEFT OUTER JOIN ( SELECT "lessons"."id" AS id, string_agg("users"."first_name"::text, ' ') AS pg_search_fca40fda173177b8bf2019, string_agg("users"."last_name"::text, ' ') AS pg_search_4423a1a8c85415388ab0f0 FROM "lessons" INNER JOIN "instructors_lessons" ON "instructors_lessons"."lesson_id" = "lessons"."id" INNER JOIN "users" ON "users"."id" = "instructors_lessons"."instructor_id" GROUP BY "lessons"."id") pg_search_3138627c6ff5f1061c3349 ON pg_search_3138627c6ff5f1061c3349.id = "lessons"."id" WHERE ((( to_tsvector('simple', COALESCE(pg_search_3138627c6ff5f1061c3349.pg_search_fca40fda173177b8bf2019::text, '')) || to_tsvector('simple', COALESCE(pg_search_3138627c6ff5f1061c3349.pg_search_4423a1a8c85415388ab0f0::text, ''))) @@ (to_tsquery('simple', ''' ' || 'bill' || ' '''))))) AS pg_search_314a6b49660562c305aaf8 ON "lessons"."id" = pg_search_314a6b49660562c305aaf8.pg_search_id ORDER BY pg_search_314a6b49660562c305aaf8.rank DESC, "lessons"."id" ASC

Collaborator

nertzy commented Feb 10, 2016

@benlieb could you also include the definitions of those scopes?

Lesson.by_fuzzy_name and Lesson.by_instructor_fullname

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