ZF2 aka Apigility SQL with predicates

Question

I'm attempting to use Predicates within ZF2 in order to query on a date type column named column_date

Nothing seems to work for the any columns and I am so new I am unsure where to start..

        $query = $this->tableGateway->getSql()->select();

        $predicate = new Where();
        $query->where($predicate->equalTo('user_id' , $params['user_id']));
        $query->where($predicate->equalTo('super_type', 'steep'));
        $query->where($predicate->greaterThan('column_date',$dateRangeArray[0]));
        $query->where($predicate->lessThan('column_date', $dateRangeArray[1]));

        $rowset = $this->tableGateway->select($query);
        $resultArr = $rowset->toArray();

This is returning things that have incorrect user_id, incorrect column_date in them.. why?

dateRangeArray looks like this :

dateRangeArray[0] = 2014-09-01 dateRangeArray[1] = 2015-09-01

I'm not sure if this is the problem but try this: `$predicate = new Where(); $predicate->equalTo('user_id' , $params['user_id']) ->equalTo('super_type', 'steep') ->greaterThan('column_date',$dateRangeArray[0]) ->lessThan('column_date', $dateRangeArray[1]);` `$query->where($predicate); ` — Ronnie, Sep 25 '14 at 12:55
@Demo , I'm still wondering why the chaining is required.. however I did get something working. Everything works except the `greaterThan` `lessThan` query parts.. I had to generate a raw Query String for those and inject it. — Erik, Sep 25 '14 at 14:25
Oh, well the chaining isn't needed. My point was that only one `$query->where(...)` was needed. Now I realize that in greater than and less than you are comparing dates, so maybe that's the problem. — Ronnie, Sep 25 '14 at 14:32
The easiest way I can think of is to strip the `-` in the dates(also save them this way in the db) and you'll compare integers (20140901) — Ronnie, Sep 25 '14 at 14:40
Ok and a wild guess try: `$query->where(new \Zend\Db\Sql\Predicate\Expression("DATEDIFF(day, 'column_date', {$dateRangeArray[0]}) > ?", 0));` — Ronnie, Sep 25 '14 at 15:07

score 0 · Accepted Answer · answered Oct 06 '14 at 22:00

This is now working properly within Apigility's TableGateway

    //parses into an array of YYYY-MM-DD
    $dateRangeArray = $transformer->parseDateRange($activity_date_range, "ymd");

    $start_date_formatted = date('Y-m-d', strtotime($dateRangeArray[0]));
    $end_date_formatted = date('Y-m-d', strtotime($dateRangeArray[1]));


    // Query 
    $query = $this->getSql()->select()
        ->where->equalTo('user_id' , $user_id)
        ->where->greaterThanOrEqualTo('field_date ', $start_date_formatted)
        ->where->lessThanOrEqualTo('fieldy_date',$end_date_formatted);


    $rowset = $this->select($query);
    $resultArr = $rowset->toArray();

    return $resultArr;

ZF2 aka Apigility SQL with predicates

1 Answers1