Create hash inside loop with query

Question

I want to create a hash from data which are pulled out from db. The hash should looks like

hash { id: process.id,
       name: company_name }

To get company_name I have to do some query which returns an array of strings with company names:

Process.all.map do |process|
  process.inquiry_field_responses
         .joins(:inquiry_field)
         .where(inquiry_fields: { name: 'company_name' })
         .first&.value
end

To create this hash I thought all I should do is something like:

fetcher = []

Process.all.map do |process|
  company_name = process.inquiry_field_responses
                        .joins(:inquiry_field)
                        .where(inquiry_fields: { name: 'company_name' })
                        .first&.value
  if company_name != nil
    fetcher << {
      id: process.id,
      name: company_name,
    }
  end
end

But in the result I've:

    2.6.0 :318 > fetcher
 => [{:id=>1, :name=>nil}, {:id=>2, :name=>nil}, {:id=>3, :name=>nil}, 
{:id=>4, :name=>nil}, {:id=>5, :name=>nil}, {:id=>6, :name=>nil}, {:id=>7, 
:name=>nil}, {:id=>8, :name=>nil}, {:id=>9, :name=>nil}, {:id=>10, 
:name=>nil}, {:id=>11, :name=>nil}, {:id=>12, :name=>nil}, {:id=>13, 
:name=>nil}, {:id=>14, :name=>nil}, {:id=>15, :name=>nil}, {:id=>16, 
:name=>nil}, {:id=>17, :name=>nil}, {:id=>18, :name=>nil}, {:id=>19, 
:name=>nil}, {:id=>20, :name=>nil}, {:id=>21, :name=>nil}, {:id=>22, 
:name=>nil}, {:id=>23, :name=>nil}, {:id=>24, :name=>nil}, {:id=>25, 
:name=>nil}, {:id=>26, :name=>nil}, {:id=>27, :name=>nil}, {:id=>28, 
:name=>nil}, {:id=>29, :name=>nil}, {:id=>30, :name=>nil}, {:id=>31, 
:name=>nil}, {:id=>32, :name=>nil}, {:id=>33, :name=>nil}, {:id=>34, 
:name=>nil}, {:id=>35, :name=>nil}, {:id=>36, :name=>nil}, {:id=>37, 
:name=>nil}, {:id=>38, :name=>nil}, {:id=>39, :name=>nil}, {:id=>40, 
:name=>nil}, {:id=>41, :name=>nil}, {:id=>42, :name=>nil}, {:id=>43, 
:name=>nil}, {:id=>44, :name=>nil}, {:id=>45, :name=>nil}, {:id=>46, 
:name=>nil}, {:id=>47, :name=>nil}, {:id=>48, :name=>nil}, {:id=>49, 
:name=>nil}, {:id=>50, :name=>nil}, {:id=>51, :name=>"231421AAAAA firma"}, 
{:id=>52, :name=>"tregfsd"}, {:id=>53, :name=>"aaaaa"}, {:id=>54, 
:name=>"zzzzzz"}, {:id=>55, :name=>"bbbbb"},

Instead of:

2.6.0 :318 > fetcher
{:id=>52, :name=>"tregfsd"}, {:id=>53, :name=>"aaaaa"}, {:id=>54, 
:name=>"zzzzzz"}, {:id=>55, :name=>"bbbbb"},

Are you sure that giving yourself a N+1 query issue is really the answer here? Can you instead provide an example of the data and the desired output? meta.stackexchange.com/questions/66377/what-is-the-xy-problem — max
– max, Commented Oct 29, 2019 at 13:58
if company_name.present? will check also check if company_name might be an empty string — lacostenycoder
– lacostenycoder, Commented Oct 29, 2019 at 14:16
@lacostenycoder ok but now instead of id: 1, name: nil I've got an array with nil values at the front and then hashes with some data - => [nil, nil, nil, nil, nil, nil, [{:id=>52, :name=>"tregfsd"}, {:id=>53, :name=>"aaaaa"}]] — mr_muscle
– mr_muscle, Commented Oct 29, 2019 at 14:25

lacostenycoder · Accepted Answer · 2019-10-29 14:48:55Z

2

This type of iteration will need to be optimized to avoid N+1 query problem on large data sets. But this will probably work for you for now.

Process.all.map do |process|
  company_name = process.inquiry_field_responses
                        .joins(:inquiry_field)
                        .where(inquiry_fields: { name: 'company_name' })
                        .first&.value
  if company_name.present?
    {
      id: process.id,
      name: company_name,
    }
  end
end.compact

edited Oct 29, 2019 at 14:48

answered Oct 29, 2019 at 14:25

lacostenycoder

11.3k4 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mr_muscle Over a year ago

With this code I've TypeError (no implicit conversion of Symbol into Integer)

lacostenycoder Over a year ago

try just .compact and omit the .select

mr_muscle Over a year ago

I've mixed up something, it works with and without select. Now I've to avoid N+1 somehow.

lacostenycoder Over a year ago

@mr_muscle so is .select needed? I removed it

3limin4t0r · Accepted Answer · 2019-10-29 16:31:21Z

Instead of starting the query chain from the Process model you could opt to start from the InquiryField model.

This answer assumes the following model associations:

# app/models/process.rb
class Process < ApplicationRecord
  has_many :inquiry_field_responses
end

# app/models/inquiry_field_response.rb
class InquiryFieldResponse < ApplicationRecord
  belongs_to :process
  belongs_to :inquiry_field
end

# app/models/inquiry_field.rb
class InquiryField < ApplicationRecord
  has_many :inquiry_field_responses
end

With the assumptions out of the way let's get to the answer.

inquiry_field = inquiry_field.find_by!(name: 'company_name')
inquiry_field_responses = inquiry_field.inquiry_field_responses.where.not(value: '')

fields = %i[process_id value]
labels = %i[id name]

fetcher = inquiry_field_responses.pluck(*fields).map { |data| labels.zip(data).to_h }

I first fetch the inquiry field that describes the field you want. From there I get the inquiry field responses without empty or NULL value. Get the fields you want using the pluck method and map the resulting array. zip the labels to the data and convert the resulting array to a hash using to_h.

Collectives™ on Stack Overflow

Create hash inside loop with query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related