PHP Sql\Where类代码示例

 public function getTranslationListForSearch($filterParams = array())
 {
     $columns = array('id' => 'id', 'type' => 'entity_type', 'content' => 'en', 'entity_name' => new Expression("\n                CASE\n                    WHEN (apartel.id IS NOT NULL) THEN apartel_group.name\n                    WHEN (building.id IS NOT NULL) THEN building.name\n                    WHEN (apartment.id IS NOT NULL) THEN apartment.name\n                    WHEN (office.id IS NOT NULL) THEN office.name\n                    WHEN (parking.id IS NOT NULL) THEN parking.name\n                    WHEN (building_section.id IS NOT NULL) THEN CONCAT(building_for_section.name, ' ', building_section.name)\n                END\n            "));
     $sortColumns = ['id'];
     $result = $this->fetchAll(function (Select $select) use($columns, $sortColumns, $filterParams) {
         $select->columns($columns);
         $select->join(['apartment' => DbTables::TBL_APARTMENTS], new Expression($this->getTable() . '.entity_id = apartment.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$APARTMENT_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT)->join(['apartel' => DbTables::TBL_APARTELS], new Expression($this->getTable() . '.entity_id = apartel.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$APARTEL_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT)->join(['apartel_group' => DbTables::TBL_APARTMENT_GROUPS], 'apartel.apartment_group_id = apartel_group.id', [], $select::JOIN_LEFT)->join(['building' => DbTables::TBL_APARTMENT_GROUPS], new Expression($this->getTable() . '.entity_id = building.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$BUILDING_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT)->join(['building_section' => DbTables::TBL_BUILDING_SECTIONS], new Expression($this->getTable() . '.entity_id = building_section.id AND ' . $this->getTable() . '.entity_type =' . Translation::PRODUCT_TEXTLINE_TYPE_BUILDING_SECTION_APARTMENT_ENTRY), [], $select::JOIN_LEFT)->join(['building_for_section' => DbTables::TBL_APARTMENT_GROUPS], 'building_section.building_id = building_for_section.id', [], $select::JOIN_LEFT)->join(['office' => DbTables::TBL_OFFICES], new Expression($this->getTable() . '.entity_id = office.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$OFFICE_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT)->join(['parking' => DbTables::TBL_PARKING_LOTS], new Expression($this->getTable() . '.entity_id = parking.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$PARKING_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT);
         $where = new Where();
         $textSearch = '%' . strip_tags(trim($filterParams["srch_txt"])) . '%';
         if ((int) $filterParams["id_translation"] > 0) {
             $where->equalTo($this->getTable() . '.entity_id', $filterParams["id_translation"]);
         } else {
             if ($filterParams["srch_txt"] != '') {
                 $where->NEST->like('apartment.name', $textSearch)->or->NEST->like('apartel_group.name', $textSearch)->or->NEST->like('building.name', $textSearch)->or->NEST->like('office.name', $textSearch)->or->like('parking.name', $textSearch)->or->like($this->getTable() . '.en_html_clean', $textSearch)->UNNEST;
             }
         }
         if ((int) $filterParams['product_type']) {
             $where->equalTo($this->getTable() . '.type', (int) $filterParams['product_type']);
         }
         $select->where($where);
         $select->group($this->getTable() . '.id');
         $select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
     });
     $statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
     $resultCount = $statement->execute();
     $row = $resultCount->current();
     $total = $row['total'];
     return ['result' => $result, 'total' => $total];
 }

 public function removeSerie($userId, $serieId)
 {
     $where = new Where();
     $where->equalTo('user_id', $userId);
     $where->equalTo('sid', $serieId);
     return parent::delete($where, $this->tableName);
 }

 /**
  * @param int $apartmentId
  * @param string $startDate
  * @param string $endDate
  *
  * @return array
  */
 public function getMonthlyCost($apartmentId, $startDate, $endDate)
 {
     $expenses = $this->fetchAll(function (Select $select) use($apartmentId, $startDate, $endDate) {
         $nestedWhere = new Where();
         $nestedWhere->lessThanOrEqualTo('item.period_from', $endDate)->or->greaterThanOrEqualTo('item.period_to', $startDate);
         $select->columns(['amount' => 'amount'])->join(['item' => DbTables::TBL_EXPENSE_ITEM], $this->getTable() . '.expense_item_id = item.id', ['period_from', 'period_to', 'date_created', 'is_refund'], Select::JOIN_LEFT)->join(['expense' => DbTables::TBL_EXPENSES], 'item.expense_id = expense.id', [], Select::JOIN_LEFT)->where->equalTo('expense.status', Ticket::STATUS_GRANTED)->equalTo($this->getTable() . '.cost_center_id', $apartmentId)->equalTo($this->getTable() . '.cost_center_type', ExpenseCosts::TYPE_APARTMENT)->addPredicate($nestedWhere);
     });
     $result = [];
     foreach ($expenses as $expense) {
         $monthStart = date("n", strtotime($expense['period_from']));
         $yearStart = date("Y", strtotime($expense['period_from']));
         $monthEnd = date("n", strtotime($expense['period_to']));
         $yearEnd = date("Y", strtotime($expense['period_to']));
         $startInMonths = $monthStart + $yearStart * 12;
         $endInMonths = $monthEnd + $yearEnd * 12;
         $sign = $expense['is_refund'] ? -1 : 1;
         $expensePerMonth = $sign * $expense['amount'] / ($endInMonths - $startInMonths + 1);
         for ($iterator = $startInMonths; $iterator <= $endInMonths; $iterator++) {
             $tempMonth = $iterator % 12;
             $tempMonth = $tempMonth ? $tempMonth : 12;
             $currentYear = ($iterator - $tempMonth) / 12;
             $currentMonth = date('M', mktime(0, 0, 0, $tempMonth, 1));
             $key = $currentMonth . '_' . $currentYear;
             if (isset($result[$key])) {
                 $result[$key] += $expensePerMonth;
             } else {
                 $result[$key] = $expensePerMonth;
             }
         }
     }
     return $result;
 }

 public function getBlackList($data)
 {
     $result = $this->fetchAll(function (Select $select) use($data) {
         $select->join(['reservation' => DbTables::TBL_BOOKINGS], $this->getTable() . '.reservation_id = reservation.id', ['res_number'], $select::JOIN_LEFT);
         $where = new Where();
         $where->equalTo('hash', '');
         if (!empty($data['email'])) {
             $where->or->equalTo('hash', $data['email']);
         }
         if (!empty($data['fullNamePhone'])) {
             $where->or->equalTo('hash', $data['fullNamePhone']);
         }
         if (!empty($data['fullNameAddress'])) {
             $where->or->equalTo('hash', $data['fullNameAddress']);
         }
         if (!empty($data['fullName'])) {
             $where->or->equalTo('hash', $data['fullName']);
         }
         if (!empty($data['phone'])) {
             $where->or->equalTo('hash', $data['phone']);
         }
         $select->where($where);
     });
     return $result;
 }

 /**
  * Creates a new square manager object.
  *
  * Preloads all available squares from the database.
  *
  * @param SquareTable $squareTable
  * @param SquareMetaTable $squareMetaTable
  * @param string $locale
  */
 public function __construct(SquareTable $squareTable, SquareMetaTable $squareMetaTable, $locale)
 {
     $this->squareTable = $squareTable;
     $this->squareMetaTable = $squareMetaTable;
     $select = $squareTable->getSql()->select();
     $select->order('priority ASC');
     $resultSet = $squareTable->selectWith($select);
     $this->squares = SquareFactory::fromResultSet($resultSet);
     /* Load square meta data */
     if ($this->squares) {
         $sids = array();
         foreach ($this->squares as $square) {
             $sids[] = $square->need('sid');
         }
         reset($this->squares);
         $metaWhere = new Where();
         $metaWhere->in('sid', $sids);
         $metaWhere->and;
         $metaWhereNested = $metaWhere->nest();
         $metaWhereNested->isNull('locale');
         $metaWhereNested->or;
         $metaWhereNested->equalTo('locale', $locale);
         $metaWhereNested->unnest();
         $metaSelect = $this->squareMetaTable->getSql()->select();
         $metaSelect->where($metaWhere);
         $metaSelect->order('locale ASC');
         $metaResultSet = $this->squareMetaTable->selectWith($metaSelect);
         SquareFactory::fromMetaResultSet($this->squares, $metaResultSet);
         /* Prepare active squares */
         $this->activeSquares = $this->getAllVisible();
     }
 }

 public function fetchFeed($params = null)
 {
     $sql = new Sql($this->adapter);
     $select = $sql->select()->from($this->table);
     $params['orderColumn'] = 'id';
     $params['orderDirection'] = 'DESC';
     $tableSchemaArray = TableSchema::getSchemaArray($this->table);
     $hasActiveColumn = $this->schemaHasActiveColumn($tableSchemaArray);
     $params = $this->applyDefaultEntriesSelectParams($params);
     $columns = array('id', 'identifier', 'action', 'table_name', 'row_id', 'user', 'datetime', 'type', 'data');
     $select->columns($columns);
     // ->order('id DESC');
     $select->where->nest->isNull('parent_id')->OR->equalTo('type', 'FILES')->unnest;
     $select = $this->applyParamsToTableEntriesSelect($params, $select, $tableSchemaArray, $hasActiveColumn);
     //die($this->dumpSql($select));
     $rowset = $this->selectWith($select);
     $rowset = $rowset->toArray();
     foreach ($rowset as &$row) {
         $row['datetime'] .= ' UTC';
     }
     $countTotalWhere = new Where();
     $countTotalWhere->isNull('parent_id')->OR->equalTo('type', 'FILES');
     $activityTotal = $this->countTotal($countTotalWhere);
     return array('total' => $activityTotal, 'rows' => $rowset);
 }

 public function fillInventory($userId, $startDate, $officeId)
 {
     /** @var \DDD\Dao\User\Schedule\Inventory $inventoryDao */
     $inventoryDao = $this->getServiceLocator()->get('dao_user_schedule_inventory');
     $deleteWhere = new Where();
     $deleteWhere->equalTo('user_id', $userId)->greaterThanOrEqualTo('date', date('Y-m-d', strtotime($startDate)))->notEqualTo('is_changed', 1);
     $inventoryDao->delete($deleteWhere);
     $scheduleData = $this->getUserSchedule($userId);
     $scheduleArr = [];
     if ($scheduleData) {
         foreach ($scheduleData as $daySchedule) {
             $scheduleArr[$daySchedule->getDay()] = $daySchedule;
         }
     }
     // Fill up to 3 months ahead
     $endDate = date('Y-m-d', strtotime('+92 days'));
     $dateIterator = date('Y-m-d', strtotime($startDate));
     $insertArray = [];
     while ($dateIterator < $endDate) {
         /** @var \DDD\Domain\User\Schedule\Schedule $daySchedule */
         if (count($scheduleArr)) {
             for ($scheduleIndex = 1; $scheduleIndex <= count($scheduleArr); $scheduleIndex++) {
                 $daySchedule = $scheduleArr[$scheduleIndex];
                 array_push($insertArray, ['user_id' => $userId, 'office_id' => $officeId, 'date' => $dateIterator, 'availability' => $daySchedule->isActive(), 'time_from1' => $daySchedule->isActive() ? $daySchedule->getTimeFrom1() : '', 'time_to1' => $daySchedule->isActive() ? $daySchedule->getTimeTo1() : '', 'time_from2' => $daySchedule->isActive() ? $daySchedule->getTimeFrom2() : '', 'time_to2' => $daySchedule->isActive() ? $daySchedule->getTimeTo2() : '']);
                 $dateIterator = date('Y-m-d', strtotime($dateIterator . '+1 days'));
             }
             // The case when user has no schedule scheme. Fill the inventory with 0 availability
         } else {
             array_push($insertArray, ['user_id' => $userId, 'office_id' => $officeId, 'date' => $dateIterator, 'availability' => 0, 'time_from1' => '', 'time_to1' => '', 'time_from2' => '', 'time_to2' => '']);
             $dateIterator = date('Y-m-d', strtotime($dateIterator . '+1 days'));
         }
     }
     $inventoryDao->multiInsert($insertArray, true);
 }

 /**
  * @param array $typeList
  * @param bool|true $onlyActive
  * @return \DDD\Domain\Warehouse\Category\Category[]
  */
 public function getCategoriesByTypeList($typeList = [], $onlyActive = true, $selectedId = 0, $returnArray = false)
 {
     $entity = $this->getEntity();
     $this->setEntity(new \DDD\Domain\Warehouse\Category\Category());
     if ($returnArray) {
         $this->setEntity(new \ArrayObject());
     }
     $result = $this->fetchAll(function (Select $select) use($typeList, $onlyActive, $selectedId) {
         $select->columns(['id', 'name', 'type' => 'type_id', 'inactive']);
         $nestedWhere = new Where();
         $where = new Where();
         if (!empty($typeList)) {
             $nestedWhere->in('type_id', $typeList);
         }
         if ($onlyActive) {
             $nestedWhere->equalTo('inactive', AssetsCategoryService::CATEGORY_STATUS_ACTIVE);
         }
         if ($selectedId) {
             $where->equalTo('id', $selectedId)->orPredicate($nestedWhere);
         } else {
             $where = $nestedWhere;
         }
         $select->where($where)->order('type_id');
     });
     $this->setEntity($entity);
     return $result;
 }

 public function search($str)
 {
     $select = new Select('video');
     $spec = new Where();
     $spec->like('title', '% ' . $str . ' %');
     $select->where($spec);
     $select->where->OR->like('title', $str . ' %');
     $select->where->OR->like('title', '% ' . $str);
     //$select->where(array('title'=>$str));
     //$select->where(array('catelog'=>$title,'description'=>$description));
     $select->order('id ASC');
     /*
                  // create a new result set based on the Album entity
                  $resultSetPrototype = new ResultSet();
                  $resultSetPrototype->setArrayObjectPrototype(new Search());
                  // create a new pagination adapter object
                  $paginatorAdapter = new DbSelect(
                      // our configured select object
                      $select,
                      // the adapter to run it against
                      $this->tableGateway->getAdapter(),
                      // the result set to hydrate
                      $resultSetPrototype
                  );
                  $paginator = new Paginator($paginatorAdapter);
                 
                  return $paginator;
     */
     return $this->tableGateway->selectWith($select);
 }

 public function fetchFeed($params = null)
 {
     $sql = new Sql($this->adapter);
     $select = $sql->select()->from($this->table);
     $params['orderColumn'] = 'id';
     $params['orderDirection'] = 'DESC';
     $tableSchemaArray = TableSchema::getSchemaArray($this->table);
     $hasActiveColumn = $this->schemaHasActiveColumn($tableSchemaArray);
     $params = $this->applyDefaultEntriesSelectParams($params);
     $columns = ['id', 'identifier', 'action', 'table_name', 'row_id', 'user', 'datetime', 'type', 'data'];
     $select->columns($columns);
     // ->order('id DESC');
     $select->where->nest->isNull('parent_id')->OR->equalTo('type', 'FILES')->unnest;
     $select = $this->applyParamsToTableEntriesSelect($params, $select, $tableSchemaArray, $hasActiveColumn);
     //die($this->dumpSql($select));
     $rowset = $this->selectWith($select);
     $rowset = $rowset->toArray();
     $rowset = $this->convertDates($rowset, $tableSchemaArray);
     //        @TODO: Returns date in ISO 8601 Ex: 2016-06-06T17:18:20Z
     //        see: https://en.wikipedia.org/wiki/ISO_8601
     //        foreach ($rowset as &$row) {
     //            $row['datetime'] .= ' UTC';
     //        }
     $countTotalWhere = new Where();
     $countTotalWhere->isNull('parent_id')->OR->equalTo('type', 'FILES');
     $activityTotal = $this->countTotal($countTotalWhere);
     return ['total' => $activityTotal, 'rows' => $rowset];
 }

 /**
  * 
  * @param string $search
  * @return mixed
  */
 public function getDocumento($search = '')
 {
     $select = $this->sql->select()->from(array('t1' => 'bdc_documento'))->join(array('t2' => 'bdc_tipo_documento'), 't1.tipd_id=t2.tipd_id', array('*'));
     $where = new Where();
     $where->equalTo('doc_numero', $search);
     $select->where($where);
     return $this->fetchRow($select);
 }

 public function findByContactId($id)
 {
     $sql = new Select();
     $sql->from(array('cc' => 'contact_companies'))->join(array('c' => 'contact_company'), 'c.company_id = cc.company_id');
     $where = new Where();
     $where->equalTo('cc.contact_id', $id);
     return $this->select($sql->where($where));
 }

 public function getSkuIdByName($sku)
 {
     return $this->fetchOne(function (Select $select) use($sku) {
         $where = new Where();
         $where->equalTo('name', $sku);
         $select->columns(['id', 'asset_category_id'])->where($where);
     });
 }

 public function getChatMesByGetter($getter)
 {
     $where = new Where();
     $where->equalTo('getter', $getter);
     $where->equalTo('isread', 1);
     $rowset = $this->tableGateway->select($where);
     return iterator_to_array($rowset);
 }

 public function fetchUserTimeline($user)
 {
     $select = $this->getSql()->select();
     $where = new Where();
     $where->equalTo('username', $user);
     $select->where($where)->order('timestamp DESC');
     return new Paginator(new DbSelect($select, $this->adapter, $this->resultSetPrototype));
 }