Query in Active record with an array of hash

Question

I have the following model in Ruby

class Entity < ActiveRecord::Base
  validates :account_type, presence: true
  validates :account_id, presence: true
end

and I have an array of hashes called accounts something like:

[{'account_id':44, 'account_type':'user'},..,{'account_id':44, 'account_type':'other'}, {'account_id':88,
'account_type':'another'}]

So I want a way to obtain all the entities that match with the elements of the accounts array (account_id and account_type both at same time).

I tried using this code:

entities = []
accounts.each do |account|
    entities << Entity.where(account_id: ActiveSupport::HashWithIndifferentAccess.new(account)['account_id'])
    .where(account_type: ActiveSupport::HashWithIndifferentAccess.new(account)['account_type'])
end

But there is a way to do it more efficient ??

Since you want a record to match both attributes at the same time, then I can't think of a better way. However there may be a better way to accomplish whatever you are trying to do. Maybe there is an issue with the logic you followed that brought you to the need of that query and not the query itself. — Tashows
– Tashows, Commented Mar 19, 2019 at 22:04
Do you need to cast as HashWithIndifferentAccess, if you are only accessing by string? The two where clauses look like they could be combined into where(account_id: account['account_id'], account_type: account['account_type]), or possibly where(account) if you trust the attributes. You could also use Entity.select('id, name').where() to reduce the number of fields pulled if you don't need them. — Unixmonkey
– Unixmonkey, Commented Mar 19, 2019 at 22:15

mu is too short · Accepted Answer · 2019-03-19 22:21:18Z

3

Given this:

[{'account_id':44, 'account_type':'user'}, {'account_id':44, 'account_type':'other'}, ... ]

the SQL you want is:

select ...
where account_id = 44 and account_type = 'user'
   or account_id = 44 and account_type = 'other'
   or ...

Note that SQL's operator precedence makes that the same as:

select ...
where (account_id = 44 and account_type = 'user')
   or (account_id = 44 and account_type = 'other')
   or ...

You can build a query like that using ActiveRecord but it is a little cumbersome due to the way #or works:

accounts = your_array_of_hashes
entities = accounts.inject(Entity.none) { |memo, h| memo.or(Entity.where(h)) }

answered Mar 19, 2019 at 22:21

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

Comments

Ursus · Accepted Answer · 2019-03-19 22:23:34Z

1

If you're using rails 5 you could try or. Something like this

entities = Entity.none
items.each do |item|
  entities = entities.or(Entity.where(item))
end

This is just one SQL query but if the array is big I don't know how this works.

edited Mar 19, 2019 at 22:23

answered Mar 19, 2019 at 22:18

Ursus

30.1k3 gold badges36 silver badges52 bronze badges

Comments

ogabriel · Accepted Answer · 2019-03-20 02:08:34Z

0

If i got your problem, this should solve it:

entities = accounts.map { |acc| Entity.where(account_id: acc['account_id'], account_type: acc['account_type']) }

Let me explain what is happening:

first, the method map returns an array will all the entries that dit match with anything at the database
the map is interating through the accounts array, just like the each, which means it will bring the data at accounts to the where query
a comma between where conditions also works for comparisons, unless you are doing an or, in the case i suppose you can use this syntax: where('account_id = :id or account_type = :type', id: acc['account_id'], type: acc['account_type'])

edited Mar 20, 2019 at 2:08

answered Mar 19, 2019 at 22:30

ogabriel

3013 silver badges7 bronze badges

Collectives™ on Stack Overflow

Query in Active record with an array of hash

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related