Searching fields on has many associations returns duplicate results

[tjvc]

What were you trying to do?

Mark a has many field as searchable and set some searchable_fields. Perform a search, and the parent record will appear multiple times on the dashboard (when it has more than one associated child).

I'm not sure if this is intended to be supported (the search options don't seem to be documented for a has many field), but it at least partially works.

What did you end up with (logs, or, even better, example apps are great!)?

Here's an example query from the logs (this is a has many through association, but the principle is the same):

SELECT "users".* FROM "users" LEFT OUTER JOIN "committee_customers" ON "committee_customers"."user_id" = "users"."id" LEFT OUTER JOIN "committees" ON "committees"."id" = "committee_customers"."committee_id" WHERE "users"."role" = $1 AND "users"."soft_deleted" = $2 AND (LOWER(CAST("users"."email" AS CHAR(256))) LIKE '%test%' OR LOWER(CAST("users"."first_name" AS CHAR(256))) LIKE '%test%' OR LOWER(CAST("users"."last_name" AS CHAR(256))) LIKE '%test%' OR LOWER(CAST("committees"."name" AS CHAR(256))) LIKE '%test%') LIMIT $3 OFFSET $4

We're left joining associations, so I think all that's required is a DISTINCT.

What versions are you running?

rails (6.0.4.1) administrate (0.16.0)

[pablobm]

I agree that those duplicates should not appear. There must be some SQL magic we can apply there to fix this issue.

In the longer term, ideally I would like our current search replaced with something more abstract, with Administrate bundling only a basic implementation and alternatives provided by plugins or custom ad-hoc code. This way people could do things like integrating with Elasticsearch or enhancing the query format. But that won't happen soon... unless someone wants to take ownership of this task?

[plabadille]

With the last version 0.18.0 there is an easy fix for this issue. Just need to slightly customize the search :

def filter_resources(resources, search_term:)
  super(resources, search_term: search_term).distinct
end

I may miss some side effects but I think it should be included in the core method ? It won't work if your rails version is above 6.0 (this may be why it's not in the core yet).

[pablobm]

@plabadille - Why do you say that it won't work outside those versions? My understanding is that Relation#distinct was introduced in Rails 4.0.

@nickcharlton - Do you see any disadvantages to making #distinct the default?

[plabadille]

@plabadille - Why do you say that it won't work outside those versions? My understanding is that Relation#distinct was introduced in Rails 4.0.

Yes you are right sorry I've been confused with the Relation.uniq that was removed from rails 6.0

[nickcharlton]

Seems like a good idea to me!

[pablobm]

@plabadille - Do you want to submit a PR? :slightly_smiling_face: