Forum Stats

  • 3,767,857 Users
  • 2,252,726 Discussions
  • 7,874,366 Comments

Discussions

Faceted Search Report pagination on REST Data Source does not work after filter/selected facet

ilmarsk
ilmarsk Member Posts: 38 Red Ribbon

Hello,

Apex 20.2.0.00.20; Listener 19.4.6.r1421859; Oracle 18c XE

Running Faceted Search report on top of REST data source (simple http) . Rest does not have any pagination and always returns all records. Search Result region (classic report) attributes for Number of Rows = 50, Pagination Type = Row Range X to Y (with next and previous links), partial page refresh checked.

There are no default filter setting on the initial report load. The report returns all 50 rows (i.e. out of 350) and pagination works just fine. However, as soon the value of a facet is selected (filter applied) the report returns & displays the first 50 filtered rows, but when trying to paginate rows 51+ nothing is returned (i.e. filtered population 74 rows).

Have tried to: a) pass the facet filters to REST Data source - rest now returns 74 rows, but pagination still does not work past 50; b) various pagination settings + no partial page refresh; c) looked for optimizer hint to revert pagination back to pre 18.1 version with APEX$USE_ROWNUM_PAGINATION and APEX$USE_NO_PAGINATION hints.

Does anybody have run into the same problem? Any suggestions on how to make pagination work for filtered faceted search report from REST data source? Built in pagination is preferred, but wondering if local post processing is an option for a workaround. NOTE: Rest will always return a full set of data and does not support pagination.

Thank you.

Tagged:

Best Answers

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Member Posts: 1,257 Employee
    edited Oct 13, 2021 1:39PM Accepted Answer

    Hi,

    thank you for reporting this. I can reproduce the behavior and logged bug 33465560 for this issue.

    The good news is that this will be fixed with changes we added to the REST Data Source feature for the upcoming APEX release 21.2.

    As a workaround (as APEX 21.2 is not available yet), you might turn on the Synchronization feature for your REST Data Source to have APEX automatically synchronize the data to a local table. As your REST Source does not return too many rows, this should be rather straightforward. Once the Synchronization is enabled, navigate to Page Designer and enable the Use Synchronization Table switch in the Source settings of the report on your Faceted Search page. As soon as that is enabled, the issue should go away.

    As a side-effect you should also get better performance for your Faceted Search page, as the HTTP requests are now not required any more.

    I hope this helps

    -Carsten

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Member Posts: 1,257 Employee
    edited Oct 14, 2021 10:47AM Accepted Answer

    Hi,

    have a look into the attached file (app export was done with APEX 20.2). It contains a very basic Plug-In with some placeholder code for filter handling. But even without the filter handling, the pagination issue does not occur with this Plug-In.

    Feel free to extend the Plug-In with the filtering logic for your REST API, as needed.

    Best regards

    -Carsten

Answers

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Member Posts: 1,257 Employee
    edited Oct 13, 2021 1:39PM Accepted Answer

    Hi,

    thank you for reporting this. I can reproduce the behavior and logged bug 33465560 for this issue.

    The good news is that this will be fixed with changes we added to the REST Data Source feature for the upcoming APEX release 21.2.

    As a workaround (as APEX 21.2 is not available yet), you might turn on the Synchronization feature for your REST Data Source to have APEX automatically synchronize the data to a local table. As your REST Source does not return too many rows, this should be rather straightforward. Once the Synchronization is enabled, navigate to Page Designer and enable the Use Synchronization Table switch in the Source settings of the report on your Faceted Search page. As soon as that is enabled, the issue should go away.

    As a side-effect you should also get better performance for your Faceted Search page, as the HTTP requests are now not required any more.

    I hope this helps

    -Carsten

  • ilmarsk
    ilmarsk Member Posts: 38 Red Ribbon

    Hi Carsten,

    Thank you for a quick response. I wish I would have posted the question earlier 😀.

    Synchronization is a great feature and I use it extensively. Unfortunately, my post is just an example for testing purposes. Real data is in thousands and growing. Concerned of filling up XE data limit. Also as the data is dynamic real time is appreciated (status changes by users in the source system, etc.). I use facet filters and send them to REST Data source to get appended to URL to limit returned number of rows.

    Will attempt to build a custom pagination with post processing query. If the approach fails - will return first 100 and will wait for 21.2 😊. It's 73 days till Christmas.

    As a side note, paginated IR has the same pagination bug when going against filtered REST Data Source.

    Thank you!

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Member Posts: 1,257 Employee

    Hi,

    I think, I can offer a workaround based on a REST Source Plug-In. Give me some time to put it together, but such a plug-in can do the same thing as the Simple HTTP type, but avoid the issue of this bug.

    You could then amend the Plug-In to process filters and to pass these correctly to the REST API.

    I‘ll post something here. Stay tuned.

    best regards

    Carsten

  • ilmarsk
    ilmarsk Member Posts: 38 Red Ribbon

    Thank you Carsten! That would be great. I did attempt to use REST Source Plug-in from sourforge as an example, but could not quite make it to work to capture filtering and order by variables.

    Anything you can put together as an example that support filter and order by string creation will be appreciated.

    Thank you.

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Member Posts: 1,257 Employee
    edited Oct 14, 2021 10:47AM Accepted Answer

    Hi,

    have a look into the attached file (app export was done with APEX 20.2). It contains a very basic Plug-In with some placeholder code for filter handling. But even without the filter handling, the pagination issue does not occur with this Plug-In.

    Feel free to extend the Plug-In with the filtering logic for your REST API, as needed.

    Best regards

    -Carsten

  • ilmarsk
    ilmarsk Member Posts: 38 Red Ribbon

    Hi Carsten!

    Thank you very much for a plug-in sample! It does fix the pagination issue and provides great insight into construction of filtering capabilities for the REST call.

    However, I do have some follow up questions about filtering and order by capabilities (struggling to decide if new threads need to be open, or leave it here as it goes together with your sample code):

    a) while I am able to loop through the filter parameters in IR. The same code does not work in faceted search.

    The code:

    does not return filters set by facets (debug prints Parameter Count: 0). How does one access facet filters in REST plug-in?


    b) while running some tests I notices that filters are applied locally (looking at Generated Component SQL in debug window) regardless whether I executed "p_result.local_filters( p_result.local_filters.count + 1 ) := p_params.filters( f );" command or not. Is this a bug? Tested with IR.


    c) we can loop through order bys with this loop "for f in 1 .. p_params.order_bys.count loop", but how does one add/remove order bys from the local execution? Tried something like this, but it does not work:

    for f in 1 .. p_params.order_bys.count loop

    p_result.local_order_bys( p_params.order_bys.coun + 1 ) := p_params.order_bys( f );

    end loop;


    Thank you.

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Member Posts: 1,257 Employee

    Hi,

    happy to hear that the sample is helpful to you. Now to your questions:

    a)

    A faceted search page will lead to two debug outputs being created. One for the report, and one for the count computation. When you refresh the page, the report is the "show" debug output, while the "ajax" entry represents the counts computation.

    In the "show" request, you should see debug output for your filter - I just tested that out and it works fine for me. For the counts computations, things are a bit different. I assume you've chosen a "checkbox" facet, which is the default and the most feasible one. If you click one of the facet items, you'll see that the others are still visible with their counts. So there is no "filter" applied here; instead all rows are fetched and counted by facet item. The only facet where APEX can apply a filter is the select list facet - here no others are shown and APEX can push the filter to the REST API. Computing Counts more or less always requires APEX to have access to all rows, in order to get these successfully - I don't think that your REST API has an endpoint which returns those ready-to-use.

    An alternative of course is to turn count computation off - but that will loose a lot of "Faceted Search" experience.

    b)

    APEX typically applies all filters once more with SQL - that's a known issue. The "Pass-Back" of filters is still important, as it tells APEX that the response from the REST API contains a superset of the actual requested data, and that the final result set will most likely be only a part of it.

    c)

    That's an interesting one, which I need to investigate; thank you for this one. Right now APEX always applies the ORDER BY clause again. However, when a REST Source Plug-In supports filtering, and can potentially return huge amounts of rows, the REST API normally returns rows in pages, so the concrete JSON typically only contains a few hundred or thousand rows, for which a local ORDER BY does not hurt.

    However, sending the ORDER BY to the server is extremely important when the REST API returns data in pages; as APEX requires the pages to be returned in the right order - and that's something only the server can so. The local ORDER BY is then only processed on a single page result, which is typically not a problem.

    Does that help?

    Best regards

    -Carsten

  • ilmarsk
    ilmarsk Member Posts: 38 Red Ribbon

    Very helpful Carsten. Thank you!

    a) You are correct. The first debug prints out the filters. It does seem there is a need for another loop to parse out the colon separated list. Also, I need to do some more digging to figure out where search type filters are stored. For the time being I have opted to use filter parameter under Search Results. It does seem a little more straight forward to create a filter string from the facet variables and pass it to REST Data source via parameter to execute. Works as expected. And you are correct to get a faceted search experience I only pass the general status to REST end point to limit the population - no counts there. Everything else is done locally with counts. So, its kind of mixed experience.

    Frankly, there are two other show stopper issues currently I am dealing with. Started two new threads:


    b) will just pass them back per your recommendation;

    c) Sounds like it's always applied locally by default. Makes sense.


    Thank you again. This has been very informative.