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 group_by column 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:

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

Selecting the options 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 an argument with the column name, and can get the used function by calling search_group_function, e.g.:

def calculation_for_group_by(column_name)
  if search_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