Upgrading Dynamic Sort to 22.1 Order By Page Item

Overview

Your end users appreciate seeing your application data in the order that best suits the task at hand. While some region types like Interactive Report offer end-user sorting as a native capability, other popular types like Cards lacked this feature. While it was definitely possible before APEX 22.1 to create Cards regions with user-controlled sorting, the new Order By Page Item feature in APEX 22.1 now makes it incredibly easy to implement. This article explores upgrading an existing dynamic sorting cards region to the new 22.1 Order By Page Item to improve the clarity and maintainability of your application.

Understanding the Existing Implementation

We’ll look at upgrading an existing APEX 21.2 example app with a single Cards page showing a list of friends (Download). The region’s select list page item allows end users to sort the friends list by name, age, or time you’ve known the person.

The page contains a P1_SORT_ORDER select list item with a static list of values showing the end-user the different available sorting orders, with a corresponding code value. The default value for this page item is set to the value NAME so the default sorting order will be alphabetical by friend’s name. The page item uses the Maintain Session State setting of Per User (Disk), so the user’s preferred sort order is remembered automatically across sessions:

The cards region’s data source is a Function Body returning a SQL Query. It returns a SQL query with an ORDER BY clause determined using a CASE statement that depends on the value of the :P1_SORT_ORDER page item as shown below. Note that the NVL() function is used here to ensure that the APEX builder can successfully parse the function body’s resulting SQL query at design time (where the value of the :P1_SORT_ORDER bind variable will be null).

return  q'[  select id,
                   name,
                   to_char(birthday,'Mon fmdd') born_on,
                   trunc((sysdate - birthday)/365) age,
                   static_image_file_name,
                   apex_util.get_since(met_on) met_them
              from friends
              order by
        ]'||
        case NVL(:P1_SORT_ORDER,'NAME')
            when 'NAME'               then 'name     asc'
            when 'AGE_OLDEST_FIRST'   then 'birthday asc'
            when 'AGE_YOUNGEST_FIRST' then 'birthday desc'
            when 'KNOWN_LONGEST'      then 'met_on   asc'
            when 'KNOWN_SHORTEST'     then 'met_on   desc'
        end;

Notice the order by clauses use a combination of ascending and descending sorting clauses. Finally, the P1_SORT_ORDER page item is configured with a Page Action on Selection property set to Submit Page, so the page re-renders to reflect the new sorting order. This also has the side-effect of sending the new value of the P1_SORT_ORDER item to the server so it can be saved into per-user session state by the APEX engine.

Setting the Order By Page Item

After importing the 21.2 Friends starting app into an APEX 22.1 workspace, editing the page, and selecting the Friends cards region in the Page Designer, we see it currently has No Order By Item.

Clicking on the No Order By Item button opens the Order By Item dialog where we can choose the existing P1_SORT_ORDER item in the page as the Order By Item for this region. After doing this, the dialog helpfully updates automatically to reflect the display and return values of the existing list, reminding us of which sorting option key values we need to provide ORDER BY clauses for:

We proceed to fill in the Clause field for each entry, using the ORDER BY clause fragments currently returned by the CASE statement in the Function Body Returning SQL Query code. After completing this task, the dialog will look like what you see below:

After clicking OK, the Order By Item reflects the name of the order by item and indicates how many Order By options are available in the related select list:

Simplifying the Region Query

Now that we’ve “refactored” the dynamic query clause selection to be done declaratively using the new Order By Item, we can simplify the Friends region source to be an easier-to-read-and-maintain SQL query instead of the function body returning SQL query. We start by copying the text of the SELECT statement to the clipboard so we’ll be able to easily paste it into the SQL Query property after changing the region source type. Next, we change the region source type to SQL Query. Then, we paste the query saved in the clipboard into the SQL Query property to result in the following situation. Notice that we left out the order by text from the query that was there before because the APEX engine will add that for us at runtime.

Trying Out the First Cut

If we run the application it appears to work fine, showing us the cards with our friends’ smiling faces initially sorted by the default NAME column value. However, if we choose one of the other sort orders, we get a runtime error like this:

ORA-00904: "BIRTHDAY": invalid identifier ORA-06512: at "APEX_220100.WWV_FLOW_PAGE", line 2062 ORA-06512: at "APEX_220100.WWV_FLOW_DISP_PAGE_PLUGS", line 1576 ORA-06512: at "APEX_220100.WWV_FLOW_CARD_REGION", line 1099 ORA-06512: at

This error occurs because the ORDER BY clause that the APEX engine adds at runtime based on the value of the region’s Order By Item is applied in an outer query that “wraps” the region’s original SQL query, using it as an inline view. In fact, after choosing to sort by age, the error dialog or APEX debug log shows the query in error. Note that I’ve added some additional comments and removed some query hints for clarity.

select *
from (
  select a.*,row_number() over (order by null) apx$rownum 
  from (
    select *
    from (
      select *
      from (
       /* ---vvv--- REGION SQL QUERY ---vvv--- */

       select id,
              name,
              to_char(birthday,'Mon fmdd') born_on,
              trunc((sysdate - birthday)/365) age,
              static_image_file_name,
              apex_util.get_since(met_on) met_them
       from friends

       /* ---^^^--- REGION SQL QUERY ---^^^--- */
      ) d
    ) i 
    /* ---vvv--- ORDER BY ITEM CLAUSE ---vvv--- */

    order by birthday asc

    /* ---^^^--- ORDER BY ITEM CLAUSE ---^^^--- */
  ) a
)
where apx$rownum <= :p$_max_rows

Adding Columns to the SELECT List of the Region’s Query

To avoid the error, we need to study the ORDER BY clauses in play in the Order By Item and ensure that any column names referenced by those order by clauses are included in the SELECT list of the region. This guarantees that they will “shine through” to the outer, wrapping SQL statement where the dynamic ORDER BY is applied.

In our Friends example app, this means adding the BIRTHDAY and MET_ON date columns into the region’s SQL Query select list so that the query now becomes:

select id,
       name,
       to_char(birthday,'Mon fmdd') born_on,
       trunc((sysdate - birthday)/365) age,
       static_image_file_name,
       apex_util.get_since(met_on) met_them,
       birthday,
       met_on
  from friends

After doing this adjustment to the SELECT list, rerunning the application shows that the query works perfectly with all of the configured dynamic sorting options. However, the page appears to be getting refreshed twice each time the end-user changes the sort order. We’ll fix that next.

Avoiding Double Page Refresh

While the query with dynamic order by now executes without an error, the presence of two spinning progress indicators (and the time required to refresh the page) gives the impression that the page is being refreshed twice.

This effect is the result of the following two factors:

1. The original page’s P1_SORT_ORDER page item is configured with a Page Action on Selection property set to Submit Page, so the page re-renders to reflect the new sorting order, and
2. The APEX 22.1 Order By Page Item feature automatically refreshes the region when the related order by page item’s value is changed.

The solution to the double-refresh issue is restoring the P1_SORT_ORDER page item’s Page Action on Selection property value to the None setting. This avoids its submitting the page since that action is no longer necessary.

With this change in place, the dynamic sorting is now using the maximally-declarative approach, we’ve improved the readability and maintainability of our region’s SQL query, and the page refreshes a single time and is looking great.

Updating the Theme to Use New Sort Order Position

APEX 22.1’s updated Universal Theme adds a new Sort Order template layout position designed to contain the region’s components that the end-user uses to configure the region’s sort order. To have our upgraded Friends page use this new layout position, we need to refresh our application’s theme before the Page Designer will show us this new position name.

So, I navigated to Shared Components and clicked the Refresh Theme button at the top of the page.

After visiting the User Interface application settings to restore the application’s Redwood Light theme style, returning to edit the page in the Page Designer allows us to now set the P1_SORT_ORDER page item to have the new Sort Order layout position:

Conclusion

With these changes in place, we have upgraded our existing dynamic sorting implementation for a 21.2 cards region to leverage the latest 22.1 declarative region sorting feature. The result is an application that is easier for colleague developers to understand and maintain. It also ensures the existing pages offering dynamic sorting are implemented in the same way that new pages created in 22.1 will be when dynamic sorting is added automatically by the new Create Page wizard. If you care to study the before and after applications, you can download the upgraded 22.1 version of the Friends example app from here.