CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecord
Edward J. Stembler
Edward J. Stembler

Reputation: 2042

How do I get Rails ActiveRecord to generate optimized SQL?

Let's say that I have 4 models which are related in the following ways:

In my Schedule#index view I want the most optimized SQL so that I can display links to the Schedule's associated Project, Client, and User. So, I should not pull all of the columns for the Project, Client, and User; only their IDs and Name.

If I were to manually write the SQL it might look like this:

select 
    s.id, 
    s.schedule_name, 
    s.schedule_type, 
    s.project_id, 
    p.name project_name, 
    p.client_id client_id, 
    c.name client_name, 
    s.user_id, 
    u.login user_login, 
    s.created_at, 
    s.updated_at, 
    s.data_count
from      
    Users u inner join 
        Clients c inner join 
            Schedules s inner join 
                Projects p
            on p.id = s.project_id
        on c.id = p.client_id
    on u.id = s.user_id
order by 
    s.created_at desc

My question is: What would the ActiveRecord code look like to get Rails 3 to generate that SQL? For example, somthing like:

@schedules = Schedule. # ?

I already have the associations setup in the models (i.e. has_many / belongs_to).

Upvotes: 1

Views: 141

Answers (1)

miked
miked

Reputation: 4499

I think this will build (or at least help) you get what you're looking for:

Schedule.select("schedules.id, schedules.schedule_name, projects.name as project_name").joins(:user, :project=>:client).order("schedules.created_at DESC")

should yield:

SELECT schedules.id, schedules.schedule_name, projects.name as project_name FROM `schedules` INNER JOIN `users` ON `users`.`id` = `schedules`.`user_id` INNER JOIN `projects` ON `projects`.`id` = `schedules`.`project_id` INNER JOIN `clients` ON `clients`.`id` = `projects`.`client_id`

The main problem I see in your approach is that you're looking for schedule objects but basing your initial "FROM" clause on "User" and your associations given are also on Schedule, so I built this solution based on the plain assumption that you want schedules!

I also didn't include all of your selects to save some typing, but you get the idea. You will simply have to add each one qualified with its full table name.

Upvotes: 0

Related Questions