I’ve heard a ton about this killer, new feature in Oracle Application Express (APEX) version 19.2..so I wanted to give it a go, and share it here.
If you’re not already using APEX..you SHOULD be. And if you want to read the official overview and announcement of this feature, I recommend this blog post from Carsten.
So what is Faceted Search?
Have you shopped for plane tickets lately?
The search criteria in the red box, those are ‘facets.’
Now, how cool would it be if you could browse your data with a similar approach? Without writing any code?
APEX and Low Code for the win
I’ll show you what I built first, then I’ll show you how I got there.
Here is my App’s ‘Search’ page: it has Facets I can use to ‘narrow down’ my data as I explore it, just like when I was searching Flights.
The cool part: I have NOT provided any SQL behind this part of the application – APEX is doing that for me.
Side note – Did you know that Oracle has a new motto/mantra? I think it gets to the root of what tools like APEX and SQL Developer are all about.
Our mission is to help people see data in new ways,Larry Ellison
discover insights, unlock endless possibilities.
This faceted search feature really does allow me to see my data in new ways.
How it works
I’m going to describe how this works as Mike Hichwa explained it to me. In layman’s terms, there are some rules coded into APEX to look for certain types of columns (number fields for example) and to discount or avoid other types of columns (foreign keys and primary keys). When you use the New App wizard, and include a Faceted Search page, APEX will automatically look for, and add some Facets to the page based on these rules. This way you can see it working out-of-the-box.
THAT is the ‘no code’ part.
Now, of course I can go in and change/add/remove the Facets as I’d like – and I will want to tweak the facets for my app ‘PERFECT’ for my end users.
Adding a Facet
I want to look at the kinds of beer I’ve been drinking…maybe based on the Month in which I’m enjoying them. So to get started, the query I have power the page is going to have a TO_CHAR(DATE_FIELD, ‘MON’) in the SELECT. I can then tie my Facet to this query column.
Now, you CAN have a DATE or TIMESTAMP field as a Facet, but APEX won’t add those by default, and when you DO add them, you’ll be limited to a RANGE facet type. I wanted check boxes for Month, hence my query to convert the DATE to a MON string.
Adding a Facet is extremely easy. Just ‘right click.’
Now I just need to fill in the details, but basically:
- the COLUMN…obviously
- the type of facet – you want check boxes, radio, range…
- and for those facets, do you maybe also want a count of rows
So for my MONTH facet, I can define it thus:
Wait, wait…start over. How did you build this?
Easily. I started with some CSV data.
Check over the data preview and new table column definitions…
Cross your fingers…
Click ‘Create Application,’ and then you’re going to pick the Pages you want included.
Generate and run the program, and voila!
Removing and adding a facet to my search page
Remember, APEX is super accessible.
It’s included in your Autonomous Database Cloud service – which is also now available FOR FREE.
And it’s available for any Oracle Database running pretty much anywhere. Go to https://apex.oracle.com/en/ to get started.