Left join in API queries
The default behavior when an API query is executed is to put in right join the relationship between objects. From DLP 4.0.0 is possible specify in the filters of the API URL query to use left join instead of the right join.
For instance the following API query: http://platform-dev.dataloy.com/ws/rest/Cargo?pageNumber=1&limit=50&filter=voyage.voyageHeader.isBudget(EQ)0&filter=(OR)&filter=voyage(NULL)
generates the following SQL query:
SELECT t0.CARGO_ID AS c0
FROM TBL_CARGO t0 JOIN TBL_VOYAGE t1 ON (t0.VOYAGE_ID = t1.VOYAGE_ID) JOIN TBL_VOYAGE_HEADER t2 ON (t1.VOYAGE_ID = t2.VOYAGE_ID)
WHERE ((t2.IS_BUDGET = 0) OR (t0.VOYAGE_ID IS NULL)) |
That will not return cargoes with voyages null.
Instead if the following query is executed:
encoded (+ become %2B):
generates the following SQL query:
SELECT t0.CARGO_ID AS c0
FROM TBL_CARGO t0 LEFT JOIN TBL_VOYAGE t1 ON (t0.VOYAGE_ID = t1.VOYAGE_ID) LEFT JOIN TBL_VOYAGE_HEADER t2 ON (t1.VOYAGE_ID = t2.VOYAGE_ID)
WHERE ((t2.IS_BUDGET = 0) OR (t0.VOYAGE_ID IS NULL)) |
It will return also cargoes with voyage null.
The same concept is used with sort:
Executing this query:
http://platform-dev.dataloy.com/ws/rest/Cargo?sort=voyage.voyageHeader.referenceNo(AS)
the following SQL query is executed:
SELECT t0.CARGO_ID FROM TBL_CARGO t0
JOIN TBL_VOYAGE t2
ON (t0.VOYAGE_ID = t2.VOYAGE_ID)
JOIN TBL_VOYAGE_HEADER t3
ON (t2.VOYAGE_ID = t3.VOYAGE_ID)
ORDER BY t3.REFERENCE_NO |
Instead running the following query:
http://platform-dev.dataloy.com/ws/rest/Cargo?sort=voyage%2B.voyageHeader%2B.referenceNo(AS)
the following SQL query is executed: