TIL: joining with LIKE

#sql #rails

Today I encountered an interesting way to join two tables for the first time - using an operator other than id = other.id.

I have a config_entries table that holds the beginning parts of Thing names that need to be handled in a special way. How to query for the Things that qualify? JOIN with LIKE to the rescue!

Thing.all.joins(
  "INNER JOIN config_entries ON " \
  "  things.name LIKE config_entries.lookup_term || '%'"
)

The LIKE config_entries.lookup_term || '%' portion is PSQL concatenation, equivalent to LIKE CONCAT(config_entries.lookup_term, '%'), resulting in a term% argument for LIKE.

Addendum

Turns out you can do a full subquery join, sparing the need to repeat any scoping:

Things.all.joins(
  "INNER JOIN (#{project_config_entries.some_scope.to_sql}) AS config_entries ON things.name LIKE config_entries.lookup_term || '%'"
)