Hidden fields

[peter-gribanov]

Some queries require complex conditions that can not be realized using your library.
Sometimes we need to add to the query a hidden field that will only be used for conditions.

Example from Doctrine docs:

$query = $em->createQuery('
    SELECT
        u,
        u.posts_count + u.likes_count AS HIDDEN score
    FROM
        CmsUser u
    ORDER BY
        score
');
$users = $query->getResult(); // array of User objects

Example implementation in my project as specification:

class PopularBroadcast extends BaseSpecification
{
    private $start;

    public function __construct(\DateTime $day, $dqlAlias = null)
    {
        // popular broadcast per month
        $this->start = $day->modify('-1 month')->setTime(0, 0, 0);

        parent::__construct($dqlAlias);
    }

    protected function getSpec()
    {
        return Spec::andX(
            Spec::innerJoin('broadcast_view_daily', 'bvd'),
            Spec::gte('day', $this->start, 'bvd'),
            Spec::groupBy('id'),
            Spec::having('views > 0')
        );
    }

    public function modify(QueryBuilder $qb, $dqlAlias)
    {
        $qb
            // add hidden field
            ->addSelect('SUM(bvd.viewed) AS HIDDEN views')
            // imposiable use Spec::orderBy() becose it add $dqlAlias for hidden field
            ->orderBy('views', 'DESC')
        ;

        parent::modify($qb, $dqlAlias);
    }
}

[peter-gribanov]

Example solution

Create ValueObject for hidden field and use it in conditions:

$field = new HiddenField('SUM(%s.viewed)', 'views', $dqlAlias); // $dqlAlias nullable

$spec = Spec::andX(
    Spec::addHiddenField($field),
    Spec::orderBy($field, 'DESC')
);

In OrderBy specification:

class OrderBy implements QueryModifier
{
    // ...

    public function modify(QueryBuilder $qb, $dqlAlias)
    {
        if ($this->dqlAlias !== null) {
            $dqlAlias = $this->dqlAlias;
        }

        if ($this->field instanceof HiddenField) {
            $field = $this->field->getAlias();
        } else {
            $field = sprintf('%s.%s', $dqlAlias, $this->field);
        }

        $qb->addOrderBy($field, $this->order);
    }
}

[peter-gribanov]

Now we ca use hidden fields for order result:

// DQL: SELECT e, e.posts_count + e.likes_count HIDDEN score FROM User e ORDER BY score
$spec = Spec::andX(
    Spec::addSelect(
        Spec::selectHiddenAs(
            Spec::add(Spec::field('posts_count'), Spec::field('likes_count')),
            'score'
        )
    ),
    Spec::orderBy(
        Spec::alias('score')
    )
);