query-builder-v2.md

#bQuery Builder v2

This document proposes a new PHPCR-ODM query builder API and attempts to explain why it is necessary and what was wrong with the previous query builder.

The Current Query Builder

The current PHPCR-ODM query builder is based upon the Doctrine ORM query builder API and uses and extends the Doctrine Commons expression builder.

$qb->from('Doctrine\Phpcr\Document\Generic');
$qb->where($qb->expr()->andx(
    $qb->expr()->eq('foo', 'bar'),
    $qb->expr()->eq('bar', 'foo'),
));

While this API is good for people coming from the Doctrine ORM, it was created to answer the needs of the Doctrine ORM and as such is not ideally suited for building PHPCR queries.

Joins.
Parameter binding, Value objects.
Ordering by dynamic operands
No support for select

The Problem with Joins

Joining is not implemented in the current query builder, but using the provided API we posit the following:

$joinCondition = new PHPCR\EquiJoinCondition('foo', 'a', 'bar', 'b');
$qb->from('Doctrine\Phcr\Document\Generic');
$qb->leftJoin('Doctrine\Foobar\Document\Join', 'b', $joinCondition);

So already we have some problems:

The from source has no selector.
We manually instantiate the PHPCR join condition.

We can imagine fixing the first problem, the second would require us to create a factory class.

$qb->leftJoin('Doctrine\Foobar\Document\Join', 'b', $qb->joinCond()->equiJoin('foo', 'a', 'bar', 'b'));

The Problem with Dynamic Operands and Binding Parameters

Binding parameters is not supported in the Query Builder currently - there is also a note indicating that parameters may not be supported by Jackalope.

The PHPCR QOM represents operands as OperandInterface classes. what we might call values are StaticOperandInterface classes, whilst the field we compare the value against are DynamicOperandInterface classes.

Currently the query builder will always convert the value part of an expr() into a LiteralInterface class. For parameter binding to work these values need to be classes of type BindVariableValue.

With the current API we can imagine something like the following:

$qb->where($qb->expr()->eq('field', ':value');
$q = $qb->getQuery();
$q->setParameter('value', 'bar');
$q->execute();

Note in PHPCR the "field" is a DynamicOperandInterface and value is a StaticOperandInterface.

But this poses the following problems:

What if we literally want to compare the string value ":foobar" ?
The equality expression above uses the PropertyValueInterface DynamicOperandInterface to compare the value of field against ":value", however PHPCR offers several types of DynamicOperandInterface:
- Length
- NodeLocalName
- LowerCase
- FullTextSearchScore

We might be able to solve (1) introducing a factory

$qb->where($qb->expr()->eq('field', $qb->valOp()->literal('value'));
$qb->where($qb->expr()->eq('field', $qb->valOp()->parameter('value'));

The current builder implements some custom expressions which partially solve the problem presented in (2):

$qb->where($qb->expr()->likeNodeName('barfoo'));

However this is incomplete, for a complete API we would need:

$qb->where($qb->expr()->likeNodeName('barfoo'));
$qb->where($qb->expr()->eqNodeName('barfoo'));
$qb->where($qb->expr()->gtNodeName('barfoo'));
$qb->where($qb->expr()->gteNodeName('barfoo'));
// and so on ..

and what if we want to compare a lowercase property value of a field? or the length of a properties value?

$qb->where$qb->expr()->eqLength('field', 10);
$qb->where$qb->expr()->gtLength('field', 10);
$qb->where$qb->expr()->gteLength('field', 10);
// and so on ...

Oops and we have forgotten about the selectors, lets add them too:

$qb->where$qb->expr()->eqLength('field', 10, 'a');
// ...

We could more efficiently solve this problem by using factories again:

$qb->where($qb->expr()->eq(
    $qb->dynOp()->propValue('field', 'a'), 
    $qb->valOp()->parameter('value', 'b')
));

And wait, what about ordering? PHPCR orderings operate with DynamicOperandInterface classes. The current implementation will always use a PropertyValueInterface, so we lose functionality. We can reuse the dynOp factory with ordering:

$qb->orderBy($qb->dynOp()->fullTextSearchScore(), 'asc');

Lots of factories huh?

It would seem that we can efficiently solve the problems of the current query builder by introducing some new factories. But by now the Query Builder is not looking much like the old Doctrine ORM query builder:

$qb->from('Doctrine\Phpcr\Documentz\Generic', 'a');
$qb->leftJoin('Doctrine\Foobar\Document\Foobar', 'b', 
    $qb->joinCond()->equiJoin('field1', 'b', 'field2', 'a')
);
$qb->where($qb->expr()->eq(
    $qb->dynOp()->propValue('field', 'a'),
    $qb->valOp()->literal('bar', 'b')
));

The QOM Factory and the Case for a Source Factory

At this point it might be interesting to look at the original PHPCR method of creating a query:

$q = $qom->createQuery(
    // SourceInterface (from)
    $qom->join(
        $qom->selector('nt:unstructured', 'a'),
        $qom->selector('nt:unstructured', 'b'),
        $qom->equiJoinCondition(
            'a', 'foobar',
            'b', 'barfoo'
        )
    ),
    // ConstraintInterface (where)
    $qom->comparison(
        $qom->nodeLocalName('a'),
        $qom->bindVariable('test_var'),
        QueryObjectModelInterface::JCR_OPERATOR_EQUAL_TO
    )
);
$q->bindValue('test_var', 'moobar');
$q->execute();

So it seems we are half-way along to implementing this type of API already. We have adopted factories in all cases but the from() method. Would it not make sense to adopt a factory here too?

$qb->from($qb->source()->document('Document/Foobar'));
// or
$qb->from($qb->source()->join(
    $qb->source()->document('Document/Foobar', 'a',),
    $qb->source()->document('Document/Foobar2', 'b',),
    $qb->joinCond()->equiJoin('field1', 'a', 'field2', 'b')
));

Unlike the other cases where we have introduced factories, this one is not actually required, we can construct the correct PHPCR object graph just by using from() and leftJoin etc. But this method is just more consistent with how PHPCR actually seems to work.

Why even bother? A New Query Builder API

If you are convinced so far with the using factories you may be equally unconvinced with the look of the API, its way to verbose...

The API below merges the Query Builder and Expression Builder components into one:

$qb->select()
    ->column('foobar', null, 'a')
    ->column('barfoo', null, 'a');
$qb->from()
    ->join()
        ->left()->document('Doctrine\Fqn\Foobar', 'a')
        ->right()->document('Doctrine\Fqn\Foobar', 'b')
        ->condition()->equi('a', 'foobar', 'b', 'barfoo');
$qb->where()
    ->and()
      ->descendantNode('/foobar')
      ->equals()
          ->nodeLocalName('a')
          ->bindVariable('test_bar');
$qb->orderBy()
    ->ascending()->propertyValue('foobar', 'a')
    ->descending()->nodeLocalName('a');
$q = $qb->getQuery();
$q->setParameter('test_bar');
$q->execute();

Structure

The query builder will ommit "end()" requirement of similar fluent factory builders and determine and validate the context node at runtime

> builder (1..* Fundamental)
    > select (0..* Column)
       > column (x)
    > from (1..1 Source)
       > document (x)
       > join (2..2 Source)
    > where (1..1 Constraint)
       > andx|orx (2..2 Constraint)
       > eq|neq|lt|lte|gt|gte|like (1..1 DynamicOperand 1..1 StaticOperand)
       > not (1..1 Constraint)
       > sameDocument (x)
       > descendant (x)
    > orderBy (0..* Ordering)
       > ascending (1..1 DynamicOperand)
       > descending (1..1 DynamicOperand)

dantleech/query-builder-v2.md