.. _default-builder:

=====================
Default query builder
=====================

The recommended way to add parts to the query generated by one of ``TableGateway``'s methods is using fluent builders.
Either an instance of a builder can be passed as ``$fragments`` parameter to that method
or a closure accepting such an instance.

Builders are created by implementations of ``TableGatewayFactory``, these are used by ``TableLocator::createBuilder()``
and ``gateways\GenericTableGateway::createBuilder()``.  

As is the case with gateways, if ``TableLocator`` is not configured with gateway factories or if neither of those
returns a specific object from its ``createBuilder()`` method, then a default implementation is returned. That will be
an instance of ``builders\FluentBuilder`` class described in this chapter.

.. _default-builder-api:

``builders\FluentBuilder`` API
==============================

.. code-block:: php

    namespace sad_spirit\pg_gateway\builders;

    use sad_spirit\pg_gateway\{
        Condition,
        SelectProxy,
        TableGateway,
        metadata\TableName
    };
    use sad_spirit\pg_gateway\conditions\{
        NotCondition,
        ParametrizedCondition,
        column\BoolCondition,
        column\IsNullCondition
    };
    use sad_spirit\pg_builder\nodes\{
        OrderByElement,
        QualifiedName
    };

    class FluentBuilder extends FragmentListBuilder
    {
        // Non-fluent methods for creating Conditions
        public function createAny(string $column, array $values) : ParametrizedCondition;
        public function createBoolColumn(string $column) : BoolCondition;
        public function createNotBoolColumn(string $column) : NotCondition;
        public function createIsNull(string $column) : IsNullCondition;
        public function createIsNotNull(string $column) : NotCondition;
        public function createNotAll(string $column, array $values) : ParametrizedCondition;
        public function createOperatorCondition(string $column, string $operator, mixed $value) : ParametrizedCondition;
        public function createEqual(string $column, mixed $value) : ParametrizedCondition;
        public function createSqlCondition(string $sql, array<string, mixed> $parameters = []) : ParametrizedCondition;
        public function createExists(string|TableName|QualifiedName|TableGateway|SelectBuilder $select) : ExistsBuilder;
        public function createPrimaryKey(mixed $value) : ParametrizedCondition;

        // Immediately adding Conditions to the list
        public function any(string $column, array $values) : $this;
        public function boolColumn(string $column) : $this;
        public function notBoolColumn(string $column) : $this;
        public function isNull(string $column) : $this;
        public function isNotNull(string $column) : $this;
        public function notAll(string $column, array $values) : $this;
        public function operatorCondition(string $column, string $operator, mixed $value) : $this;
        public function equal(string $column, mixed $value) : $this;
        public function sqlCondition(string $sql, array<string, mixed> $parameters = []) : $this;
        public function exists(
            string|TableName|QualifiedName|TableGateway|SelectBuilder $select
        ) : proxies\ExistsBuilderProxy;
        public function primaryKey(mixed $value) : $this;

        // Adding fragments that modify the output expressions list
        public function returningColumns() : proxies\ColumnsBuilderProxy;
        public function returningExpression(
            string|Condition $expression,
            ?string $alias = null,
            array<string, mixed> $parameters = []
        ) : $this;
        public function returningSubquery(SelectBuilder $select) : proxies\ScalarSubqueryBuilderProxy;

        // Adding a join
        public function join(
            string|TableName|QualifiedName|TableGateway|SelectBuilder $joined
        ) : proxies\JoinBuilderProxy;

        // Adding CTEs to the query's WITH clause
        public function withSqlString(
            string $sql,
            array<string, mixed> $parameters = [],
            int $priority = Fragment::PRIORITY_DEFAULT
        ) : $this;
        public function withSelect(SelectProxy $select, string $alias) : proxies\WithClauseBuilderProxy;

        // Adding fragments to SELECT statements
        public function orderBy(iterable<OrderByElement|string>|string $orderBy) : $this;
        public function orderByUnsafe(iterable<OrderByElement|string>|string $orderBy) : $this;
        public function limit(int $limit) : $this;
        public function offset(int $offset) : $this;
    }

Methods returning proxies
=========================

Several of the ``FluentBuilder``'s methods return objects from ``builders\proxies`` namespace.
The classes in that namespace extend :ref:`builder classes <fragment-builders>` and proxy the methods
of ``FluentBuilder`` instance returning them.
Thus it is possible to configure the specific builder and then continue with methods of ``FluentBuilder``:

.. code-block:: php

    $builder
        ->exists(new TableName('example', 'stuff'))
            ->not()
            ->joinOn('self.klmn @@@ joined.klmn')
        ->orderBy('something');

Creating vs. adding ``Condition`` instances
===========================================

As seen above, there are two groups of methods dealing with :ref:`Condition classes <conditions>`: methods in
the first group return an instance of ``Condition`` and those in the second group just add that ``Condition``
to the list (using the methods from the first group under the hood).

The base ``Condition`` class implements ``FragmentBuilder`` interface, its ``getFragment()`` method returns
a ``fragments\WhereClauseFragment``. Thus adding a ``Condition`` directly to the list will add it
to the ``WHERE`` clause of the query using ``AND``.

Therefore, two main reasons to use ``create*()`` methods are

- Created ``Condition`` should be used in the ``HAVING`` clause or as the ``JOIN`` condition;
- Several ``Conditions`` should be combined via ``AND`` and ``OR``:

.. code-block:: php

    use sad_spirit\pg_gateway\Condition;

    // This will generate "self.processed is null or self.employee_id = :employee_id" for the WHERE clause 
    $gateway->select(
        $builder->add(Condition::or(
            $builder->createIsNull('processed'),
            $builder->createEqual('employee_id', $currentEmployee)
        ))
    );

Created ``Condition``\ s
========================

The ``create*()`` methods eventually generate the following SQL
 
``createAny()``
    Generates ``self.column = any(:column::column_type[])``. 
    Returned ``conditions\ParametrizedCondition`` decorates ``conditions\column\AnyCondition`` here,
    allowing to pass ``$values`` together with condition rather than separately
    in ``$parameters`` argument to a query method.
``createBoolColumn()``
    Generates ``self.column`` using a column of ``bool`` type.
``createNotBoolColumn()``
    Generates ``NOT self.column``, returned ``conditions\NotCondition`` decorates ``conditions\column\BoolCondition``.
``createIsNull()``
    Generates ``self.column IS NULL``.
``createIsNotNull()``
    Generates ``self.column IS NOT NULL``, returned ``conditions\NotCondition`` decorates
    ``conditions\column\IsNullCondition``.
``createNotAll()``
    Generates ``self.column <> all(:column::column_type[])``, returned ``conditions\ParametrizedCondition``
    decorates ``conditions\column\NotAllCondition``.
``createOperatorCondition()``
    Generates ``self.column <OPERATOR> :column::column_type``, returned ``conditions\ParametrizedCondition``
    decorates ``conditions\column\OperatorCondition``.
``createEqual()``
    generates a ``self.column = :column::column_type``, returned ``conditions\ParametrizedCondition``
    decorates ``conditions\column\OperatorCondition``.
``createSqlCondition()``
    Embeds manually written SQL as a condition, returned ``conditions\ParametrizedCondition``
    decorates ``conditions\SqlStringCondition``.
``createExists()``
    Returns :ref:`a builder <fragment-builders-exists>` for configuring a ``[NOT] EXISTS(...)`` condition.
    If the argument is a string it is treated as a ``SELECT`` query and eventually passed to ``Parser``,
    if it is an instance of ``TableName`` / ``QualifiedName`` then a gateway is located for that table name and
    ``select()``\ ed from. If the argument is already a ``TableGateway`` instance
    then an unconditional ``select()`` is done.
``createPrimaryKey()`` (actually defined in ``PrimaryKeyBuilder`` trait)
    Similar to ``createEqual()`` but handles composite primary keys as well.
    The returned ``ParametrizedCondition`` decorates ``conditions\PrimaryKeyCondition``.

Note that all the methods that accept column values do not embed them into SQL, passing them on instead 
via ``ParametrizedCondition`` decorator. This way the generated SQL does not depend on specific parameter values
and may be reused with other values.

While ``sqlCondition()`` / ``createSqlCondition()`` methods accept an SQL string, it won't of course be inserted
verbatim into the generated SQL, e.g.

.. code-block:: php

    $condition = $builder->createSqlCondition(
        'case when self.foo @@ :foo::foo_type then self.bar else false end',
        ['foo' => $fooValue]
    )

will have the special ``self`` alias replaced as needed, named ``:foo`` placeholder will be converted
to positional one, and its type info ``foo_type`` extracted and used to properly convert the given ``$fooValue``.

Modifying the returned values
=============================

``returningColumns()``
    Configures a list of columns returned by
    a ``SELECT`` statement or by the ``RETURNING`` clause of data-modifying statement
    using :ref:`a ColumnsBuilder <fragment-builders-columns>`.
``returningExpression()``
    Adds expression(s) to the list of columns returned by a ``SELECT`` statement
    or to the ``RETURNING`` clause of ``DELETE`` / ``INSERT`` / ``UPDATE``.
``returningSubquery()``
    Adds a scalar subquery to the output list of a ``SELECT`` statement
    or (less probably) to the ``RETURNING`` clause of data-modifying statement,
    :ref:`configured with ScalarSubqueryBuilder <fragment-builders-scalar>`.

Adding joins
============

``join()`` adds a join to the given table / query using
:ref:`a Builder for configuring the join <fragment-builders-join>`.
The first argument has the same semantics as for ``exists()`` / ``createExists()`` method described above:

.. code-block:: php

    use sad_spirit\pg_gateway\metadata\TableName;

    $builder->join(new TableName('example', 'users'))
        ->onForeignKey(['editor_id'])
        ->left()
        ->alias('editors');

    $builder->join('select foo from bar')
        ->on($builder->createSqlCondition('bar.baz <> self.baz'));

Adding Common Table Expressions to the ``WITH`` clause
======================================================

``withSqlString()``
    The first argument can be either a complete ``WITH`` clause, possibly with several CTEs, or a
    single CTE: ``foo AS (...)``. The second can contain parameters used in the SQL. The third is priority since
    order of CTEs is important in non-``RECURSIVE`` ``WITH`` clauses.
``withSelect()``
    Accepts a result of ``TableGateway::select()`` and a mandatory alias, returns
    :ref:`a builder for WITH clause <fragment-builders-with>`:

    .. code-block:: php

        use sad_spirit\pg_gateway\Fragment;

        // this will generate 'WITH RECURSIVE foo (bar, baz) AS (...result of $otherGateway...)'
        $builder->withSelect($otherGateway->select(/* some conditions */), 'foo')
            ->priority(Fragment::PRIORITY_HIGHEST)
            ->columnAliases(['bar', 'baz'])
            ->recursive();

Fragments for ``SELECT`` statements
===================================

``orderBy()`` / ``orderByUnsafe()``
    These add fragments that set the ``ORDER BY`` list of a ``SELECT`` query
    to the given expressions, the difference being that the former allows only column names and ordinal numbers
    as expressions while the latter allows everything.

    The reasoning is that sort options are often coming from
    user input and due to SQL language structure should be embedded in the query without the means to use
    some parameter-like constructs. "Unsafe" in the method name is a huge hint not to pass user input.
``limit()``
    Adds a fragment applying the ``LIMIT`` clause. Note that the given ``$limit`` value will not actually
    be embedded in SQL but passed as a parameter value (for the ``:limit`` placeholder).
``offset()``
    Adds a fragment applying the ``OFFSET`` clause. ``$offset`` parameter is also not embedded in SQL (``:offset``
    placeholder is used).