As part of my review of data access abstractions in MediaWiki core, I'm introducing a query builder module. The idea is to encapsulate the parameters to IDatabase::select(), as seen in the getQueryInfo() pattern, into an object.
The class has chainable mutator methods, similar to those in Doctrine's QueryBuilder. Unlike in Doctrine, to help with migration from existing code, it is necessary to allow callers to directly set the underlying arrays. Certain implementation details are exposed.
The query builder object does not require an IDatabase for its construction, only for its execution. This gives it a similar state to the getQueryInfo() arrays, which eases migration. The disadvantage of this approach is that it is difficult to add an expression builder. Ideally, the arrays passed to IDatabase::select() would be DBMS-independent, including not having any quoted strings in them. For example, instead of IDatabase::buildLike(), which returns a DBMS-dependent string, you would have a LikeExpression value object which would be resolved during the execution of IDatabase::select(). But I think that's a project for another day. For now, the syntax will be $queryBuilder->where( 'user_name' . $db->buildLike(...) ).
The query builder's join methods, which are aimed at new code, require a modern style in which all joins have aliases and join conditions. Doctrine's join interface is very similar. Instead of
$tables = 'user'; $join_conds['user'] = [ 'JOIN', 'rev_user=user_id' ];
We would have
$builder->join( 'user', 'user1', 'rev_user=user_id' );
I'm adding structured support for parenthesized join expressions and joining on a subquery.
Doctrine uses the same QueryBuilder class for select, update and delete queries, which I don't think is advisable since the allowed methods are different. I'm adding a newable SelectQueryBuilder; we could later add UpdateQueryBuilder and DeleteQueryBuilder.
Instead of a single execute() function, I'm planning to have wrappers for select(), selectField(), selectFieldValues(), selectRow() and selectSQLText(), potentially called fetchResultSet(), fetchField(), fetchFieldValues(), fetchRow() and getSQL() respectively.