A self referential belongs_to creates an incorrect SQL query

[mackross]

• What were you trying to do?

I've a user model that has a reference to itself as belongs_to :accountability_buddy, class_name: 'User'
I want to use accountability_buddy: Field::BelongsTo.with_options(searchable: true, searchable_fields: 'email') and have the
search return both a user and any users that this user is the accountability buddy for.

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

If I change the searchable fields instead to ['xxx'], the query demonstrates the issue.

21:32:54 web.1 | User Load (0.9ms) SELECT "users".* FROM "users" LEFT OUTER JOIN "users" "accountability_buddies_users" ON "accountability_buddies_users"."id" = "users"."accountability_buddy_id" WHERE (LOWER(CAST("users"."email" AS CHAR(256))) LIKE '%andrew@example.com%' OR LOWER(CAST("users"."xxx" AS CHAR(256))) LIKE '%andrew@example.com%' OR LOWER(CAST("users"."last_sign_in_ip" AS CHAR(256))) LIKE '%andrew@example.com%') LIMIT $1 OFFSET $2 [["LIMIT", 20], ["OFFSET", 0]]

You can see that it should the second OR statement should be as "accountability_buddies_users"."xxx" but it instead is "users"."xxx".

• What versions are you running?
  • Rails 7
  • administrate 0.18

[pablobm]

@mackross - Thank you for this report. It's indeed a bug but unfortunately it doesn't (I think) have an easy fix. It'll have to be part of an effort to redesign the search, which is one of the things in my radar.

A workaround would be to implement your own search, as explained at https://github.com/pablobm/administrate/blob/howto-search/docs/guides/customising_search.md (those docs are not published officially yet, but will work).