Problems searching for resources belonging to STI

[adamruzicka]

Let's consider classes Location and Organization which are subclasses of Placement using STI. Class Item belongs_to one organization and one location. Searching for an Item belonging to organization and location generates wrong search query and always returns 0 items.

The search query first LEFT OUTER JOINs the placements, then LEFT OUTER JOINs the placements again with an alias, but when the filtering is done, the first joined table is used twice instead of each being used once.

# Running this
Item.search_for('location = Somewhere and organization = Something').count

# Generates this SQL
SELECT COUNT(DISTINCT "items"."id") FROM "items"
  LEFT OUTER JOIN "placements" ON "placements"."id" = "items"."location_id" AND "placements"."type" IN ('Location')
  LEFT OUTER JOIN "placements" "organizations_items" ON "organizations_items"."id" = "items"."organization_id" AND "organizations_items"."type" IN ('Organization')
     WHERE ((("placements"."title" = 'Somewhere') AND ("placements"."title" = 'Something')))

Example rails application demonstrating the issue can be found at adamruzicka/scoped_search_reproducer.

Versions used:

• ruby-2.5.0
• activerecord (5.2.1)
• scoped_search (4.1.3)