JDev: How to reset or undo a af:table sort

This entry describes how to reset or undo a sort a user has initiated by clicking on a header in an af:table. The use case is based on a question on OTN Jdeveloper forum.
A more detailed description of the case is as follows:

  1. A use has a sortable af:table on a page. At some point he clicks the column header to sort the data.
  2. Now he wants to get back to the unsorted list which he gets is when the table is first shown.

The first part is easily accomplished by setting the sortable property of the columns of the table to true:

<af:table value="#{bindings.EmployeesView1.collectionModel}" var="row"
                          rows="#{bindings.EmployeesView1.rangeSize}"
                          emptyText="#{bindings.EmployeesView1.viewable ? 'No data to display.' : 'Access Denied.'}"
                          fetchSize="#{bindings.EmployeesView1.rangeSize}" rowBandingInterval="0"
                          selectedRowKeys="#{bindings.EmployeesView1.collectionModel.selectedRow}"
                          selectionListener="#{bindings.EmployeesView1.collectionModel.makeCurrent}"
                          rowSelection="single" id="resId1" styleClass="AFStretchWidth"
                          displayRow="selected" binding="#{RTSBean.qtable}">
                    <af:column sortProperty="#{bindings.EmployeesView1.hints.EmployeeId.name}"
                               sortable="true"
                               headerText="#{bindings.EmployeesView1.hints.EmployeeId.label}"
                               id="resId1c1">
...

Part 2 of the case isn't obvious as there is no 'unsort' or 'reset sort' button available at design time. To undo the sort you have to do two things:

  1. remove the sort criteria from the table (this will remove the arrows in the column header)
  2. remove the sort criteria from the underlying iterator of the table and execute the query again (this gets the data unsorted from the DB)

Now that we know what to do let's implement it. You'll find a link to a working workspace at the end of the article.

public class RTSBean
{
    private RichTable qtable;

    public RTSBean()
    {
    }


    public void resetSort(ActionEvent actionEvent)
    {
        // get the binding container
        BindingContainer bindings = BindingContext.getCurrent().getCurrentBindingsEntry();
        DCBindingContainer dcBindings = (DCBindingContainer) bindings;
        RichTable table = getQtable();
        table.queueEvent(new SortEvent(table, new ArrayList<SortCriterion>()));
        DCIteratorBinding iterBind = (DCIteratorBinding) dcBindings.get("EmployeesView1Iterator");
        Key currentRow = null;
        Row row = iterBind.getCurrentRow();
        if (row != null)
            currentRow = row.getKey();
        SortCriteria[] sc = new SortCriteria[0];
        iterBind.applySortCriteria(sc); // iterBind is the iterator the table uses
        iterBind.executeQuery();
        if (currentRow != null)
            iterBind.setCurrentRowWithKey(currentRow.toStringFormat(true));
    }

    public void setQtable(RichTable qtable)
    {
        this.qtable = qtable;
    }

    public RichTable getQtable()
    {
        return qtable;
    }
}

I'm using the method resetSort(ActionEvent actionEvent) to do the work. I bound the table to a bean property to make it easily accessible in the method. You can also search for the table component in the component tree (using the id of the table) if you like.
The method first queues a new sort event to the table with an empty list of sort criterion. This removes the sort arrows from the column header. Next it gets the iterator which is used by the table and saves its current row. I do this to re-select the row after I executed the query again. This way the user still sees the selected row after the sort is reset. An empty array of sort criteria is created and applied to the iterator removing all sort criteria currently applied. The query of the iterator gets executed and finally the selected row is set again (if it was set before).

This successfully resets a table to unsorted mode. This picture show the table after an initial search (without criteria entered in the name field):

Unsortet table after first load

Unsortet table after first load

Next I sorted the 'salary' column and selected the Row with employee_id 162:

Sorted table with row 162 selected

Sorted table with row 162 selected

The final picture shows the table after a click on the 'Reset Sort' button:

Table after 'Reset Sort'

Table after 'Reset Sort'


As you see the row with 'EmployeeId' 162 is still selected and scrolled into first position as the table is set to displayRow="selected".

The sample workspace uses JDeveloper 11.1.2.1.0 but the bean code should work in older JDeveloper version 11.1.1.x too. The sample used the HR schema as DB connection.
You can download the sample workspace from BlogResetTableSort.zip
After downloading the file rename it to 'BlogResetTableSort.zip'!

Disclaimer and Acknowledgment: I remember an old blog post by Frank Nimphius showing this too. However, I couldn't find it on the web and decided to show how to do it again.

About these ads

3 thoughts on “JDev: How to reset or undo a af:table sort

  1. Nice work.
    If you want to make it more generic you can get iteratorBinding from table

    CollectionModel cm = (CollectionModel)table.getValue();
    JUCtrlHierBinding tb = (JUCtrlHierBinding)cm.getWrappedData();
    DCIteratorBinding iterBinding = tb.getDCIteratorBinding();

  2. I found a issue with this approach, so thought of reporting it.
    Issue : Your Employee VO already has a order by defined in SQL query. Ex- OrderBy Salary.
    Now if we try to run this application, everything works fine except the selected Row is not in correct order. Don’t know why ?

    • Well, this is not an issue with the approach. When you set a sort order via the af:table the original ‘order by’ clause is stripped from the query.
      So this is the expected behavior. It is just that the table sort works this way.
      You can get the original ‘order by’ clause from the view objects definition object and apply it back to the VO before executing the query again. this should again sort the data.

      Timo

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s