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
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.

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

• 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.

P.S.

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.

Hi,

Thanks for your question and answer, very interesting!

I think your provided answer unfortunately does not help me in my case. I’d like to create a column for certain rows in V (based on your example). So my query would look like this

select i.id,  v1.value AS Column1, v2.value AS Column2
 from Mdl.I i
 left join i/Mdl.V_I/Mdl.V v1 on
  v1/Mdl.V_A/Mdl.A/ID = $A_ID
 left join i/Mdl.V_I/Mdl.V v2 on
  v2/Mdl.V_A/Mdl.A/ID = $A2_ID

Is there a way to do this?

Does anyone know why these LEFT JOIN constraints act as INNER JOINS? It does not make sense to me.