IN Clause in filterClause

24 views
Skip to first unread message

Anderson Florencio

unread,
Dec 23, 2015, 1:22:56 PM12/23/15
to zne...@googlegroups.com
Hi Pascal, I altered the setFilterCriteria() method in dao.php for accept array values for IN clauses case.

public function setFilterCriteria() {
        $this
->filterValues = array();
       
foreach (func_get_args() as $value) {
           
if (is_array($value)) {
               
foreach ($value as $val) {
                    $this
->filterValues[] = $val;
               
}
           
} else {
                $this
->filterValues[] = $value;
           
}
       
}
        $this
->result = FALSE;
   
}



There is already a way for the framework to accept an array as parameters in an IN clause?

Pascal Martinez

unread,
Dec 23, 2015, 2:55:03 PM12/23/15
to ZnetDK
Hello Anderson,

You don't need to modify the DAO::setFilterCriteria() method (and in general, it's not suitable to directly change the ZnetDK core classes).

The best way in your case is just to derive from the DAO class to create your custom DAO.

In the example below, I created in app/model/ a new DAO script named "inclausedao.php" that requests the products table (the same table than the online ZnetDK demo) :

<?php
namespace app\model;


class InClauseDAO extends \DAO {


   
protected function initDaoProperties() {
        $this
->table = "products";
   
}


   
public function setInClauseCriteria() {
        $values
= func_get_args();
        $place_holders
= implode(',', array_fill(0, count($values), '?'));
        $this
->filterClause = " WHERE id IN ($place_holders)";
        $this
->filterValues = $values;
   
}
}

The 'setInClauseCriteria()' method is a custom method set to pass its parameters to the "IN" Sql clause.

Now from your custom controller, you can call this custom method to filter the results from the specified IDs:

<?php

namespace app\controller;

class InClauseCtrl extends \AppController {

   
static protected function action_data() {
       
// 1) Request rows from the database
        $response
= new \Response();
        $productsDAO = new \app\model\InClauseDAO();
        $productsDAO
->setInClauseCriteria(1, 3, 5, 8);

        $productsFound
= array();
       
try {
           
while($row = $productsDAO->getResult()) {
                $productsFound
[] = $row;
           
}
            $response
->rows = $productsFound;
            $response
->success = TRUE;
       
} catch (\PDOException $ex) {
            $response
->setFailedMessage("Request data", "Error " .  $ex->getCode());
       
}
       
// 3) Return JSON response
       
return $response;
   
}
}

  
Let me know if this code suits your need.

Pascal MARTINEZ
 
Reply all
Reply to author
Forward
0 new messages