Rails active record call with parameters?-CodePudding

My app uses Searchkick with Elasticsearch to return aggregations (as in the breakdown of categories etc) from your search which I use to build filters.

Now from this I can quickly get an array like:

[[1, 47], [1, 119], [1, 174], [1, 207], [1, 217], [1, 225], [1, 239], [1, 241], [1, 271], [1, 293], [1, 295]]

The first number is the count and the second is the id of the record.

I want to do something like this:

"SELECT CONCAT(name, ' (', $1, ')') AS label, id  FROM projects WHERE id = $2"

So I end up with a new array like:

[["Project A (1)", 47], ["Project B (1)", 119], ... ]

I can then use this direct in a select. I don't want to / can't to second group query to recreate those counts.

I could loop over the array and make separate raw SQL calls but that seems messy. I think this is some sort of messy .map call but is way over my head.

I can get two separate arrays (id and count) then do a quick pluck to get the name array from the BD:

['Project A', 'Project B', ...]

Once I have that is there a quick way to merge the three arrays to the option format I need?

CodePudding user response：

Yes, as you already suggest, there is a simple solution using map.

Let's assume that you have already stored the id and count from ElasticSearch in the following array:

count_and_id = [[1, 47], [1, 119], [1, 174], [1, 207], [1, 217], [1, 225], [1, 239], [1, 241], [1, 271], [1, 293], [1, 295]]

Then, as you describe, you pluck the project names and put them into a Hash for faster lookup:

relevant_ids = count_and_id.map(&:second)
project_names = Project.where(id: relevant_ids).pluck(:id, :name).to_h # {47 => "Project A", 119 => "Project B", ...}

In order to end up with the formatted array, you can use map, as suggested:

result = count_and_id.map do |count,id|
  description = "#{project_names[id]} (#{count})"
  [description, id]
end # [["Project A (1)", 47], ["Project B (1)", 119], ...]