ORMs I've worked with usually expect you to write the complex queries/joins yourself using a series of functions e.g. orm.select(table1).join(table2).on(keys).where(cond).group(by).having(cond).order(by). It's just SQL with extra steps.

An ORM that understands db schema, indices, and automatically decides where to use outer join or subqueries based on what would be most efficient given the specific schema would be fantastic. Basically an ORM that 95% of the cases comes up with the most efficient way to look up the data.