Query Builder help with JOINs

[Jezza]

Can anyone tell me if it is possible to nest queries in a SQL JOIN in the Query Builder?

I want to achieve this in SQL:

select o.id_order, r.code 
from ps_orders o 
left outer join ( 
select ocr.id_order, cr.code 
from ps_order_cart_rule as ocr 
inner join ps_cart_rule as cr 
on ocr.id_cart_rule = cr.id_cart_rule 
) r 
on o.id_order = r.id_order

So the LEFT OUTER JOIN needs to be on the result of the nested query (not on a table that exists already which IS supported by the Query Builder).

Is it possible?

Thanks!

I have posted already on the #general Slack channel but no response.