PHP Select::having方法代码示例

 /**
  *
  * @param int $days_threshold days threshold
  * @param int $ca_threshold turnover threshold
  * @param int $limit
  * @return type
  */
 public function getCustomerGeo($days_threshold = 300, $ca_threshold = 1000, $min_accuracy = 6, $limit = 1000)
 {
     $akilia2db = $this->configuration['synchronizer']['db_akilia2'];
     $select = new Select();
     $bcg = new \Zend\Db\Sql\TableIdentifier('base_customer_geo', $akilia2db);
     $bc = new \Zend\Db\Sql\TableIdentifier('base_customer', $akilia2db);
     $bs = new \Zend\Db\Sql\TableIdentifier('base_state', $akilia2db);
     $bco = new \Zend\Db\Sql\TableIdentifier('base_country', $akilia2db);
     $so = new \Zend\Db\Sql\TableIdentifier('sal_order', $akilia2db);
     $sol = new \Zend\Db\Sql\TableIdentifier('sal_order_line', $akilia2db);
     $select->from(["bc" => $bc], [])->join(['bcg' => $bcg], "bc.id = bcg.customer_id", [], Select::JOIN_LEFT)->join(['bs' => $bs], "bs.id = bc.state_id", [], Select::JOIN_LEFT)->join(['bco' => $bco], "bco.id = bc.country_id", [], Select::JOIN_LEFT)->join(['so' => $so], "bc.id = so.customer_id", [], Select::JOIN_INNER)->join(['sol' => $sol], "so.id = sol.order_id", [], Select::JOIN_INNER)->where('bc.flag_archived <> 1');
     $columns = ['customer_id' => new Expression('bc.id'), 'name' => new Expression('bc.name'), 'street' => new Expression('bc.street'), 'street_2' => new Expression('bc.street_2'), 'street_number' => new Expression('bc.street_number'), 'state_reference' => new Expression('bs.reference'), 'state_name' => new Expression('bs.name'), 'zipcode' => new Expression('bc.zipcode'), 'city' => new Expression('bc.city'), 'country' => new Expression('bco.name'), 'accuracy' => new Expression('bcg.accuracy'), 'latitude' => new Expression('bcg.latitude'), 'longitude' => new Expression('bcg.longitude')];
     $select->columns(array_merge($columns, ['total_net' => new Expression('sum(sol.price_total_net)')]), true);
     $select->group($columns);
     $select->having("sum(sol.price_total_net) > {$ca_threshold}");
     $select->where(function (Where $where) use($min_accuracy) {
         //$where->greaterThan('so.date_order', '2012-12-31');
         $where->notLike('bc.name', '%FINISHED%');
         $where->nest->lessThan('accuracy', $min_accuracy)->or->isNull('accuracy')->unnest;
     });
     $select->where(new Expression("(TO_DAYS(NOW()) - TO_DAYS(so.date_order)) < {$days_threshold}"));
     if ($limit > 0) {
         $select->limit($limit);
     }
     $store = $this->getStore($select);
     $data = $store->getData()->toArray();
     return $data;
 }

 /**
  * @testdox unit test: Test reset() resets internal stat of Select object, based on input
  * @covers Zend\Db\Sql\Select::reset
  */
 public function testReset()
 {
     $select = new Select();
     // table
     $select->from('foo');
     $this->assertEquals('foo', $select->getRawState(Select::TABLE));
     $select->reset(Select::TABLE);
     $this->assertNull($select->getRawState(Select::TABLE));
     // columns
     $select->columns(array('foo'));
     $this->assertEquals(array('foo'), $select->getRawState(Select::COLUMNS));
     $select->reset(Select::COLUMNS);
     $this->assertEmpty($select->getRawState(Select::COLUMNS));
     // joins
     $select->join('foo', 'id = boo');
     $this->assertEquals(array(array('name' => 'foo', 'on' => 'id = boo', 'columns' => array('*'), 'type' => 'inner')), $select->getRawState(Select::JOINS));
     $select->reset(Select::JOINS);
     $this->assertEmpty($select->getRawState(Select::JOINS));
     // where
     $select->where('foo = bar');
     $where1 = $select->getRawState(Select::WHERE);
     $this->assertEquals(1, $where1->count());
     $select->reset(Select::WHERE);
     $where2 = $select->getRawState(Select::WHERE);
     $this->assertEquals(0, $where2->count());
     $this->assertNotSame($where1, $where2);
     // group
     $select->group(array('foo'));
     $this->assertEquals(array('foo'), $select->getRawState(Select::GROUP));
     $select->reset(Select::GROUP);
     $this->assertEmpty($select->getRawState(Select::GROUP));
     // having
     $select->having('foo = bar');
     $having1 = $select->getRawState(Select::HAVING);
     $this->assertEquals(1, $having1->count());
     $select->reset(Select::HAVING);
     $having2 = $select->getRawState(Select::HAVING);
     $this->assertEquals(0, $having2->count());
     $this->assertNotSame($having1, $having2);
     // limit
     $select->limit(5);
     $this->assertEquals(5, $select->getRawState(Select::LIMIT));
     $select->reset(Select::LIMIT);
     $this->assertNull($select->getRawState(Select::LIMIT));
     // offset
     $select->offset(10);
     $this->assertEquals(10, $select->getRawState(Select::OFFSET));
     $select->reset(Select::OFFSET);
     $this->assertNull($select->getRawState(Select::OFFSET));
     // order
     $select->order('foo asc');
     $this->assertEquals(array('foo asc'), $select->getRawState(Select::ORDER));
     $select->reset(Select::ORDER);
     $this->assertNull($select->getRawState(Select::ORDER));
 }

 /**
  * @testdox unit test: Test having() returns same Select object (is chainable)
  * @covers Zend\Db\Sql\Select::having
  */
 public function testHaving()
 {
     $select = new Select();
     $return = $select->having(array('x = ?' => 5));
     $this->assertSame($select, $return);
     return $return;
 }

 public function buildSelect(array $conditions)
 {
     $select = new Select();
     if (isset($conditions['from']) && $conditions['from']) {
         $select->from($conditions['from']);
     }
     if (isset($conditions['columns']) && $conditions['columns']) {
         $select->columns($conditions['columns']);
     }
     if (isset($conditions['joins']) && is_array($conditions['joins'])) {
         foreach ($conditions['joins'] as $join) {
             list($join_name, $join_on, $join_columns) = $join;
             if (!isset($join[3])) {
                 $join_type = $select::JOIN_INNER;
             } else {
                 switch ($join[3]) {
                     case 'INNER':
                         $join_type = $select::JOIN_INNER;
                         break;
                     case 'OUTER':
                         $join_type = $select::JOIN_OUTER;
                         break;
                     case 'LEFT':
                         $join_type = $select::JOIN_LEFT;
                         break;
                     case 'RIGHT':
                         $join_type = $select::JOIN_RIGHT;
                         break;
                     default:
                         $join_type = $select::JOIN_INNER;
                         break;
                 }
             }
             if (is_array($join_name)) {
                 $join_table = key($join_name);
             } else {
                 $join_table = $join_name;
             }
             $join_ons = new Predicate\PredicateSet();
             if (is_array($join_on)) {
                 foreach ($join_on as $p) {
                     if ($p instanceof Predicate\PredicateInterface) {
                         $join_ons->addPredicate($p);
                     } else {
                         if (is_array($p) && count($p) == 3) {
                             $join_ons->addPredicate(new Predicate\Operator($p[0], $p[1], $p[2]));
                         } else {
                             if (is_string($p)) {
                                 $join_ons->addPredicate(new Predicate\Expression($p));
                                 if (strpos($p, $join_table . ".bool_deleted") !== false) {
                                     unset($join_table);
                                 }
                             }
                         }
                     }
                 }
             } else {
                 $join_ons->addPredicate(new Predicate\Expression($join_on));
                 if (strpos($join_on, $join_table . ".bool_deleted") !== false) {
                     unset($join_table);
                 }
             }
             if (isset($join_table)) {
                 $join_ons->addPredicate(new Predicate\Expression($join_table . ".bool_deleted = '0'"));
             }
             $select->join($join_name, $join_ons, $join_columns, $join_type);
         }
     }
     if (isset($conditions['like']) && is_array($conditions['like'])) {
         foreach ($conditions['like'] as $column => $val) {
             $conditions['where'][] = new Predicate\Like($column, '%' . $val . '%');
         }
     }
     if (isset($conditions['like_begin']) && is_array($conditions['like_begin'])) {
         foreach ($conditions['like_begin'] as $column => $val) {
             $conditions['where'][] = new Predicate\Like($column, $val . '%');
         }
     }
     if (isset($conditions['like_end']) && is_array($conditions['like_end'])) {
         foreach ($conditions['like_end'] as $column => $val) {
             $conditions['where'][] = new Predicate\Like($column, '%' . $val);
         }
     }
     $select->where($conditions['where']);
     if (isset($conditions['group']) && $conditions['group']) {
         $select->group($conditions['group']);
     }
     if (isset($conditions['having']) && $conditions['having']) {
         if (is_array($conditions['having'])) {
             foreach ($conditions['having'] as $combination => $having) {
                 if ($combination !== Predicate\PredicateSet::OP_OR) {
                     $combination = Predicate\PredicateSet::OP_AND;
                 }
                 $select->having($having, $combination);
             }
         } else {
             $select->having($conditions['having']);
         }
     }
     if (isset($conditions['order'])) {
//.........这里部分代码省略.........