like_query

For my clients' newly built applications with Turbo, search queries mostly serve two purposes:

  • Index view callable by a url like /customers?find=müller screw
  • javascript component / dropdown on the front, in our case built with svelte, which receives a json and renders a table in a dropdown.

This query generator is built for these two purposes

Modules like one of the svelte components mentioned above have a total response time (from pressing a key until the result is rendered) of about 60 msec, while with turbo the same time is mostly around 140-160 msec. The gem itself, from querying the database to producing a hash, has a time of about 3 msec. These results are for small data sets (e.g. 30 records found).

Installation

add

gem "like_query"

to Gemfile

this adds the methods #like and #generate_hash to all models.

Config

You can set a default limit by

  config.x.like_query.limit = 20

This can be overriden by calling the methods

Usage

#like

customer = Customer.create(name: 'Ambühl')
Article.create(name: 'first', number: '01', customer: customer)

Article.like('fir', :name, :number)
# => <Article:0x00000001067107a8 id: ...>
# => searches by: "where name like '%fir%' or number like '%fir%'"
# => queries are built with Article.arel_table[:name].matches('%fir%')

Article.like('fir ambühl', [:name, :number, customer: :name])
# => :customer is the name of the association
# => first parameter ('fir ambühl') is equal to ['fir', 'ambühl']
# => search-tag as array adds a ".and(where( ... like '%ambühl%'))" for all scoped columns
# => would also find art1

#generate_hash

returns a hash that can easily be transformed by #to_json for a javascript frontend, for example

customer = Customer.create(name: 'Ambühl')
art1 = Article.create(name: 'first', number: '01', customer: customer)

Article.like('fir', :name).generate_hash(limit: 10)
# returns: 
{
  data: 
    [
      {
        values: ["Müller"], 
        attributes: {"customer.name": "Müller"}, 
        id: 1, 
        model: "Article"
      }, 
      ...
    ], 
  length: 4, 
  overflow: true, 
  columns_count: 2, 
  sub_records_columns_count: 0, 
  image: false, 
  time: 18.282996
}

Article.like('fir', :name).generate_hash(:number, limit: 10)
# would query like the above example: Search scope is only :name
# but would return article-number instead of article-name inside the data block

#generate_hash uses LikeQuery::Collect, functionality is the same.

Enums

class Article < ApplicationRecord
  enum type_enum: { 'Wheel': 0, 'Vehicle': 1, 'Truck': 2 }
end

Article.like('Truck', [:type_enum]).count #=> 1

Translated enums and enums on associated models (e.g. article.customer) not yet implemented

Numbers

Book.like('Nelson 3.4..8', [:title, :price])
# => find books where title includes "Nelson" and price (must be number column) is between 3.4 and 8

Column specific search

Book.like('title:history', [:title, :price])
# => find books where title includes "history"

Book.like('author.name:marc', [{author: [:name]}])
# => find books where author.name includes "marc"

LikeQuery raises a error if invalid column name is given, for example:

Book.like('title2:history', [:title, :price])
# => raises a error like column «title2» is not present within available columns: «title, price»
# => can be translated/changed by setting locale like_query.column_not_available_error
# => Format: "column «%<column>s» is not present within available columns: «%<available_columns>s»"

This behaviour can be shut off/on by Rails.configuration.x.like_query.column_search = true/false And overriden by Book.like(..., column_search: true/false)

There is a helper for allowing the user translating the column names:

LikeQuery::Formatter.translate_columns('one N:two', [['n','name']], case_sensitive: false)
# => 'one name:two'
# case_sensitive: Default is true

Only upper and lower chars (if case-insensitive), point, underscore, and no German-Umlaute are allowed on the column-key

Class LikeQuery::Collect

cust = Customer.create(name: 'cust')
Article.create(name: 'screw', customer: cust)

c = LikeQuery::Collect.new(4)
# => 4 is the limit

c.set_schema(Customer, :name)
c.collect(parent: :customer) { Article.like('screw', :name) }
c.generate_json
c.result

# => 
{
  "data": [
    {
      "values": [
        "cust"
      ],
      "attributes": {
        "name": "cust"
      },
      "id": 1,
      "model": "Customer",
      "children": [
        {
          "values": [
            "screw"
          ],
          "attributes": {
            "name": "screw"
          },
          "id": 1,
          "model": "Customer.Article",
          "parent_id": 1,
        }
      ]
    }
  ],
  "length": 2,
  "overflow": false,
  "columns_count": 1,
  "sub_records_columns_count": 0,
  "image": false,
  "time": 0.0075
}

query schema and result_schema

The resulting hash for a record looks like this:

{
  :values => ["abc", "123"],
  :id => 456,
  :model => "article",
  :image => "src:img..."
}

The resulting hash or json is built from the schema, which can look like this:

:number

or

[:number, :name]

or

{
  values: [:number, :name],
  image: :name_of_a_column_or_method
}

There is a query schema and an output schema. If no output schema is defined, the query schema is used for both.

Article.like('first', :name).generate_hash
# => :values => ["first"]

If an output schema is specified, the result may differ from the search scope:

Article.like('first', :name).generate_hash(:number, :name)
# => :values => ["012", "first"]

The collect class remembers the schema for a model:

c = LikeQuery::Collect.new
c.collect([:name]) { Article.like('x') }
c.collect { Article.like('x') } #=> schema [:name] is used
c.collect { Article.like('x', :number) } # => schema [:number] is used

#set_schema

When a child returns its parent, the schema for the parent must be given. Otherwise #generate_hash would not know what values to return.

    c = LikeQuery::Collect.new
    c.set_schema(Customer, :name)
    # => now the customer will be returned with { values: [<name>] }
    c.collect(parent: :customer) { Article.like('screw', :name) }
    # => this will add the customer (unless it exists in the list) and add the Article as a child to the customer
    r = c.generate_hash

There is a :no_action argument on set_schema. If set to true, it will return a no_action: true on regarding models on the resulting json. This is meant for omitting javascript actions or mouse hover effects for resulting records that should do nothing.

url

You can also use the handy Rails url generators to avoid having to build urls tediously with javascript on the frontend:

customer = Customer.create(name: 'Ambühl')
proc1 = proc {|cust| customer_path(cust)}
r = Customer.like('ambühl', :name).generate_hash(:name, url: proc1)
r[:data].first[:url] #=> "/customer/#{customer.id}"

or 

customer = Customer.create(name: 'Ambühl')
c = LikeQuery::Collect.new
proc1 = proc {|cust| customer_path(cust)}
c.collect(url: proc1) { Customer.like('ambühl', :name) }
r = c.generate_hash
r[:data].first[:url] #=> "/customer/#{customer.id}"

or 

customer = Customer.create(name: 'Ambühl')
art = Article.create(number: 'art-no', customer: customer)
c = LikeQuery::Collect.new
proc1 = proc {|cust| customer_path(cust)}

c.collect(url: proc1) { Customer.like('xyxyxyyxy', :number) }
# remember url although nothing found

proc2 = proc {|article| customer_article_path(article.customer, article) }
c.collect(parent: :customer, url: proc2) { Article.like('art', :number) }
r = c.generate_hash
r[:data].first[:url] #=> "/customer/#{customer.id}"
r[:data].first[:children].first[:url] #=> "/customer/#{customer.id}/article/#{art.id}"

Performance

Values defined by the schema are processed by the #send method, but recursively. This means, for example, that for an article with the given key customer.employees.contact_details.email in the schema would return the name of the associated customer.

ATTENTION: This can trigger a lot of database queries, depending on your structure or if or which method is behind the called names.

Tests

Tests for this gem, by rspec, are not included in this gem, they can be found in test project

Collaborate with your team

Test and Deploy

Use the built-in continuous integration in GitLab.

Hope this helps

I created this only for one of my customers. If it may help others, i am happy.

License

MIT