I checked the code for Query Grouping, it pushes parentheses/AND/OR/NOT directly into the where clause.
I understand that Query Builder isn't capable of handling complex cases, but I really think that it's over-sold in its capabilities and it really isn't clear what is gained through the protect/escape_identifiers() functionality. I use Query Builder because I like the syntax, and because building queries by concatenating strings is clumsy and a maintenance headache.
To execute the above query in CI2's Active Record required ~120 lines of code to replicate the functionality of CI3's Query Builder's get_compiled_select(). To execute it in CI3's Query Builder is impossible unless you disable protect_identifiers, so I ended up adding about 30 lines of code to my model to replicate functionality in Query Builder to build out portions of this query. Overall, an improvement in terms of reducing the number of lines used to perform functionality which belongs in the database layer, but I really hope I never have to revisit this query.
I've gotten past the point of believing that I'm ever going to be able to change databases easily just because I use Query Builder, since it does nothing to abstract the database in any significant way beyond the database library itself. The limited selection of functions may keep you from venturing into db-specific territory in some ways, but you rarely need much else. The area which limits the ability to transition between databases the most is probably DBForge, but I'm not sure there is much demand in the CI community to actually build a database layer which might someday provide real cross-platform capabilities.
What really seems significant here is that the biggest changes with an impact on the functionality of Query Builder seem to have taken place not within Query Builder (despite the lack of back-ports for most of the bug fixes in it), but within the escape_identifiers() method (previously _escape_identifiers()).
