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.

If you read through this post it should be clear I'm trying to build a multi-column search for a table that can traverse related objects. I'm pretty confident that I'll be able to iterate over a set of `search_relations` and corresponding `search_columns`, plus a list of `search_terms`, and `inject` OR statements together in the right places. I just can't figure out how to make a simple non-iterative example work in console. — Chris Keele, Nov 27 '12 at 07:56

score 2 · Answer 1 · answered Nov 27 '12 at 13:19

2

Chris,

You're on the right track. Simply calling the matches method on the KeyPath object will create a matches predicate with the keypath on the left side and the argument to matches on the right. Squeel's visitor will traverse the keypath on the left, arrive at the attribute in question, and build the matches ARel predicate against it.

answered Nov 27 '12 at 13:19

Ernie

896
5
7

I had hoped it was as simple as that. I tried it a few different ways but to no success, results posted above. Hopefully I'm just making a syntax error somewhere. – Chris Keele Nov 27 '12 at 17:14
I believe I have figured it out! Thanks for your help! – Chris Keele Nov 27 '12 at 18:00

score 1 · Accepted Answer · answered Nov 27 '12 at 18:06

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%'

answered Nov 27 '12 at 18:06

Chris Keele

3,364
3
30
52

i'm trying to do nearly the exact same thing, and i copied this code in and don't get the same results as you - instead i get the results you got in your original question, where the key_path is read as a field – prater Jul 24 '13 at 09:58
I haven't looked at this for a while; my original question came up when building a toy project for integrating jQuery Datatables into rails through a DSL. I believe the result was: https://github.com/christhekeele/rails-tables/blob/master/lib/rails-tables/datatable/searching.rb#L41. Mind that this code hasn't been touched for nearly a year, so may not be consistent with Squeel today. – Chris Keele Jul 24 '13 at 20:03
From the looks of it, I never arranged the `KeyPath` into a predicate node, but called `=~` on it directly, passing in the search term, then embedding that in a `my` in a `where`. So: `Model.where{ my{KeyPath.new(...) =~ term} }`. – Chris Keele Jul 24 '13 at 20:10

Metaprogram Squeel to search on keypaths

The question

The problem

Previous attempts

2 Answers2