The introduction of the concept of operand

[peter-gribanov]

All a comparison operations in the filters consist of the left operand, operator and the right operand.

<left_operand> <operator> <right_operand>

Now the left operand can be only a entity field, and the right operand only a value. This is a simple and effective mechanic to solve the standard tasks. However, it has several restrictions:

• Impossible to use functions CountOf or having usage? #126, Improve usage of dqlAlias when using having and / or custom function e.g. from Doctrine Extensions #132, Implemented size, addSelect and memberOf DQL functions #163, Size, addSelect and memberOf DQL functions #164;
• Impossible to use aliases of fields OrderBy (and others) not working with aliased fields #127, Hidden fields #151;
• Impossible to customize value type Usage custom types in Condition value #133, Spec comparison methods do not support specifying custom parameter type #161;
• Impossible to compare two fields;
• Impossible to compare two values;
• Impossible to use arithmetic.

I offer to introduce the concept of operand. All specifications take operands as arguments. Transformation operand in DQL delegated to operand. This is simplify the specifications and will open a lot of opportunities.

By default, the first argument is packed into the operand of the field, and the second into the operand of the value. This will partially maintain backward compatibility and make the usage of specifications more compact.

if (!($field instanceof Operand)) {
    $field = new Field($field);
}
if (!($value instanceof Operand)) {
    $value = new Value($value);
}

Some operands can be put into other operands. For example operand functions can be put into another function.

trim(lower(e.email)) = :email

Operands arithmetic operations also can be put into each other.

((e.price_old - e.price_current) / (e.price_current / 100)) > :discount

I don't think that we need to create the operand classes for each the database functions on each platforms. I think we may just check that the specified user function is supported by the current platform. See getCustomStringFunction, getCustomNumericFunction and getCustomDatetimeFunction methods in Doctrine Configuration class. As default, we can add a DQL Functions.

Usage

// DQL: e.day > :day
Spec::gt('day', $day);
// or
Spec::gt(Spec::field('day'), $day);
// or
Spec::gt(Spec::field('day', $dqlAlias), $day);

// DQL: e.day > :day
Spec::gt('day', $day);
// or
Spec::gt('day', Spec::value($day));
// or
Spec::gt('day', Spec::value($day, Type::DATE));

Use functions:

// DQL: size(e.products) > 2
Spec::gt(Spec::size('products'), 2);
// or
Spec::gt(Spec::fun('size', 'products'), 2);
// or
Spec::gt(Spec::fun('size', Spec::field('products')), 2);

Nested functions:

// DQL: trim(lower(e.email)) = :email
Spec::eq(Spec::trim(Spec::lower('email')), trim(strtolower($email)));
// or
Spec::eq(
    Spec::fun('trim', Spec::fun('lower', Spec::field('email'))),
    trim(strtolower($email))
);

Use field as left and right operand for find products with a discount:

// DQL: e.price_current < e.price_old
Spec::lt(Spec::field('price_current'), Spec::field('price_old'));

Very complex specification for find products with a discount of greater than N percent:

// DQL: IF(
//     e.price_old IS NOT NULL AND e.price_current < e.price_old,
//     ((e.price_old - e.price_current) / (e.price_current / 100)),
//     0
// ) > :discount
Spec::gt(
    Spec::ifElse(
        Spec::andX(
            Spec::isNotNull(Spec::field('price_old')),
            Spec::lt(Spec::field('price_current'), Spec::field('price_old'))
        ),
        Spec::div(
            Spec::sub(Spec::field('price_old'), Spec::field('price_current')),
            Spec::div(Spec::field('price_current'), Spec::value(100))
        ),
        Spec::value(0)
    ),
    Spec::value($discount)
)

Select single field:

// DQL: SELECT e.email FROM ...
Spec::select('email')
// or
Spec::select(Spec::field('email'))

Add single field in the selected set:

// DQL: SELECT e, u.email FROM ...
Spec::addSelect(Spec::field('email', $dqlAlias))

Add one more custom fields in the selected set:

// DQL: SELECT e.title, e.cover, u.name, u.avatar FROM ...
Spec::andX(
    Spec::select('title', 'cover'),
    Spec::addSelect(Spec::field('name', $dqlAlias), Spec::field('avatar', $dqlAlias))
)

Add single entry in the selected set:

// DQL: SELECT e, u FROM ...
Spec::addSelect(Spec::selectEntity($dqlAlias))

Use aliases for selection fields:

// DQL: SELECT e.name author FROM ...
Spec::select(Spec::selectAs(Spec::field('name'), 'author'))

Add single hidden field in the selected set:

// DQL: SELECT e, u.name HIDDEN author FROM ...
Spec::addSelect(Spec::selectHiddenAs(Spec::field('email', $dqlAlias), 'author')))

Use expression in selection for add product discount to the result:

// DQL: SELECT (e.price_old is not null and e.price_current < e.price_old) discount FROM ...
Spec::select(Spec::selectAs(
    Spec::andX(
        Spec::isNotNull('price_old'),
        Spec::lt(Spec::field('price_current'), Spec::field('price_old'))
    ),
    'discount'
))

Use aliases in conditions to search a cheap products:

// DQL: SELECT e.price_current price FROM ... WHERE price < :low_cost_limit
Spec::andX(
    Spec::select(Spec::selectAs('price_current', 'price')),
    Spec::lt(Spec::alias('price'), $low_cost_limit)
)

Use hidden field for order users by score:

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

[Nyholm]

Wow, Yes!
This will bring lots more flexibility. Im all 👍 if you think the implementation is manageable.

[yaffol]

When I began work on #164, I looked into abstracting the operand so that field or value could be either left or right hand side of the comparison.

This goes so much further and is a really powerful abstraction that opens up an order of magnitude more possibilities with the library.

It also addresses some use cases I've already come against already such as using hidden fields. Indeed today it would be very helpful for me to be able to select a single field, in the form of

// DQL: SELECT e.email FROM ...
Spec::select('email')

I'm really in favour of this!