Support exact match search queries in dashboards

[FabriceCastel]

• What would you like to be able to do? Can you provide some examples?

I'd like to be able to set a search_exact: true option on a searchable field in my dashboard such that when administrate executes the search query, it matches results using an equality constraint instead of a LIKE '%my-string%' substring search. I mainly want to do this for performance reasons when searching for eg. all emails sent to a given email address, from a table that might hold a very large number of records.

The filter feature wasn't well suited to this because it requires the filter options to be defined upfront, and in a case like this I don't think that I'd really want to include every possible email in the filter options even if I could.

• How could we go about implementing that?

I've put together a proof of concept in repo based off an older fork and the diffs are pretty minimal:

lib/administrate/field/base.rb

+      def self.search_exact?
+        false
+      end

lib/administrate/field/deferred.rb

+      def self.search_exact?
+        false
+      end

lib/administrate/search.rb

    def query_template
      search_attributes.map do |attr|
        table_name = query_table_name(attr)
        searchable_fields(attr).map do |field|
          column_name = column_to_query(field)
+         if attribute_types[attr].search_exact?
+           "LOWER(#{table_name}.#{column_name}) = ?"
+         else
+           "LOWER(CAST(#{table_name}.#{column_name} AS CHAR(256))) LIKE ?"
+         end
        end.join(" OR ")
      end.join(" OR ")
    end

    # [...]

    def query_values
+     search_attributes.flat_map do |attr|
+       attribute_type = attribute_types[attr]
+
+       if attribute_type.search_exact?
+         ["#{term.mb_chars.downcase}"] * searchable_fields(attr).count
+       else
+         ["%#{term.mb_chars.downcase}%"] * searchable_fields(attr).count
+       end
+     end
    end

There were a couple of specs that needed updating to validate the new option.

• Can you think of other approaches to the problem?

No, but to be fair this is my first time digging into administrate. Also, caveat: I haven't tested this with non-text fields, this might still require the CAST to work across all types given the LOWER.

[nickcharlton]

Oh, that's a great idea! I appreciate how the diff is quite small too.

Would you be able to open a PR for it, and then we can take it from there?

[jared-thoughtbot]

Coincidentally I was looking for exactly this today! Similar scenario. Some very large tables that timeout with the LIKE scans even though the searchable fields are indexed. Would love to see this

[FabriceCastel]

Ok this is admittedly a little bit of a bait & switch, insofar as I've thrown in two other options alongside the one I'm proposing here 😅 but they're working towards the same goal: more performant search options!

#2752

I haven't gone as far as to document or test it, this is an initial proof of concept to see if we're at least aligned on the extra bits I'm requesting we add here before doing any more work.