These are not the same concept: a relational database makes sense when your application relies on relations between records. If you need to do lots of joins across many records, Riak is going to perform horribly because it's designed for a different problem.
CAP says nothing whatsoever about whether you want a relational or non-relational database, merely what tradeoffs you'll have to make to satisfy your business needs.
Using an ORM doesn't factor into this discussion at all other than for providing a convenient place to implement whatever system you devise to meet those needs.
MySQL way: SELECT * FROM a JOIN b ON x WHERE y
NoSQL way: 1) SELECT * FROM a WHERE x 2) Perform join in app layer or stored procedure.
Like it or not, when you scale you will lose one of the CAP, and NoSQL databases do the hard task of delivering an eventually consistent data store to you and letting you express yourself in the RIGHT context, which is not SQL.
> You can still do joins, etc. but it's in the context of things like map-reduce, and it makes sure that you can scale despite the joins.
Either of your examples are commonly implemented in SQL databases, too: this is a routine MySQL optimization to avoid subselects and, amusingly, one which an ORM makes significantly easier to implement:
SELECT * FROM a WHERE x; SELECT * FROM b WHERE pk IN (…list of IDs from first query…);
Again, the SQL vs. NoSQL question is about your data model and access patterns, not whether you use an ORM or magical thinking about CAP. The line between the two has become quite blurry since there are things like MySQL-backed key-value stores or Postgres extensions which allow it to handle document-store workloads without losing performance or giving up the ability to do flexible queries. This isn't a question of religion: it's just looking at your business, assessing how well you know the access patterns (SQL systems are generally more flexible) and performance requirements and picking the best solution. Anyone claiming to have a right answer for everyone is wrong.
> Like it or not, when you scale you will lose one of the CAP, and NoSQL databases do the hard task of delivering an eventually consistent data store to you and letting you express yourself in the RIGHT context, which is not SQL.
You've now gone from wrong to very dangerously wrong: there is no scale which is immune to CAP and NoSQL has no magic for avoiding this. Eventual consistency is only appropriate for some problems and, as above, can be implemented on either system. No matter what storage system you choose you're still going to have to make careful decisions about business priorities and test carefully.
Having said that, I have discovered a heuristic over the years: that if you are using an ORM, you probably don't want a relational database. You should learn something like Riak and let it handle the distribution and provide all the partitioning and availability for you. The CAP theorem shows that you can't get it all, and most likely you want to use one of those data stores instead of a relational one.
For regular sites that won't have millions of users constantly using it, though, a relational db is fine.