Just to be clear, CDBI does not prevent you from writing custom SQL, it just provides an abstraction layer. You can write any SQL statement you want (including complex joins, etc.) into a CDBI class and reference it from your code.

We use CDBI as our abstraction layer, sometimes with complex SQL. The advantage here is the SQL is re-usable and all in one place. We use Oracle, but generally try to avoid 'special' Oracle features.