Optimize a query using active record rails

Question

The DB relation in rails models:

Class User < ActiveRecord::Base
  has_many :user_messages
  has_many :messages
end

Class UserMessage < ActiveRecord::Base
  belongs_to :user
  belongs_to :message
end

Class Message < ActiveRecord::Base
  belongs_to :user
  has_many :user_messages
end

I want to optimize the following code:

ids.each do |id|
  user = User.find_by_id(id)
  unread_count = user.user_messages.where(:folder => user.inbox_id, :read => false).count
  puts "UserID #{id} ---- Unread Message count #{unread_count}"
end

Can some tell me how can I optimize the above code using Active record or SQL Query. I basically want to reduce DB queries and time the above code take to complete the loop.

Thanks in advance.

Sounds like a job for a left join with a group by and count. Have you tried doing a basic tutorial to sql? — Shadow
– Shadow, Commented Dec 26, 2016 at 0:47
you should store an unread_count column in users table to store unread_count for each user. — Thanh
– Thanh, Commented Dec 26, 2016 at 3:46
Thanh I can store the unread_count in users tables because I insert bulk data in user_messages table using this Model.import method. — user1969191
– user1969191, Commented Dec 27, 2016 at 21:20

Sajan · Accepted Answer · 2016-12-26 10:19:17Z

You can do something like this:

users = User.where(id: ids).includes(:user_messages)
users.each do |user|
  unread_count = user.user_messages.where(:folder => user.inbox_id, :read => false).count
  puts "UserID #{user.id} ---- Unread Message count #{unread_count}"
end

Since you are eager loading user_messages, it won't fire any other DB query inside the loop. Only 1 query to fetch user and it's user_messages.

Or you can do something like:

user_messages = UserMessage.joins(:user).where('folder = users.inbox_id AND read = false').group(:user_id).count
# user_messages = {1=>10, 2=>19}
# Here keys are user ids and values are no of user_messages for that user

user_messages.each do |user_id,unread_msg_count|
  puts "UserID #{user_id} ---- Unread Message count #{unread_msg_count}"
end

PS: I have not tested any code, please comment any error if you have any. And I assumed here puts "UserID #{ids} you want to put a single user id and modified my code according to it, since in your code it will print all the ids ids.

Collectives™ on Stack Overflow

Optimize a query using active record rails

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