Cannot solve scope-chaining problem, perhaps bug?

[maxim]

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?

[Peeja]

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]

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]

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

[nertzy]

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

Lesson.by_fuzzy_name and Lesson.by_instructor_fullname