CODEWITHSUNDEEP
mysqlsqlruby-on-railspostgresql
user1969191
user1969191

Reputation: 282

Optimize a query using active record rails

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.

Upvotes: 0

Views: 298

Answers (1)

Sajan
Sajan

Reputation: 1923

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.

Upvotes: 1

Related Questions