Grouped Searches

ActiveScaffold support aggregated list by one column using field search.

Define the available columns to group by with config.field_search.group_options, which is an array of column names. The values can be symbols of column names, which will display the column’s translated name, or an array of label and column name. Instead of column name, a # string can be used, to group by column using SQL function.

config.field_search.group_options = [:customer, [:date_year, 'date#year'], [:date_quarter, 'date#year_quarter'], [:date_month, 'date#year_month'], :payment_method]

It will add a column, named active_scaffold_group, to field search form, with the next options:

<select name="search[active_scaffold_group]" id="search_active_scaffold_group">
<option value="">No group</option>
<option value="customer">Customer</option>
<option value="date#year">Date (year)</option>
<option value="date#year_quarter">Date (quarter)</option>
<option value="date#year_month">Date (month)</option>
<option value="payment_method">Payment method</option>
</select>

These keys were added to locale file for the labels of date#function options, but string can be used instead of symbol, to skip translation:

en:
  activerecord:
    attributes:
      invoice:
        date_year: Date (year)
        date_quarter: Date (quarter)
        date_month: Date (month)

If grouping by a belongs_to association column, it will group by the foreign key column.

Selecting the options like date#function will group by date column, using a SQL function, depending on the value set after #. The next function names are supported:

• year, will use year SQL function.
• month, will use year SQL function.
• quarter, will use year SQL function.
• year_month, will use extract(YEAR_MONTH FROM <column>) SQL function, to return year and month in the format YYYYMM.
• yeqr_quarter, will use YEAR(<column>) * 10 + QUARTER(<column>) to return year and quarter in the format YYYYQ.

Any other value requires to override calculation_for_group_by to return the sql code to use. The method gets 2 arguments, the column name and the group function, e.g.:

def calculation_for_group_by(column_name, group_function)
  if group_function == 'year_week'
    sql_operator(sql_operator(sql_function('year', column_name), '*', 100), '+', sql_function('extract', sql_operator(Arel::Nodes::SqlLiteral.new('WEEK'), 'FROM', column_name))) # YEAR(column_name) * 100 + EXTRACT(WEEK FROM column_name)
  else
    super
  end
end

When doing a group search, the query will load only the group column and the columns defined in list.columns which have calculation, no action links. It would look like this:

The columns to include in the grouped search can be set in config.field_search.grouped_columns, but all columns must have calculations.