is it possible to create hash based on columns of two connected models with ActiveRecord/SQL?-CodePudding

Here are 2 models Question & Answer

Question has many answers & has column question_text with unique values.

Answer belongs to question & has column answer_text.

I want to create hash based on columns of these tables, that question.question_text will be keys and answers.answer_text will be values as array.

I'm trying something like this:

Answer.joins(:question).where(questions: {question_text: ['value1', 'value2']}).group('questions.question_text').select('questions.question_text, array_agg(answers.answer_text)').as_json

But it doesn't work as well as I would like. Because, this return array of hashes:

[{"question_text"=>"value1", "answers"=>["some text", "some text", "some text", "some text", "some text"], "id"=>nil}, {"question_text"=>"value2", "answers"=>["text", "text"], "id"=>nil}]

I would like to prefer only hash with next format:

{question.question_text: [question.answers.answer_text], question.question_text: [question.answers.answer_text]}

CodePudding user response：

You can declare an empty hash and then loop through the questions and assign key/value pairs in the hash:

answer_hash = {}
Question.all.includes(:answers).map do |question|
  answer_hash[question.question_text] = question.answers.pluck(:answer_text)
end

The answer_hash contains the desired result.

CodePudding user response：

array_agg is a Postgresql specific feature so using it your query cannot be considered DB-agnostic which is one of the main purpose ORMs like ActiveRecord in place.

Regarding the solution with query only whether what you desire can be obtained with less complexity I cannot comment but a simple solution I can suggest is

arel = Answer.joins(:question).where(questions: {question_text: ['value1', 'value2']})

arr_of_arr = arel.pluck('questions.question_text', 'answers.answer_text')

data_hash = {}

arr_of_arr.find_each do |arr|
  question_text = arr[0]
  answer_text = arr[1]

  data_hash[question_text] ||= []
  data_hash[question_text] ||= answer_text
end

data_hash

Hope this turns out to be useful.