Makarov Makarov - 16 days ago 4x
Ruby Question

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(
OR unaccent(LOWER(companies.description))
OR unaccent(LOWER(
OR unaccent(LOWER(
.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.

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


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 { |record| record.attributes.merge(query: "query1") }
results.concat { |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 { |record| record.query = "query1"; record}
records.concat { |record| record.query = "query2"; record}
# etc.