[wp-trac] [WordPress Trac] #54642: Add prepared query builder support for `$wpdb` to build prepared queries across multiple location.

[WordPress Trac]

#54642: Add prepared query builder support for `$wpdb` to build prepared queries
across multiple location.
------------------------------+-----------------------------
 Reporter:  vedjain           |      Owner:  (none)
     Type:  enhancement       |     Status:  new
 Priority:  normal            |  Milestone:  Awaiting Review
Component:  Database          |    Version:
 Severity:  normal            |   Keywords:
  Focuses:  coding-standards  |
------------------------------+-----------------------------
 This requests scopes adding a query builder like API to WordPress, so that
 queries can be built across multiple functions, without needing to disable
 any PHPCS sniffs.

 Currently, if the query is not completely being built nearby, developers
 will eventually have to disable phpcs sniff, since they would need to
 combine or interpolate queries. Some examples of this are present in WP
 core as well, such as in the [https://github.com/WordPress/wordpress-
 develop/blob/trunk/src/wp-includes/class-wp-meta-query.php#L668 meta
 query], [https://github.com/WordPress/wordpress-develop/blob/trunk/src/wp-
 includes/taxonomy.php#L1585 taxonomy] etc. Although WP does a great job
 generally to avoid disabling phpcs, in the general plugin ecosystem, these
 examples are more prevalent.

 Note that most of the examples of disabling phpcs can be refactored to not
 needing to disable. However, as far as I can tell, building a query this
 way (across multiple functions/places) is not considered a bad practice,
 if this assumption is correct then we should support doing it better.

 This eventual disabling of PHPCS is risky and removes a major and
 effective protective layer.

 == Solution

 The solution for WordPress would be to provide an API where developers can
 build a prepared query in steps, and join them before executing in a safe
 manner.

 I looked at query builders for various ORMs such as Doctrine (Symphony),
 ActiveRecord (Rails), Laravel etc. While such extensive APIs would likely
 be overkill for WordPress, one possible solution is this hopefully simpler
 API that I am proposing in [https://github.com/WordPress/wordpress-
 develop/pull/2062 this PR 2062].

 This PR adds a new class called `WP_DB_Partial_Query` which implements
 `__toString()` magic method so that it can be interpolated in sprintf
 calls. The objects of this class will represent a partial query and a new
 method `$wpdb->prepare_partial()` which will create and return these
 objects.

 This `$wpdb->prepare_partial()` method internally calls `$wpdb->prepare()`
 and takes exactly the same arguments. This PR also introduces a new
 placeholder `%q` (although this isn't necessary and `%s` can be used as
 well) which can be used to pass these prepared partial queries. I have
 added some examples to use the API in [https://github.com/vedanshujain
 /wordpress-develop/pull/1 this PR on my fork].

 Note that this is only a PoC and implementation is not complete.
 Specifically, along with tests, a major component missing is to match
 against `%q` placeholders and quote the args if they are not objects of
 the `WP_DB_Partial_Query` class.

 I can polish the PoC more to get in an acceptable state if this is a
 direction that WordPress is interested to explore at all and there are no
 obvious problems with the general approach that I have missed.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/54642>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform