How to convert ActiveRecord SQL query to array of hash without instanciating ActiveRecord objects?

Question

I am developing a rails 4 app using ActiveRecord models for my db tables. The main issue is that my model is quite complicated, and I would like to display a lot of information when I do an index of the main object.

For example, let's assume I have the following tables and columns:

Person:       name(string)
Address:      address(string), person_id(int)
EmailAddress: email(string), person_id(int)
Email:        spam (boolean), email_address_id(int)

and the relations:

person        has_many: :email_addresses
person        has_one:  :address
email_address has_many: :emails

Now I would like to display the following information

person.name
person.address.name
person.email_addresses.count
person.email_addresses.map do |email_address| 
  email_address.email.where(spam: false).count
end

The main issue is that I have a big amount of records, and I don't want to instantiate all of them (I have some memory issues because of that). Therefore, I was wondering how to do this kind of thing directly to get either an array of hashes or of arrays.

I managed to get the beginning using pluck:

Person.joins(:address).pluck('persons.name, addresses.address')

The problem begins with the count part.

Has someone already encountered such a situation? And is there a way to do this without writing the complete SQL query?

What's the database you are using ?

Arup Rakshit
– Arup Rakshit

2015-02-24 16:12:51 +00:00
Commented Feb 24, 2015 at 16:12 — Arup Rakshit
– Arup Rakshit, Commented Feb 24, 2015 at 16:12
HI. I have a postgres database.

Vincent Algayres
– Vincent Algayres

2015-02-24 18:40:40 +00:00
Commented Feb 24, 2015 at 18:40 — Vincent Algayres
– Vincent Algayres, Commented Feb 24, 2015 at 18:40

dgilperez · Accepted Answer · 2015-03-02 20:57:12Z

You can't use pluck for complex queries, but you can always use select to fetch the columns you want. First you join all the tables you need. Note I joined emails table twice, the second one with the spam: false condition. Then you define your columns, directly from the table or COUNT'ed, in your select statement:

persons = Person.joins(:address, email_addresses: :emails).
   joins('INNER JOIN emails not_spammy_email_addresses ON emails.email_address_id = email_addresses.id AND emails.spam = 0').
   select('persons.name, addresses.address AS address_address,
           COUNT(email_addresses.id) AS email_addresses_count, 
           COUNT(not_spammy_email_addresses.id) AS not_spammy_email_addresses_count')

And then call your result's columns like this:

person = persons.first
person.name
person.address_address # note I'm not using *address* to prevent conflict with the model Adress 
person.email_addresses_count
person.not_spammy_email_addresses_count

I believe this is as far as you can get with active_record and a single query, but I'd love to see other approaches. For instance, if you use Arel this query would feel less SQLish.

Collectives™ on Stack Overflow

How to convert ActiveRecord SQL query to array of hash without instanciating ActiveRecord objects?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related