It really isn't terribly complicated, but the reasoning is a bit subtle.
Stop trying to generate SQL.
That's it. Really.
Now you're probably wondering why I'd say a thing like that.
The most basic, inevitable reason is this: SQL is at least a
second-order logic because you can do arithmetic on aggregates in it.
In order to write second-order logic, you need third-order logic,
which explains why attempts to generate SQL in general result in such
horrible and consistent failures in all but the most trivial cases.
Another reason, just as inevitable at the moment and almost as basic
is that the data definition language fails to capture meaning. A
foreign key relationship, for example, can mean a whole slew of
different things, not a single one of which is captured by the mere
existence of the foreign key data definition. When you operate with
radically incomplete information, your best guesses will only be
misleading if you're lucky. Most times, you're not lucky, and you
get to deal with the fact that the best guesses, while appearing
authoritative, are completely wrong.
So what's the answer? Go back to the definition. You're mapping
objects to relational constructs. What are those constructs?
Queries.
Not tables, not views, not rows. Those are
private methods.
Queries.
And yes, you'll be writing more SQL because of this. Fear not. While
SQL is an incredibly hard language for computers to write, it's made
specifically to be easy on humans. SQL has plenty of power and
expressiveness if you'll just remember that it really is a full-bore
programming language, and not just some kind of accessor for hash
tables.
One question that I have when tailoring ORM mappings using made SQL queries, what strategy do you use to persist changes to object data that was instantiated using a query?
1) plpgsql generates sql. say bye to it?
2) 'made to be easy for humans' begs hard questions 'what is easy?' and 'who is human, i.e., which humans?'. they didn't publish what tests they used to determine usability nor did they make them repeatable. why accept their conclusions? do we know it's not bad science?
3) if mappings into sql can be made, why not? why allow mappings into sql only from sql? the restriction seems arbitrary. were i to use abstract state machines and map to sql, why not?
4) why not generate schemas from type decls and functional and inclusion dependency expressions, and generate sql from abstract syntax representations of sql?
state of the art was enormously advanced by db modeling work and by the logic behind sql forms, which are often very close to logical forms. but sql is a historical artifact. i admire efforts like LINQ, for improving on sql by embedding it better.
1) Use an ORM that's probably overkill, probably restricts you in many ways,
and might produce suboptimal queries, or
2) End up writing your own little ORM that does just what you need, or
3) End up writing lots of very similar class and object methods that
use hand-generated SQL.
If your application is object-oriented and your database uses SQL, there's
a fundamental translation problem. I don't like any of the solutions above,
but I can't think of any better solutions. (I tend to dislike (3) the
least, because you can tailor your queries exactly to the problem domain
and you can change your database schema without too much pain.)