OQL LEFT OUTER JOIN with entity path in the ON constraint results an INNER JOIN in SQL

I have the following entities: I (1←*) V (*→1) A I need to retrieve the list of I/ID sorted by V/Value for V objects having certain V/V_A/A/ID My OQL select i/ID from Mdl.I i left join i/Mdl.V_I/Mdl.V v on v/Mdl.V_A/Mdl.A/ID = $A_ID order by v/Value DESC LIMIT 21 OFFSET 60 Generated SQL SELECT "i"."id" FROM "mdl$i" "i" LEFT OUTER JOIN "mdl$v_i" "atj1Mdl$V_I" ON "atj1Mdl$V_I"."mdl$iid" = "i"."id" LEFT OUTER JOIN "mdl$v" "v" ON "v"."id" = "atj1Mdl$V_I"."mdl$vid" INNER JOIN "mdl$v_a" "atj2Mdl$V_A" ON "atj2Mdl$V_A"."mdl$vid" = "v"."id" WHERE "atj2Mdl$V_A"."mdl$aid" = ? ORDER BY "v"."value" DESC LIMIT ? OFFSET ? I expected “V_A” to also be joined with LEFT JOIN because it’s part of the constraint in a LEFT JOIN in OQL. As the result, entities of type “I” not having associated “V” are not returned. How to rewrite the OQL query above?
2 answers

Hi ilya,

First, your question is fairly vague. OQL is also not something that everyone here can necessarily do I think and that is why it is difficult to answer this.

More importantly, why are you using OQL and not the standard component of Mendix?


I managed to solve the problem. TL;DR; a join-table can be joined alone in OQL (by exploiting the OQL optimizer) and you can explicitly opt to use INNER, LEFT, or RIGHT join on each side individually.

Here’s the desired SQL (simplified):

select I.id
from I
left join (
  select V_I.iid, V.Value
  from V
  inner join V_A on V_A.vid = V.id
  inner join V_I on V_I.vid = V.id
  where V_A.aid = ?
) v on v.iid = I.id
order by v.Value DESC

As you can see, the join between V and both join-tables must be inner otherwise we get extra rows.

Naturally, I expected that when you use an “ON constraint” in OQL then a subquery would be generated, but instead it just adds one of the join-tables to the outer SQL killing the outer join.

First thing I tried was LEFT-joining an OQL subquery, but the OQL parser refused because subquery is only allowed immediately after the “FROM” keyword. So I swapped them and changed a LEFT join into a RIGHT join. The syntax was now correct, but the unpleasant finding was that all my constraints after the keywords “ON” and “WHERE” were now being stripped from the resulting SQL leading to completely unfiltered results. Of course that behavior was not documented.

So I unwrapped the subquery with all the INNER joins and tried to RIGHT-join “v/V_I/I”, but now more rows than needed were being returned because it generated “RIGHT JOIN V_I” instead of just “RIGHT JOIN I” and the “V_I” rows not associated with the filtered “V” were also being fetched.

The solution

In OQL an entity path like v/V_I/I/ID does not lead to joining the right-side table in SQL because the join-table already contains the needed value: “v_i.iid”. It’s a neat optimization. An entity path generates an INNER join between “V” and “V_I” and we now only need to RIGHT join the table “I”:

select i/ID
 from V v
 inner join v/V_A/A a on a/ID = $A_ID
 inner join v/V_I/I iref
 right join I i on i/ID = iref/ID
 order by v/Value DESC
  • The OQL was simplified (no module prefixes)
  •  I’m taking this out of my head so there can be typos
  • Notice that I’m joining “I” twice: with and without using an entity path
  • In the generated SQL both “I” and “V_I” are mentioned only once
select i.id
from v
inner join v_a on v.id = v_a.vid and v_a.aid = ?
inner join v_i on v.id = v_i.vid
right join i on i.id = v_i.iid
order by v.value DESC
limit ? offset ?

The SQL above is also simplified. It returns the desired result.


If you need an SQL like

select * from (t1 inner join t2) x1 right join (t3 inner join t4) x2 ...

then you’re out of luck because there’s absolutely no way to do that in OQL due to broken subqueries.