Here is a simplified snippet of code that I have:
=============================================================================
class Approval < ActiveRecord::Base
belongs_to :user
belongs_to :event
end
-----------------------------------------------------------------------------
class Admin::ApprovalsController < Admin::AdminController
active_scaffold do |config|
config.columns = [:user, :event, :status, :created_at]
end
end
-----------------------------------------------------------------------------
module Admin::ApprovalsHelper
end
-----------------------------------------------------------------------------
class User < ActiveRecord::Base
def to_label
"test"
end
end
=============================================================================
By default, this will execute the following SQL:
←[1m←[36mSQL (0.0ms)←[0m ←[1mSELECT COUNT(*) FROM `approvals`←[0m
←[1m←[35mApproval Load (1.0ms)←[0m SELECT `approvals`.* FROM
`approvals` ORDER BY `approvals`.`id` ASC LIMIT 15 OFFSET 0
←[1m←[36mUser Load (0.0ms)←[0m ←[1mSELECT `users`.* FROM `users`
WHERE (`users`.`id` IN (106,9,2820,1,2,3351,500))←[0m
←[1m←[35mEvent Load (1.0ms)←[0m SELECT `events`.* FROM `events`
WHERE (`events`.`id` IN (24,25,26,15,1))
Is it possible to optimize this, so that it only loads the columns
that I specify? and so that it does it in a single query w/ joins?
If I use this:
def custom_finder_options
{:select => "approvals.id, approvals.status, approvals.created_at,
user_id, users.first_name, users.last_name, event_id,
events.title", :joins => [:user, :event]}
end
It executes this:
SELECT approvals.id, approvals.status, approvals.created_at, user_id,
users.first_name, users.last_name, event_id, events.title FROM
`approvals` INNER JOIN `
users` ON `users`.`id` = `approvals`.`user_id` INNER JOIN `events` ON
`events`.`id` = `approvals`.`event_id` ORDER BY `approvals`.`id` ASC
LIMIT 15 OFFSET 0
This is all the data that I need to render the list and links on the
list. No other queries are needed. However, it still runs these two
queries:
←[1m←[35mUser Load (0.0ms)←[0m SELECT `users`.* FROM `users` WHERE
(`users`.`id` IN (106,9,2820,1,2,3351,500))
←[1m←[36mEvent Load (0.0ms)←[0m ←[1mSELECT `events`.* FROM `events`
WHERE (`events`.`id` IN (24,25,26,15,1))←[0m
If I add this:
config.columns[:user].includes = nil
Then it just runs a bunch of individual queries:
←[1m←[35mUser Load (0.0ms)←[0m SELECT `users`.* FROM `users` WHERE
`users`.`id` = 9 LIMIT 1
←[1m←[36mCACHE (0.0ms)←[0m ←[1mSELECT `users`.* FROM `users` WHERE
`users`.`id` = 9 LIMIT 1←[0m
←[1m←[35mUser Load (1.0ms)←[0m SELECT `users`.* FROM `users` WHERE
`users`.`id` = 500 LIMIT 1
...
If I change the custom_finder_options to use an :includes instead
of :joins, like this:
def custom_finder_options
{:select => "approvals.id, approvals.status, approvals.created_at,
user_id, users.first_name, users.last_name, event_id,
events.title", :includes => [:user, :event]}
end
Then it doesn't run the extra queries, but it does return ALL the
columns for ALL 3 models (50+ columns per record)
Assuming I can get that narrowed down to select only the relevant
columns, I ran into an issue when adding a helper to display the first/
last name for the user. Like this:
def user_column(record)
"#{record.first_name} #{record.last_name}"
end
The record argument getting passed into the user_column override only
contains real column data for the approval record. It doesn't contain
either of the user or event fields that were returned in the query.
undefined method `first_name' for #<Approval:0x9ab6708>
Any suggestions?
Thanks,
Alan
On Jul 14, 6:58 am, vhochstein <[email protected]> wrote:
> Hi,
>
> if you do no want to use ActiveRecord associations I would suggest to
> simply remove that users column from the list.columns.
>
> --
> Volker
>
> On Jul 14, 6:24 am, Alan <[email protected]> wrote:
>
>
>
>
>
>
>
> > So far, the cleanest method I have for accomplishing this:
> > 1) set the column as [:user_id], NOT as an associated column ([:user])
> > 2) override the user_id_column helper to display the link
> > 3) add a class to the user_id column to force left justification
>
> > This keeps AS from executing the additional query.
>
> > Alan
>
> > On Jul 13, 3:34 pm, Alan <[email protected]> wrote:
>
> > > My active scaffold controller utilizes custom_finder_options and joins
> > > to for the list action. It has an association for "user", which I have
> > > set up for a show link when clicked on in the AS list.
>
> > > My main list query already retrieves user_id, users.first_name,
> > > users.last_name, so I have that data available. However, an extra
> > > query gets executed for this association:
> > > SELECT `users`.* FROM `users` WHERE SELECT `users`.* FROM `users`
> > > WHERE (`users`.`id` IN (12,20,10))
>
> > > I realize this is a query for eager loading, but it is unneeded. I
> > > have tried disabling eager loading by setting "includes = nil", but it
> > > still makes a query for each individual record. I want to completely
> > > disable this extra query and have my users_column helper just display
> > > the first_name + last_name.
>
> > > What is the best way to approach this?
> > > I have disabled eager loading and found out where to conditionally
> > > omit this, but it is a mess. Is there a configuration option or a
> > > cleaner/simpler way to override a method to disable any querying on an
> > > associated model?
--
You received this message because you are subscribed to the Google Groups
"ActiveScaffold : Ruby on Rails plugin" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/activescaffold?hl=en.
