Zend\Db\Sql\Predicate\Between: Implementation of optional Type parameters

[GeeH]

This issue has been moved from the zendframework repository as part of the bug migration program as outlined here - http://framework.zend.com/blog/2016-04-11-issue-closures.html

---

Original Issue: https://api.github.com/repos/zendframework/zendframework/issues/7337
User: @Koopzington
Created On: 2015-03-18T09:42:39Z
Updated At: 2015-11-06T20:57:29Z
Body
Today i've used a SQL statement that checked if a value is between the values of 2 columns.
I've tried using Between like this:

$select->where->between(
    500, 
    'minAmount', 
    'maxAmount'
);

Since the format currently is fixed to "Identifier BETWEEN Value AND Value", I'd like to see an implementation of optional type params like they're being used in Zend\Db\Sql\Predicate\Predicate.
In the end i'd be able to build the WHERE condition like this:

$select->where->between(
    500, 
    'minAmount', 
    'maxAmount', 
    $select->where::TYPE_VALUE, 
    $select->where::TYPE_IDENTIFIER, 
    $select->where::TYPE_IDENTIFIER
);

---

[Estigy]

I came here to second this proposal.
Having only the first parameter being treated as an identifier prevents me from using the between() method to query all products where a given date lies between their start and end date:

SELECT * FROM products WHERE '2016-11-02' BETWEEN onlineStart AND onlineEnd;

How is this to be done? Do I really need to use where->greaterThanOrEqualTo() and where->lessThanOrEqualTo()?

[Koopzington]

After 2 hours of making a PR for this i've seen this in the Test.
So theoretically this worked all the time if you setup the select like this:

$select->where->between(
    ['2016-11-02' => Between::TYPE_VALUE],
    ['onlineStart' => Between::TYPE_IDENTIFIER],
    ['onlineEnd' => Between::TYPE_IDENTIFIER]
);

Now i'm wondering if i should still do the PR or not...

[Estigy]

This seems to cover everything we both do need - but we were missing some documentation on this.

So from my perspective it is not so much a feature request but a documentation completion request now.

Great you found it, thanks @Koopzington !

[michalbundyra]

This repository has been closed and moved to laminas/laminas-db; a new issue has been opened at laminas/laminas-db#117.