Rails 3 - select with Include?

Here is a nested select with include:

@items = Item.where("complete = ?", true).includes( :manufacturer, {:order=>[:supplier, :agent开发者_StackOverflow中文版] })

This is a taxing query as it pulls 1000s of rows of data from all the above included tables.

How can I get the query to only select specific fields?

• user.name, user.created_at
• order.created_at
• supplier.name
• agent.name
• manufacturer.name

---

There is a select method in ARel, but you must use the correct table names (i.e. plural and beware if you have polymorphic models or if you're using set_table_name or some other similar non-standard practice)

@items = Item.
  select('users.name', 'users.created_at', 'orders.created_at', 'suppliers.name', 'agents.name', 'manufacturers.name').
  where(:users => { :fulfilled => true }).
  includes(:orders => [:supplier, :agent], :manufacturer)

"We can use select with joins not includes" - @Bhavesh_A_P

Note:

As @Bhavesh_A_P pointed out above, select with includes does not produce consistent behavior. It appears that if the included association returns no results, select will work properly, if it returns results, the select statement will have no effect. In fact, it will be completely ignored, such that your select statement could reference invalid table names and no error would be produced. select with joins will produce consistent behavior.

---

Actually we can't use select with includes. It can only be used with joins.

---

The problem is not solved by including a call to the 'select' method in the chain. In a similar ActiveRecord::Relation we built, calling 'includes' seems to override any call to 'select'.

scope :active, where("hired_on < ? AND (separated_on > ? OR separated_on IS NULL)", Time.now, Time.now )

scope :with_role, lambda {|roles| includes(:team_member_roles).where(:team_member_roles => {:role => roles } ) }

scope :with_site_code, lambda {|site_codes| includes(:team_member_sites).where(:team_member_sites => {:site_code => site_codes } ) }

TeamMember.select("team_members.email, team_members.first_name, team_members.last_name").active.with_site_code(params[:site_code]).with_role(["senior_editing", "senior_and_reg_editing"]) 

As shown, the query selects all columns.

When the 2 scopes use 'joins' instead of 'includes', the query works: only the 3 specified columns are selected.

---

Item.where("fulfilled = ?", true)
    .includes({:orders =>[:suppliers, :agents]}, :manufacturers)
    .select("users.name, users.created_at, orders.created_at, suppliers.name, agents.name").
    .order('orders.created_at DESC')

---

As other people have noted, using .select(...) gets overridden if there's an .includes(...). And I didn't like that ... so I figured out a patch to make it work and released it as a part of The Brick.

By overriding ActiveRecord::Associations::JoinDependency.apply_column_aliases() like this then when you add a .select(...) then it can act as a filter to choose which column aliases get built out.

With gem 'brick' loaded, in order to enable this selective behaviour, add the special column name :_brick_eager_load as the first entry in your .select(...), which turns on the filtering of columns while the aliases are being built out. Here's an example:

Employee.includes(orders: :order_details)
        .references(orders: :order_details)
        .select(:_brick_eager_load,
                'employees.first_name', 'orders.order_date', 'order_details.product_id')

Because foreign keys are essential to have everything be properly associated, they are automatically added, so you do not need to include them in your select list.

Hope it can save you both query time and some RAM!