DEV Community

Augusts Bautra
Augusts Bautra

Posted on • Edited on

TIL: joining with LIKE

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 || '%'"
)
Enter fullscreen mode Exit fullscreen mode

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 || '%'"
)
Enter fullscreen mode Exit fullscreen mode

Top comments (0)