Optimizing database query in Rails 5

Question

I have 4 models

module Vehicle
  has_many :routes
end

module Route
  has_many :route_users
  belongs_to :vehicle
end

module RouteUser
  belongs_to :route
  belongs_to :user
end

module User
  belongs_to :route_user
end

My goal is to return the most recent driver (a user) through the aggregate.rb; to be specific, I need user id, first_name, last_name.

 
  attributes = {
    id: vehicle.id,
    ... other attributes ...
  }

  attributes.merge!(
    driver: {
      id: vehicle.routes.last.route_users.last.user.id,
      first_name: vehicle.routes.last.route_users.last.user.first_name,
      last_name: vehicle.routes.last.route_users.last.user.last_name
    }
  ) if vehicle.routes.present? && vehicle.routes.last.route_users.present?

As you can see, .merge! loads a bunch of information and dramatically slows down the aggregate.rb return. Is there any way to optimize this return to make it faster? Am I missing something?

Hi Anton, can you add ActiveRecord::Base.logger = Logger.new(STDOUT) in your code before the merge, and add the return for us in your question to help us analyse? — Rafael Costa
– Rafael Costa, Commented May 10, 2022 at 2:14

Pedro Augusto Ramalho Duarte · Accepted Answer · 2022-05-10 02:12:59Z

1

You can improve your User model to make the query easier.

class User < ApplicationRecord
  has_many :route_users
  has_many :routes, through: :route_users
  has_many :vehicles, through: :routes
end

And when the query is big from one side, the best way is two invert the logic, and make a query from user, example:

First fetch last_user_drive, and after that, use his fields to merge into attributes

last_user_driver = User.joins(routes: :vehicle).where(vehicle: {id: vehicle.id}).order('routes.created_at').last

...
attributes.merge!(
  driver: {
    id: last_user_driver.id,
    first_name: last_user_driver.first_name,
    last_name: last_user_driver.last_name
  }
) if last_user_driver.present?

answered May 10, 2022 at 2:12

Pedro Augusto Ramalho Duarte

4564 silver badges11 bronze badges

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

3 Comments

Anton S. Over a year ago

It seems that the solution does make sense; getting the following error:

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does not exist: character varying = uuid LINE 1: ...NER JOIN "route_users" ON "route_users"."user_id" = "users"....                                                              ^ HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Pedro Augusto Ramalho Duarte Over a year ago

@AntonS. I create my test models with this following commands, maybe we have differents fks, can you send your schema for this models? rails g model Vehicle rails g model Route vehicle:references rails g model User rails g model RouteUser route:references user:references

Anton S. Over a year ago

It works! The code I posted was oversimplified, which is my fault; Some tables use UUIDs, some IDs. Your solution works, thank you very much!

Collectives™ on Stack Overflow

Optimizing database query in Rails 5

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related