Hi, I would suggest to use virtual columns for that purpose and to remove user and event column from list columns set.
Activescaffold is still assuming that user and event are associations and treats them accordingly, however, you do not want that, so remove them. -- volker On Jul 28, 11:10 pm, Alan <[email protected]> wrote: > 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.
