I will talk about expert mode View Object (with hand written SQL), this View Object is created based on SQL join. So, thats my use case for today example. I will describe issue related to generated SQL statement and give a hint how to solve it. This is in particular useful, if you want to expose complex VO (SQL with joins and calculating totals) over ADF BC REST service and then run queries against this REST resource.
Code is available on my GitHub repository.
Here is SQL join and expert mode VO (the one where you can modify SQL by hand):
This VO is exposed through ADF BC REST, I will not go through those details, you can find more info about it online. Once application is running, REST resource is accessible through GET. ADF BC REST syntax allows to pass query string along with REST request, here I'm filtering based on StreetAddress='ABC':
On backend this works OK by default and generates nested query (this is expected behaviour for expert mode VOs, all additional criteria clauses will be added through SQL wrapping). While such query executes just fine, this is not what we want in some use cases. If we calculate totals or average aggregated values in SQL, we don't want it to be wrapped:
To prevent SQL wrapping we can call ADF BC API method in VO constructor:
While probably this works with regular ADF BC, it doesn't work with criteria coming from ADF BC REST. SQL query is generated with two WHERE clauses, after query nesting was disabled:
Possible solution proposed by me - override executeQueryForCollection method, do some parsing and change second WHERE to be AND, apply changed query string and then execute super:
This trick helps and query is generated as we would expect, criteria added from ADF BC REST query call is appended at the end of WHERE clause:
Code is available on my GitHub repository.
Here is SQL join and expert mode VO (the one where you can modify SQL by hand):
This VO is exposed through ADF BC REST, I will not go through those details, you can find more info about it online. Once application is running, REST resource is accessible through GET. ADF BC REST syntax allows to pass query string along with REST request, here I'm filtering based on StreetAddress='ABC':
On backend this works OK by default and generates nested query (this is expected behaviour for expert mode VOs, all additional criteria clauses will be added through SQL wrapping). While such query executes just fine, this is not what we want in some use cases. If we calculate totals or average aggregated values in SQL, we don't want it to be wrapped:
To prevent SQL wrapping we can call ADF BC API method in VO constructor:
While probably this works with regular ADF BC, it doesn't work with criteria coming from ADF BC REST. SQL query is generated with two WHERE clauses, after query nesting was disabled:
Possible solution proposed by me - override executeQueryForCollection method, do some parsing and change second WHERE to be AND, apply changed query string and then execute super:
This trick helps and query is generated as we would expect, criteria added from ADF BC REST query call is appended at the end of WHERE clause:
No comments:
Post a Comment