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 LIMIT 21 OFFSET 60
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.
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 LIMIT 21 OFFSET 60
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.