Databases
StackOverflow2013
Postsdbo
POMetaprogram Squeel to search on keypaths
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMetaprogram Squeel to search on keypaths
    primarykey
    data
    text
    <h3>The question</h3> <p>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.</p> <p>TL;DR: Does anyone know how to mimic Squeel's <code>Model.where{related.objects.field.matches string}</code> syntax out of composed <code>Squeel::Node</code> objects?</p> <h3>The problem</h3> <p><a href="https://stackoverflow.com/questions/11641802/squeel-evaluating-a-string-as-a-keypath-for-joins">This question</a> demonstrates how to build a Squeel KeyPath from a string, like so:</p> <pre><code>search_relation = 'person.pets' User.joins{Squeel::Nodes::KeyPath.new(search_relation.split('.'))} # Mimics User.joins{person.pets} </code></pre> <p>The plot thickens in <a href="http://www.tumblr.com/tagged/squeel?before=1318573080" rel="nofollow noreferrer">this post</a>, where I learned how to metaprogram a search across multiple columns. Cleaned up with a <a href="https://github.com/ernie/squeel/wiki/Tips-and-tricks" rel="nofollow noreferrer">documented Squeel trick</a> (the last line on the page), it looks like this:</p> <pre><code>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(&amp;:|) } # Mimics User.where{(last_name.matches '%chris%') | (first_name.matches '%chris%')} </code></pre> <p>What I want is to be able to combine the two, mimicking the syntax of</p> <pre><code>User.joins{person.pets}.where{person.pets.name.matches '%polly%'} </code></pre> <p>by building the squeel objects myself.</p> <p>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.</p> <p>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 <code>KeyPath</code> object from the first example to scope the <code>Predicate</code> object in the second example. The <code>Predicate</code> object only seems to work with a <code>Stub</code> object that represents a single column on a single table, not a <code>KeyPath</code>.</p> <h3>Previous attempts</h3> <p>Assumming</p> <pre><code>search_relation = 'key.path' search_column = 'field' search_term = '%search%' keypath = Squeel::Nodes::KeyPath.new(search_relation.split('.') &lt;&lt; search_column) </code></pre> <p>I've tried calling the <code>matches</code> method on the KeyPath:</p> <pre><code>Model.where{keypath.matches search_term} #=&gt; NoMethodError: undefined method `matches' for Squeel::Nodes::KeyPath </code></pre> <p>I've tried using the matches operator, <code>=~</code>:</p> <pre><code>Model.where{keypath =~ search_term} #=&gt; TypeError: type mismatch: String given </code></pre> <p>And I've tried building the <code>Predicate</code> manually, passing in a <code>:matches</code> parameter:</p> <pre><code>Model.where{Squeel::Nodes::Predicate.new(keypath, :matches, search_term)} #=&gt; ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column #=&gt; 'model.key.path.field' in 'where clause': #=&gt; SELECT `model`.* FROM `model` #=&gt; INNER JOIN `key` ON `key`.`id` = `model`.`key_id` #=&gt; INNER JOIN `path` ON `path`.`id` = `key`.`path_id` #=&gt; WHERE `model`.`key.path.field` LIKE '%search%' </code></pre> <p>Here, it successfully generates SQL (correctly joining using <code>Model.joins{search_relation}</code> like above, omitted for readability) then assumes the <code>KeyPath</code> is a field instead of traversing it.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USChris Keele
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMetaprogram Squeel to search on keypaths
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMetaprogram Squeel to search on keypaths
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf 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.
      singulars
      1. POMetaprogram Squeel to search on keypaths
      1. USChris Keele
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload