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 " \
  "  thing.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.