Re:Ask HN: Are ORMs overkill for using SQL databases?
by SQLite on Friday August 04, 2017 @06:02PM
(#14932719)
I'd never heard of "CROSS JOIN LATERAL" before. So I went and read the PostgreSQL documentation. If I understand correctly what LATERAL does, it is not needed by SQLite. The SQLite query planner will figure out on its own that you are doing a LATERAL join and do the right thing.
[ Parent
| on HN ]
Re:Ask HN: Are ORMs overkill for using SQL databases?
by ufmace on Saturday August 05, 2017 @12:34AM
(#14934248)
Well that's interesting. I checked around a bit and tried a few things. Seems that SQLite doesn't support LATERAL joins, but you can get the same effect by putting the subquery in the JOIN condition[0]. I'm not sure if they're exactly the same and you can do all of the same things, but it worked for the quick use-case I came up with:
select a.username, p2.*
from accounts a
join posts p2 on p2.id in (select p1.id from posts p1 where p1.account_id = a.id order by p1.score desc limit 2);