The JPA Processor fills the gap between Olingo V4 and the database, by providing a mapping between JPA metadata and OData metadata, generating queries and supporting the entity manipulations.
Apache License 2.0
121
stars
76
forks
source link
v 2.1.1 Unexpected SQL being generated when ODATA`s $select command contains only @ElementCollection fields #331
@wog48, It would be great if you could check and leave your thoughts on my question. My scenario is simple, I`m trying explicitly select more then 1 @ElementCollection field using $select.
Example to reproduce using JPA entities:
Organizations('1')?$select=Comment,Comment2
Where Comment and Comment2 are both @ElementCollection fields.
I had to modifiy Organization Entity as it didn`t contain another @ElementCollection. Below is how comment2 definition looks like:
Example Odata call is being converted into 3 SQL statements:
Organization search [OK]:
SELECT E0."ID" S0 FROM "OLINGO"."BusinessPartner" E0 WHERE ((E0."ID" = ?) AND (E0."Type" = ?)) LIMIT 2147483647 OFFSET 0
Comment search [NOT OK]
SELECT DISTINCT E0."ID" S0, E1."Text" S1, E2."Text" S2 FROM "OLINGO"."BusinessPartner" E0 INNER JOIN "OLINGO"."Comment" E1 ON (E0."ID" = E1."BusinessPartnerID") INNER JOIN "OLINGO"."Comment" E2 ON (E0."ID" = E2."BusinessPartnerID") WHERE ((E0."ID" = ?) AND (E0."Type" = ?)) ORDER BY E0."ID" ASC
Considering this as NOT OK because
SELECT DISTINCT E0."ID" S0, E1."Text" S1, E2."Text" contains E2 refferences. And E2 must be retrieved by 3-rd SQL call.
Comment2 search [NOT OK]
SELECT DISTINCT E0."ID" S0, E1."Text" S1, E2."Text" S2 FROM "OLINGO"."BusinessPartner" E0 INNER JOIN "OLINGO"."Comment" E1 ON (E0."ID" = E1."BusinessPartnerID") INNER JOIN "OLINGO"."Comment" E2 ON (E0."ID" = E2."BusinessPartnerID") WHERE ((E0."ID" = ?) AND (E0."Type" = ?)) ORDER BY E0."ID" ASC
Considering this as NOT OK because
SELECT DISTINCT E0."ID" S0, E1."Text" S1, E2."Text" contains E1 refferences. And E1 must be retrieved by 2-nd SQL call.
I`m considering Statements 2 and 3 as NOT OK beacase the SQL query is done not to same table (Assuming we really have table "Comment2" in DB schema) then output will be:
statement 2 will return us:
"ID1", "Text1", null
"ID2", "Text2", null
@wog48, It would be great if you could check and leave your thoughts on my question. My scenario is simple, I`m trying explicitly select more then 1 @ElementCollection field using $select.
Example to reproduce using JPA entities:
Organizations('1')?$select=Comment,Comment2
Where Comment and Comment2 are both @ElementCollection fields. I had to modifiy Organization Entity as it didn`t contain another @ElementCollection. Below is how comment2 definition looks like:Example Odata call is being converted into 3 SQL statements:
SELECT E0."ID" S0 FROM "OLINGO"."BusinessPartner" E0 WHERE ((E0."ID" = ?) AND (E0."Type" = ?)) LIMIT 2147483647 OFFSET 0
SELECT DISTINCT E0."ID" S0, E1."Text" S1, E2."Text" S2 FROM "OLINGO"."BusinessPartner" E0 INNER JOIN "OLINGO"."Comment" E1 ON (E0."ID" = E1."BusinessPartnerID") INNER JOIN "OLINGO"."Comment" E2 ON (E0."ID" = E2."BusinessPartnerID") WHERE ((E0."ID" = ?) AND (E0."Type" = ?)) ORDER BY E0."ID" ASC
Considering this as NOT OK becauseSELECT DISTINCT E0."ID" S0, E1."Text" S1, E2."Text"
contains E2 refferences. And E2 must be retrieved by 3-rd SQL call.SELECT DISTINCT E0."ID" S0, E1."Text" S1, E2."Text" S2 FROM "OLINGO"."BusinessPartner" E0 INNER JOIN "OLINGO"."Comment" E1 ON (E0."ID" = E1."BusinessPartnerID") INNER JOIN "OLINGO"."Comment" E2 ON (E0."ID" = E2."BusinessPartnerID") WHERE ((E0."ID" = ?) AND (E0."Type" = ?)) ORDER BY E0."ID" ASC
Considering this as NOT OK becauseSELECT DISTINCT E0."ID" S0, E1."Text" S1, E2."Text"
contains E1 refferences. And E1 must be retrieved by 2-nd SQL call.I`m considering Statements 2 and 3 as NOT OK beacase the SQL query is done not to same table (Assuming we really have table "Comment2" in DB schema) then output will be: statement 2 will return us: "ID1", "Text1", null "ID2", "Text2", null
statement 3 will return us: "ID1", null, "Text1" "ID2", null, "Text2"
and general result will be looking: ID:1, Comment: [Text1, null], Comment2: [null, Text1] ID:2, Comment: [Text2, null], Comment2: [null, Text2]
Instead of: ID:1, Comment:[Text1], Comment2:[Text1] ID:2, Comment:[Text2], Comment2:[Text2]