CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresql
Makarov
Makarov

Reputation: 187

Find coincidences on related tables using SQL and Rails and know where the coincidences were

Well, I have the next Rails scope, when given a word I found the companies that match that word either on the name, description, city or in any of the products related with the company.

includes([:products, {city: :department}]).where("unaccent(LOWER(companies.name)) 
ILIKE('%#{term.parameterize.underscore.humanize.downcase}%') 
OR unaccent(LOWER(companies.description)) 
ILIKE('%#{term.parameterize.underscore.humanize.downcase}%') 
OR unaccent(LOWER(cities.name)) 
ILIKE('%#{term.parameterize.underscore.humanize.downcase}%') 
OR unaccent(LOWER(products.name)) 
ILIKE('%#{term.parameterize.underscore.humanize.downcase}%')"
.gsub("ñ","n")).references(:products, :city, :department)

This works just fine, but know I need to know in which (name, description, city or products) was the coincidence found.

I have thought in the next solutions but I am not sure if is efficient or good enough.

Solution. Separate the scope in 4 different queries, then use a loop and an aux column to fill with something like "Coincidence founds in, name and description" on each different query. then use something like this

query1 | query2 | query3 | query4 # to merge the arrays

For the record my Rails app is using Postgres 9.4

Upvotes: 0

Views: 434

Answers (1)

max pleaner
max pleaner

Reputation: 26768

I think you have a good start by separating your results into four queries.

When you merge them, you want to maintain a way to see which query it came from.

If you're fine with your results being hashes, you can do this:

results = []
results.concat query1.map { |record| record.attributes.merge(query: "query1") }
results.concat query2.map { |record| record.attributes.merge(query: "query2") }
# etc

If you want your results to be active record objects, you can add a virtual attribute and do something similar

# in the model, add a virtual attribute (not stored in db)
attr_accessor :query

# in the controller
records = []
records.concat query1.map { |record| record.query = "query1"; record}
records.concat query2.map { |record| record.query = "query2"; record}
# etc.

Upvotes: 1

Related Questions