QueryBuilder

[acobster]

Acceptance Criteria

As Conifer user, I want to be able to:

1. create compound database clauses by passing their components to methods (e.g. $builder->and_clause('1=1', '2=2')
2. build up a database query by composing these clauses

Example usage:

$searchTerms = ['dogs', 'cats'];
$builder = new Conifer\Query\QueryBuilder($wpdb);

$builder->or_clause(array_merge(
  $builder->map_wildcards('post_title LIKE %s', $searchTerms),
  $builder->map_wildcards('post_excerpt LIKE %s', $searchTerms),
  $builder->map_wildcards('post_content LIKE %s', $searchTerms),
  // meta
  $builder->and_clause(
    $builder->or_clause(
      $builder->map_wildcards('meta_key LIKE %s', ['custom_field_%', 'extra_custom_field_%'])
    ),
    $builder->or_clause(
      $builder->map_wildcards('meta_value LIKE %s', $searchTerms)
    )
  )
));

echo $builder->sql();

The above will output something like:

(post_title LIKE '%dogs%' OR post_title LIKE '%cats%')
OR (post_excerpt LIKE '%dogs%' OR post_execrpt LIKE '%cats%')
OR (post_content LIKE '%dogs%' OR post_content LIKE '%cats%')
OR (
  (meta_key LIKE 'custom_field_%' OR meta_key LIKE 'extra_custom_field_%')
  AND (meta_value LIKE '%cats%' OR meta_value LIKE '%dogs%')
)

QueryBuilder aims to...

1. automate the mundane task of composing many SQL clauses together using standard operators (AND, OR, =, LIKE, etc.). In other words, it abstracts away tedious/error-prone string concatenation so you can focus on your query logic and the nesting of the clauses within them
2. generate SQL clauses as strings in a predictable way

QueryBuilder does not...

1. function as a fully-fledged ORM
2. possess knowledge of database structure
3. check SQL syntax
4. execute queries

[acobster]

This is going to be an internal API. Too much complexity to expose and document.