Sorting table by nested models attribute in Rail with Turbo Frames

Question

I am creating a table of Employees and I would like to sort by full_rate_pence, which is an attribute of HourlyRate. Each employee has_many hourly_rates which form a history of pay increases or decreases. The problem is trying to access the nested HourlyRate because the HourlyRate I need to access is within a collection. It's not possible to use .find or .find_by because of the use of :includes

Initially I received the following error:

Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "hourly_rates.first.full_rate_pence asc".This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql().

Then once wrapping the part with Arel.sql() I received the next error:

SQLite3::SQLException: no such column: hourly_rates.full

Models

class Employee < ApplicationRecord
    has_many :hourly_rates
    accepts_nested_attributes_for :hourly_rates
end

class HourlyRate < ApplicationRecord
    belongs_to :employee
    monetize :full_rate_pence, as: 'full'
end

Link In Table

<th scope="col">
  <%= sort_link(column: "hourly_rates.first.full", label: "Hourly Rate") %>
</th>

Helper Method to create link

def sort_link(column:, label:)
      if column == params[:column]
        link_to(label, list_employees_path(column: column, direction: next_direction))
      else
        link_to(label, list_employees_path(column: column, direction: 'asc'))
      end
    end

Controller method

def list
  employees = Employee
      .includes(:hourly_rates)
      .where(hourly_rates: {active:true})
      .order(Arel.sql("#{params[:column]}"))
    render(partial: 'employees', locals: { employees: employees })
end

Thanks for any advice how I can implement this.

Dan

why is `list` using the `:column` from the params directly (assuming they're passed in from a form) ... this seems to me to be incredibly unsafe. from the error message that you're getting, this column appears to be filled with `hourly_rates.first.full_rate_pence` which doesn't appear to be a bare column, and probably isn't helpful. In the past I have done column sorting in ruby like this, but I found that the most responsive way of doing it was in JavaScript (no additional database calls). have you investigated that approach? — Jad, Oct 04 '22 at 08:52
i was investigating if it was possible to do this using solely rails, however I suspect I will need to use javascript. That will be my next step. thanks — Daniele Deltodesco, Oct 04 '22 at 11:03

Jad · Answer 1 · 2022-10-05T14:19:46.710

Ruby answer:

something like:

def list
  employees = Employee
      .includes(:hourly_rates)
      .where(hourly_rates: {active:true})

  # you can add to the query before the end:
  case params[:column]
  when "Hourly Rate"
    employees = employees.order(hourly_rates.first.full)
  else
    employees = employees.order(id)
  end

  render(partial: 'employees', locals: { employees: employees })
end

Javascript sorting:

var asc = 0;
function sort_table(table, col) {
    $('.sortorder').remove();
    if (asc == 2) { asc = -1; } else { asc = 2; }
    var rows = table.tBodies[0].rows;
    var rlen = rows.length;
    if (!table.tHead) { rlen--; }
    var arr = new Array();
    var i, j, cells, clen;
    // fill the array with values from the table
    // does not like empty rows, so check your haml!
    for (i = 0; i < rlen; i++) {
        cells = rows[i].cells;
        clen = cells.length;
        arr[i] = new Array();
        for (j = 0; j < clen; j++) { arr[i][j] = cells[j].innerHTML; }
    }
    // sort the array by the specified column number (col) and order (asc)
    arr.sort(function (a, b) {
        var retval = 0;
        var col1 = a[col].toLowerCase().replace(',', '').replace('$', '').replace(' usd', '')
        var col2 = b[col].toLowerCase().replace(',', '').replace('$', '').replace(' usd', '')
        var fA = parseFloat(col1);
        var fB = parseFloat(col2);
        if (col1 != col2) {
            if ((fA == col1) && (fB == col2)) { retval = (fA > fB) ? asc : -1 * asc; } //numerical
            else { retval = (col1 > col2) ? asc : -1 * asc; }
        }
        return retval;
    });
    for (var rowidx = 0; rowidx < rlen; rowidx++) {
        for (var colidx = 0; colidx < arr[rowidx].length; colidx++) { table.tBodies[0].rows[rowidx].cells[colidx].innerHTML = arr[rowidx][colidx]; }
    }

    hdr = table.rows[0].cells[col];
    if (hdr.children.length == 0) {
        obj = hdr
    } else {
        obj = hdr.children[0]
    }
    if (asc == -1) {
        // $(hdr).html($(hdr).html() + '<span class="sortorder">▲</span>');
        $(obj).html($(obj).html() + '<span class="sortorder">▲</span>');
    } else {
        //$(hdr).html($(hdr).html() + '<span class="sortorder">▼</span>');
        $(obj).html($(obj).html() + '<span class="sortorder">▼</span>');
    }
}

calling from the view:

:javascript
  function sortTable(n) {
  sort_table(document.getElementById("indextable"), n);
  }
%table#indextable
  %thead
    %tr
      %th{ onclick: 'sortTable(0)' } Name
      %th{ onclick: 'sortTable(1)' } Machine
      %th{ onclick: 'sortTable(2)' } Method
      %th{ onclick: 'sortTable(3)' } Bands
  %tbody
    // etc.

Sorting table by nested models attribute in Rail with Turbo Frames

1 Answers1