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:

http://platform-dev.dataloy.com/ws/rest/Cargo?pageNumber=1&limit=50&filter=voyage+.voyageHeader+.isBudget(EQ)0&filter=(OR)&filter=voyage(NULL)

encoded (+ become %2B):

http://platform-dev.dataloy.com/ws/rest/Cargo?pageNumber=1&limit=50&filter=voyage%2B.voyageHeader%2B.isBudget(EQ)0&filter=(OR)&filter=voyage(NULL)

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:

SELECT  t0.CARGO_ID  FROM TBL_CARGO t0
     LEFT JOIN TBL_VOYAGE t2
    ON (t0.VOYAGE_ID = t2.VOYAGE_ID)
     LEFT JOIN TBL_VOYAGE_HEADER t3
    ON (t2.VOYAGE_ID = t3.VOYAGE_ID)
    ORDER BY t3.REFERENCE_NO