I took some time to dig into the source code of ash_sqlite and ash_sql, which would have to change significantly.
Unfortunately this is definitely over my head.
There are many lateral joins in ash_sql, and replacing them by correlated sub queries seems really difficult.
For one, ecto doesn’t support correlated subqueries, so fragments would have to be used for them.
Plus, finding the correct join clause for each lateral join will probably be different for each usage of lateral join, requiring proper understanding of each usage.
It really seems like lateral joins are just the right fit for this kind of problem and sqlite not supporting them really makes it painfully difficult.
I also do recall hearing something about sqlite supporting lateral joins at some point?
There is a branch that implements lateral, but Richard is not convinced it is needed.
Maybe you could be the one to convince him?
If you thinking having LATERAL is important, then try to convince me otherwise. Your best approach would be do demonstrate a real-world problem that LATERAL solves that would be difficult to resolve without LATERAL.
– Richard Hipp
I mean, ash definitely solves real-world problems, so you might actually be able to sway him?
