Metaprogram Squeel to search on keypaths

Question

The question

I'm trying to wrap my head around arel and squeel, but I feel like I lack the vocabulary to ask Google what I'm looking for.

TL;DR: Does anyone know how to mimic Squeel's Model.where{related.objects.field.matches string} syntax out of composed Squeel::Node objects?

The problem

This question demonstrates how to build a Squeel KeyPath from a string, like so:

search_relation = 'person.pets'
User.joins{Squeel::Nodes::KeyPath.new(search_relation.split('.'))}
# Mimics User.joins{person.pets}

The plot thickens in this post, where I learned how to metaprogram a search across multiple columns. Cleaned up with a documented Squeel trick (the last line on the page), it looks like this:

search_columns = %w[first_name last_name]
search_term = 'chris'
User.where{
  search_columns.map do |column|
    column_stub = Squeel::Nodes::Stub.new(column)
    Squeel::Nodes::Predicate.new(column_stub, :matches, "%#{search_term}%")
  end.compact.inject(&:|)
}
# Mimics User.where{(last_name.matches '%chris%') | (first_name.matches '%chris%')} 

What I want is to be able to combine the two, mimicking the syntax of

User.joins{person.pets}.where{person.pets.name.matches '%polly%'}

by building the squeel objects myself.

The first part is easy, demonstrated in the first example. The second part I can do for columns directly on the table being queried, demonstrated in the second example.

However, I can't figure out how to do the second part for columns on joined in tables. I think I need to use the KeyPath object from the first example to scope the Predicate object in the second example. The Predicate object only seems to work with a Stub object that represents a single column on a single table, not a KeyPath.

Previous attempts

Assumming

search_relation = 'key.path'
search_column = 'field'
search_term = '%search%'
keypath = Squeel::Nodes::KeyPath.new(search_relation.split('.') << search_column)

I've tried calling the matches method on the KeyPath:

Model.where{keypath.matches search_term}
#=> NoMethodError: undefined method `matches' for Squeel::Nodes::KeyPath

I've tried using the matches operator, =~:

Model.where{keypath =~ search_term}
#=> TypeError: type mismatch: String given

And I've tried building the Predicate manually, passing in a :matches parameter:

Model.where{Squeel::Nodes::Predicate.new(keypath, :matches, search_term)}
#=> ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column
#=>   'model.key.path.field' in 'where clause':
#=>   SELECT `model`.* FROM `model`
#=>     INNER JOIN `key` ON `key`.`id` = `model`.`key_id`
#=>     INNER JOIN `path` ON `path`.`id` = `key`.`path_id`
#=>     WHERE `model`.`key.path.field` LIKE '%search%'

Here, it successfully generates SQL (correctly joining using Model.joins{search_relation} like above, omitted for readability) then assumes the KeyPath is a field instead of traversing it.

Answer 1

While the KeyPath in the first example is sufficient for joining tables, a KeyPath to be used in WHERE clauses needs to be constructed a little more carefully, by ensuring the last value is indeed a Stub.

search_relation = 'key.path'
search_column = 'field'
search_stub = Squeel::Nodes::Stub(search_column)
search_term = '%search%'
keypath = Squeel::Nodes::KeyPath.new(search_relation.split('.') << search_stub)

Model.where{Squeel::Nodes::Predicate.new(keypath, :matches, search_term)}
#=> SELECT `model`.* FROM `model`
#=>   INNER JOIN `key` ON `key`.`id` = `model`.`key_id`
#=>   INNER JOIN `path` ON `path`.`id` = `key`.`path_id`
#=>   WHERE `path`.`field` LIKE '%search%'