PHP Select::where方法代码示例

 public function get($outputInventoryId, $id = false)
 {
     $select = new Select($this->tableGateway->getTable());
     $select->join('output_inventory', "output_inventory.id = " . $this->tableGateway->getTable() . ".output_inventory", array(), 'inner');
     $select->where(array($this->tableGateway->getTable() . ".output_inventory" => $outputInventoryId));
     if ($id) {
         $select->where(array($this->tableGateway->getTable() . ".id" => $id));
         $resultSet = $this->tableGateway->selectWith($select);
         $result = $resultSet->current();
         $product = $this->productTable->getName(false, $result->getProduct());
         $result->setProduct(implode($product));
     } else {
         $resultSet = $this->tableGateway->selectWith($select);
         $result = array();
         foreach ($resultSet as $rows) {
             $product = $this->productTable->getName(false, $rows->getProduct());
             $rows->setProduct(implode($product));
             switch ($rows->getIva()) {
                 case 1:
                     $rows->setIvaAccumulated($rows->getCost() - $rows->getCost() / 1.16);
                     $rows->setCost($rows->getCost() / 1.16);
                     break;
                 case 2:
                     $rows->setIvaAccumulated($rows->getCost() * 0.16);
                     break;
                 case 3:
                     $rows->setIvaAccumulated(0);
                     break;
             }
             $result[] = $rows;
         }
     }
     return $result;
 }

 public function fetchAll($paginate = true, $filter = array(), $orderBy = array())
 {
     if ($paginate) {
         $select = new Select('service_language');
         $select->join('lookup_status', 'lookup_status.status_id = service_language.status_id', array('status'), 'left');
         /* Data filter code start here*/
         if (count($filter) > 0) {
             $filter['language_name'] != "" ? $select->where("service_language.language_name LIKE '%" . $filter['language_name'] . "%'") : "";
             $filter['status_id'] != "" ? $select->where("service_language.status_id = " . $filter['status_id']) : "";
         }
         /* Data filter code end here*/
         /* Data sorting code starts here */
         if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') {
             switch ($orderBy['sort_field']) {
                 case 'language':
                     $select->order('service_language.language_name ' . $orderBy['sort_order']);
                     break;
                 case 'status':
                     $select->order('lookup_status.status ' . $orderBy['sort_order']);
                     break;
             }
         }
         /* Data sorting code ends here */
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new ServiceLanguages());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         $paginator = new Paginator($paginatorAdapter);
         return $paginator;
     }
     return $this->tableGateway->select();
 }

 /**
  *
  * @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;
 }

 /**
  * Get properties
  *
  * @param boolean $forceReload to initiliaze properties
  *
  * @return array
  */
 public function getProperties($forceReload = false)
 {
     if ($this->getData('properties') == null or $forceReload) {
         $select = new Select();
         $select->from('tab')->columns(array())->join('property', 'tab.id = property.tab_id', '*', Select::JOIN_INNER);
         if ($this->getDocumentId() !== null) {
             $select->join('document', 'document.document_type_id = tab.document_type_id', array(), Select::JOIN_INNER);
             $select->join('property_value', 'document.id = property_value.document_id AND property.id = property_value.property_id', array('value'), Select::JOIN_LEFT);
             $select->where(array('document.id' => $this->getDocumentId()));
         }
         if ($this->getTabId() != null) {
             $select->where(array('tab.id' => $this->getTabId()));
         }
         if ($this->getDocumentTypeId() != null) {
             $select->where(array('tab.document_type_id' => $this->getDocumentTypeId()));
         }
         $select->order('property.sort_order ASC');
         $rows = $this->fetchAll($select);
         $properties = array();
         foreach ($rows as $row) {
             $propertyModel = Model::fromArray((array) $row);
             if ($this->getDocumentId() !== null) {
                 $propertyModel->setDocumentId($this->getDocumentId());
             }
             $properties[] = $propertyModel;
         }
         $this->setData('properties', $properties);
     }
     return $this->getData('properties');
 }

 public function setParameters(Parameters $params)
 {
     $groupsPostsTable = Api::_()->getDbTable('Group\\DbTable\\GroupsPosts');
     $groupsPostsTableName = $groupsPostsTable->initTableName()->getTable();
     $groupsCategoriesTable = Api::_()->getDbTable('Group\\DbTable\\CategoriesGroups');
     $groupsCategoriesTableName = $groupsCategoriesTable->initTableName()->getTable();
     if ($params->group_id || $params->groupCategory) {
         $params->inGroup = true;
     }
     if ($params->inGroup) {
         $groupId = $params->group_id;
         $categoryId = $params->groupCategory;
         $this->where(function ($where) use($groupsPostsTableName, $groupsCategoriesTableName, $groupId, $categoryId) {
             $select = new Select($groupsPostsTableName);
             $select->columns(array('post_id'));
             if ($groupId) {
                 $select->where(array('group_id' => $groupId));
             }
             if ($categoryId) {
                 $cateSelect = new Select($groupsCategoriesTableName);
                 $cateSelect->columns(array('group_id'));
                 $cateSelect->where(array('category_id' => $categoryId));
                 $select->where(function ($where) use($cateSelect) {
                     $where->in('group_id', $cateSelect);
                     return $where;
                 });
             }
             $where->in('id', $select);
             return $where;
         });
     }
     return parent::setParameters($params);
 }

 public function fetchAll($paginate = true, $filter = array(), $orderBy = array())
 {
     if ($paginate) {
         $select = new Select('booking');
         $select->columns(array('*', new Expression("service_provider.first_name as sp_first_name, service_provider.last_name as sp_last_name,\n\t\t\tinvoice.status_id AS invoice_status,payment_history.currency as currency,CASE invoice.status_id WHEN 0 THEN 'Unpaid' WHEN 1 THEN 'Paid' WHEN 2 THEN 'Partially Paid' END AS PaymentStatus")));
         $select->join('booking_suggestion_history', 'booking_suggestion_history.booking_id = booking.id', array('booking_time', 'booking_status'), 'inner');
         $select->join('users', 'users.id = booking.user_id', array('first_name', 'last_name'), 'left');
         $select->join(array('service_provider' => 'users'), 'service_provider.id = booking.service_provider_id', array(), 'left');
         $select->join('service_provider_service', 'service_provider_service.id = booking.service_provider_service_id', array('duration', 'price'), 'left');
         $select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'left');
         $select->join('invoice', 'invoice.id = booking.invoice_id', array('invoice_total', 'site_commision'), 'inner');
         $select->join('invoice_details', 'invoice_details.invoice_id = invoice.id', array('sale_item_details'), 'inner');
         $select->join('payment_history', 'payment_history.invoice_id = invoice.id', array(), 'inner');
         $select->join('lookup_status', 'lookup_status.status_id = booking_suggestion_history.booking_status', array('status'), 'left');
         $select->where('booking_suggestion_history.id = (SELECT id FROM booking_suggestion_history WHERE booking_id = booking.id ORDER BY id DESC LIMIT 1)');
         count($filter) > 0 ? $select->where($filter) : "";
         /* Data sorting code starts here */
         if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') {
             switch ($orderBy['sort_field']) {
                 case 'user':
                     $select->order('users.first_name ' . $orderBy['sort_order']);
                     break;
                 case 'service_provider':
                     $select->order('service_provider.first_name ' . $orderBy['sort_order']);
                     break;
                 case 'service':
                     $select->order('service_category.category_name ' . $orderBy['sort_order']);
                     break;
                 case 'booked_date':
                     $select->order('booking.booked_date ' . $orderBy['sort_order']);
                     break;
             }
         } else {
             $select->order('booking.id desc');
         }
         /* Data sorting code ends here */
         //echo str_replace('"', '', $select->getSqlString()); exit;
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new Bookings());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         $paginator = new Paginator($paginatorAdapter);
         return $paginator;
     } else {
         $select = $this->tableGateway->getSql()->select();
         $select->columns(array('*', new Expression("service_provider.first_name as sp_first_name, service_provider.last_name as sp_last_name,\n\t\t\tinvoice.status_id AS invoice_status,CASE invoice.status_id WHEN 0 THEN 'Unpaid' WHEN 1 THEN 'Paid' WHEN 2 THEN 'Partially Paid' END AS PaymentStatus")));
         $select->join('booking_suggestion_history', 'booking_suggestion_history.booking_id = booking.id', array('booking_time', 'booking_status'), 'inner');
         $select->join('users', 'users.id = booking.user_id', array('first_name', 'last_name'), 'left');
         $select->join(array('service_provider' => 'users'), 'service_provider.id = booking.service_provider_id', array(), 'left');
         $select->join('service_provider_service', 'service_provider_service.id = booking.service_provider_service_id', array('duration', 'price'), 'left');
         $select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'left');
         $select->join('invoice', 'invoice.id = booking.invoice_id', array('invoice_total', 'site_commision', 'created_date'), 'inner');
         $select->join('invoice_details', 'invoice_details.invoice_id = invoice.id', array('sale_item_details'), 'inner');
         $select->join('payment_history', 'payment_history.invoice_id = invoice.id', array(), 'inner');
         $select->join('lookup_status', 'lookup_status.status_id = booking_suggestion_history.booking_status', array('status'), 'left');
         $select->where('booking_suggestion_history.id = (SELECT id FROM booking_suggestion_history WHERE booking_id = booking.id ORDER BY id DESC LIMIT 1)');
         count($filter) > 0 ? $select->where($filter) : "";
         return $this->tableGateway->selectwith($select);
     }
 }

 /**
  * Sets the filter
  * @param \Zend\Db\Sql\Select $select
  * @param array $criteria
  * @return \Zend\Db\Sql\Select
  */
 private function setFilter($select, $criteria)
 {
     $select->columns(array('*', 'role' => new \Zend\Db\Sql\Predicate\Expression('(SELECT `description` FROM `profile_role` WHERE `profile_role`.`id`=`profile`.`role_id` )')));
     if (array_key_exists('role_id', $criteria) && $criteria['role_id'] != '') {
         $select->where('`profile`.`role_id` = ' . intval($criteria['role_id']));
     }
     if (array_key_exists('sSearch', $criteria) && $criteria['sSearch'] != '') {
         $select->where(' ( `profile`.`first_name` LIKE "' . addslashes($criteria['sSearch']) . '%" OR `profile`.`last_name` LIKE "' . addslashes($criteria['sSearch']) . '%" ) ');
     }
     return $select;
 }

 /**
  * Get config value
  *
  * @param string $source Source
  * @param string $locale Optional locale
  *
  * @return string value
  */
 public function getValue($source, $locale = null)
 {
     $select = new Select();
     $select->from('core_translate')->columns(array('src_id' => 'id', 'source'))->join('core_translate_locale', 'core_translate.id = core_translate_locale.core_translate_id', array('dst_id' => 'id', 'destination', 'locale'), Select::JOIN_INNER);
     if (!empty($source)) {
         $select->where(array('core_translate.source' => $source));
     }
     if (!empty($locale)) {
         $select->where(array('core_translate_locale.locale' => $locale));
     }
     return $this->fetchRow($select);
 }

 public function buscarTodos($paginado = false, array $filtro = array())
 {
     $sqlTotal = new Select();
     $sqlTotal->from('despesas')->columns(['total' => new Expression('SUM(valor)')])->join('clientes', 'despesas.fk_cliente = clientes.id', []);
     $sqlTotalPendente = new Select();
     $sqlTotalPendente->from('despesas')->columns(['totalPendente' => new Expression('SUM(valor)')])->join('clientes', 'despesas.fk_cliente = clientes.id', [])->where(['despesas.pagamento' => 'N']);
     $sqlTotalRecebido = new Select();
     $sqlTotalRecebido->from('despesas')->columns(['totalRecebido' => new Expression('SUM(valor)')])->join('clientes', 'despesas.fk_cliente = clientes.id', [])->where(['despesas.pagamento' => 'S']);
     $sqlPrincipal = new Select();
     $sqlPrincipal->from(['des' => 'despesas'])->columns(['id', 'fk_categoria', 'fk_subcategoria', 'fk_cliente', 'fk_conta', 'fk_cartao', 'descricao', 'valor', 'repetir', 'repetir_quando', 'repetir_ocorrencia', 'data_fatura', 'data_vencimento' => new Expression('DATE_FORMAT(data_vencimento, "%d/%m/%Y")'), 'pagamento' => new Expression('IF (pagamento = "S", "Efetivado", "Não Efetivado")'), 'pagamento_data' => new Expression('DATE_FORMAT(pagamento_data, "%d/%m/%Y")'), 'total' => new Expression('?', [$sqlTotal]), 'totalPendente' => new Expression('?', [$sqlTotalPendente]), 'totalRecebido' => new Expression('?', [$sqlTotalRecebido])])->join(['cli' => 'clientes'], 'des.fk_cliente = cli.id', ['cli_nome' => 'nome'])->join(['dca' => 'despesas_categorias'], 'des.fk_categoria = dca.id', ['dca_nome' => 'nome'])->join(['con' => 'contas'], 'des.fk_conta = con.id', ['con_nome' => 'nome'])->order('des.id Desc');
     //Retorna as receitas do mês corrente.
     if (!isset($filtro['data']) && !isset($filtro['data_inicio']) && !isset($filtro['data_fim'])) {
         $parteSql = 'DATE_FORMAT(data_vencimento, "%Y-%m") = DATE_FORMAT(NOW(), "%Y-%m")';
         $sqlTotalPendente->where($parteSql);
         $sqlTotalRecebido->where($parteSql);
         $sqlTotal->where($parteSql);
         $sqlPrincipal->where($parteSql);
     }
     //Filtros
     if (count($filtro) > 0) {
         $arrayFiltro = array_filter(['des.descricao' => isset($filtro['descricao']) ? filter_var($filtro['descricao'], FILTER_SANITIZE_STRING) . '%' : null, 'des.pagamento' => isset($filtro['pagamento']) ? filter_var($filtro['pagamento'], FILTER_SANITIZE_STRING) : null, 'dca.nome' => isset($filtro['categoria']) ? filter_var($filtro['categoria'], FILTER_SANITIZE_STRING) . '%' : null, 'con.nome' => isset($filtro['conta']) ? filter_var($filtro['conta'], FILTER_SANITIZE_STRING) . '%' : null, 'des.valor' => isset($filtro['valor']) ? filter_var($filtro['valor'], FILTER_SANITIZE_STRING) : null]);
         foreach ($arrayFiltro as $chave => $valor) {
             $sqlPrincipal->where->like($chave, $valor);
         }
         if (isset($filtro['data_inicio']) && !empty('data_inicio') && isset($filtro['data_fim']) && !empty($filtro['data_fim'])) {
             $dtInicio = filter_var($filtro['data_inicio'], FILTER_SANITIZE_NUMBER_INT);
             $dtFim = filter_var($filtro['data_fim'], FILTER_SANITIZE_NUMBER_INT);
             $dataInicio = implode('-', array_reverse(explode('-', $dtInicio)));
             $dataFim = implode('-', array_reverse(explode('-', $dtFim)));
             $sqlTotalPendente->where->between('des.data_vencimento', $dataInicio, $dataFim);
             $sqlTotal->where->between('des.data_vencimento', $dataInicio, $dataFim);
             $sqlTotalRecebido->where->between('des.data_vencimento', $dataInicio, $dataFim);
             $sqlPrincipal->where->between('des.data_vencimento', $dataInicio, $dataFim);
         }
         //Retorna as receitas de acordo com o filtro data
         if (isset($filtro['data']) && !empty($filtro['data']) && !isset($filtro['data_inicio']) && !isset($filtro['data_fim'])) {
             $data = filter_var($filtro['data'], FILTER_SANITIZE_NUMBER_INT);
             $parteSql = ['DATE_FORMAT(data_vencimento, "%Y-%m") = DATE_FORMAT(?, "%Y-%m")' => $data];
             $sqlTotalPendente->where($parteSql);
             $sqlTotalRecebido->where($parteSql);
             $sqlTotal->where($parteSql);
             $sqlPrincipal->where($parteSql);
         }
     }
     if ($paginado) {
         $paginatorAdapter = new DbSelect($sqlPrincipal, $this->getAdapter());
         $paginado = new Paginator($paginatorAdapter);
         return $paginado;
     }
     $dados = $this->selectWith($sqlPrincipal);
     return $dados;
 }

 public function buscarTodos($paginado = false, array $filtro = array())
 {
     $sqlTotal = new Select();
     $sqlTotal->from('receitas')->columns(['total' => new Expression('SUM(receitas.valor)')])->join('clientes', 'receitas.fk_cliente = clientes.id', []);
     $sqlTotalPendente = new Select();
     $sqlTotalPendente->from('receitas')->columns(['total_pendente' => new Expression('SUM(receitas.valor)')])->join('clientes', 'receitas.fk_cliente = clientes.id', [])->where(['receitas.pagamento' => 'N']);
     $sqlTotalRecebido = new Select();
     $sqlTotalRecebido->from('receitas')->columns(['total' => new Expression('SUM(receitas.valor)')])->join('clientes', 'receitas.fk_cliente = clientes.id', [])->where(['receitas.pagamento' => 'S']);
     $sqlPrincipal = new Select();
     $sqlPrincipal->from(array('rec' => 'receitas'))->columns(array('id', 'descricao', 'valor', 'pagamento' => new Expression('IF (pagamento = "S", "Efetivado", "Não Efetivado")'), 'pagamento_data' => new Expression("DATE_FORMAT(pagamento_data, '%d/%m/%Y')"), 'total' => new Expression('?', array($sqlTotal)), 'total_pendente' => new Expression('?', array($sqlTotalPendente)), 'total_recebido' => new Expression('?', array($sqlTotalRecebido))))->join(array('cli' => 'clientes'), 'rec.fk_cliente = cli.id', array('cli_nome' => 'nome'))->join(array('con' => 'contas'), 'rec.fk_conta = con.id', array('con_nome' => 'nome'))->join(array('cat' => 'receitas_categorias'), 'rec.fk_categoria = cat.id', array('cat_nome' => 'nome'))->order('rec.id Desc');
     //Retorna as receitas do mês corrente.
     if (!isset($filtro['data']) && !isset($filtro['data_inicio']) && !isset($filtro['data_fim'])) {
         $parteSql = 'DATE_FORMAT(data_vencimento, "%Y-%m") = DATE_FORMAT(NOW(), "%Y-%m")';
         $sqlTotalPendente->where($parteSql);
         $sqlTotalRecebido->where($parteSql);
         $sqlTotal->where($parteSql);
         $sqlPrincipal->where($parteSql);
     }
     //Outros filtros
     if (count($filtro) > 0) {
         $arrayFiltro = array('rec.descricao' => isset($filtro['descricao']) ? filter_var($filtro['descricao'], FILTER_SANITIZE_STRING) . '%' : null, 'cat.nome' => isset($filtro['categoria']) ? filter_var($filtro['categoria'], FILTER_SANITIZE_STRING) . '%' : null, 'con.nome' => isset($filtro['conta']) ? filter_var($filtro['conta'], FILTER_SANITIZE_STRING) . '%' : null, 'rec.pagamento' => isset($filtro['pagamento']) ? filter_var($filtro['pagamento'], FILTER_SANITIZE_STRING) : null, 'rec.valor' => isset($filtro['valor']) ? filter_var($filtro['valor'], FILTER_SANITIZE_STRING) : null);
         foreach (array_filter($arrayFiltro) as $chave => $valor) {
             $sqlPrincipal->where->like($chave, $valor);
         }
         if (isset($filtro['data_inicio']) && !empty('data_inicio') && isset($filtro['data_fim']) && !empty($filtro['data_fim'])) {
             $data_inicio = $this->converterData(filter_var($filtro['data_inicio'], FILTER_SANITIZE_NUMBER_INT));
             $data_fim = $this->converterData(filter_var($filtro['data_fim'], FILTER_SANITIZE_NUMBER_INT));
             $sqlTotalPendente->where->between('rec.data_vencimento', $data_inicio, $data_fim);
             $sqlTotal->where->between('rec.data_vencimento', $data_inicio, $data_fim);
             $sqlTotalRecebido->where->between('rec.data_vencimento', $data_inicio, $data_fim);
             $sqlPrincipal->where->between('rec.data_vencimento', $data_inicio, $data_fim);
         }
         //Retorna as receitas de acordo com o filtro data
         if (isset($filtro['data']) && !empty($filtro['data']) && !isset($filtro['data_inicio']) && !isset($filtro['data_fim'])) {
             $data = filter_var($filtro['data'], FILTER_SANITIZE_NUMBER_INT);
             $parteSql = ['DATE_FORMAT(data_vencimento, "%Y-%m") = DATE_FORMAT(?, "%Y-%m")' => $data];
             $sqlTotalPendente->where($parteSql);
             $sqlTotalRecebido->where($parteSql);
             $sqlTotal->where($parteSql);
             $sqlPrincipal->where($parteSql);
         }
     }
     //Se os dados forem paginados, entra aqui.
     if ($paginado) {
         $paginatorAdapter = new DbSelect($sqlPrincipal, $this->getAdapter());
         $paginado = new Paginator($paginatorAdapter);
         return $paginado;
     }
     $dados = $this->selectWith($sqlPrincipal);
     return $dados;
 }

 public function fetchAll($userId, $cond = null)
 {
     $select = new Select('user_track');
     if ($cond == null) {
         $select->join('packages', 'packages.package_id = user_track.usertrack_package_id', array('package_name'));
         $select->where(" usertrack_user_id = '{$userId}' and track_type = 'package' ");
     } else {
         $select->where(" usertrack_user_id = '{$userId}' and track_type = 'offer' and offer_status = '1' ");
     }
     $select->where(" usertrack_user_id = '{$userId}' ");
     $paginatorAdapter = new DbSelect($select, $this->adapter);
     $collection = new PackageCollection($paginatorAdapter);
     return $collection;
 }

 public function fetchAll($id, $paginate = true, $filter = array(), $orderBy = array())
 {
     if ($paginate) {
         $select = new Select('media');
         //$select->columns(array('*', new Expression("user_feature_setting.email as email_status")));
         $select->join('users', 'users.id = media.user_id', array('first_name', 'last_name'), 'inner');
         $select->join('lookup_status', 'lookup_status.status_id = media.status_id', array('status'), 'left');
         $select->where(array('user_id' => $id, 'media_type' => 1));
         /* Data filter code start here*/
         if (count($filter) > 0) {
             isset($filter['name']) && $filter['name'] != "" ? $select->where("CONCAT(users.first_name,' ',users.last_name) LIKE '%" . $filter['name'] . "%'") : "";
             isset($filter['title']) && $filter['title'] != "" ? $select->where("media.media_title LIKE '%" . $filter['title'] . "%'") : "";
             isset($filter['media_type']) && $filter['media_type'] != "" ? $select->where("media.media_type = " . $filter['media_type']) : "";
             if (isset($filter['from_date']) && $filter['from_date'] != "" && isset($filter['to_date']) && $filter['to_date'] != "") {
                 $select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') BETWEEN '" . $filter['from_date'] . "' AND '" . $filter['to_date'] . "'");
             } else {
                 if (isset($filter['from_date']) && !isset($filter['to_date']) && $filter['from_date'] != "") {
                     $select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') = '" . $filter['from_date'] . "'");
                 } else {
                     if (!isset($filter['from_date']) && isset($filter['to_date']) && $filter['to_date'] != "") {
                         $select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') = '" . $filter['to_date'] . "'");
                     }
                 }
             }
             isset($filter['status_id']) && $filter['status_id'] != "" ? $select->where("media.status_id = " . $filter['status_id']) : "";
         }
         /* Data filter code end here*/
         /* Data sorting code starts here */
         if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') {
             switch ($orderBy['sort_field']) {
                 case 'name':
                     $select->order('users.first_name ' . $orderBy['sort_order']);
                     break;
                 case 'title':
                     $select->order('media.media_title ' . $orderBy['sort_order']);
                     break;
                 case 'media_type':
                     $select->order('media.media_type ' . $orderBy['sort_order']);
                     break;
                 case 'date':
                     $select->order('media.created_date ' . $orderBy['sort_order']);
                     break;
                 case 'status':
                     $select->order('lookup_status.status ' . $orderBy['sort_order']);
                     break;
             }
         }
         /* Data sorting code ends here */
         //echo str_replace('"', '', $select->getSqlString()); exit;
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new UsersMedia());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         $paginator = new Paginator($paginatorAdapter);
         return $paginator;
     } else {
         $select = $this->tableGateway->getSql()->select();
         return $this->tableGateway->selectwith($select);
     }
 }

 /**
  * Sets the filter
  * @param \Zend\Db\Sql\Select $select
  * @param array $criteria
  * @return \Zend\Db\Sql\Select
  */
 private function setFilter($select, $criteria)
 {
     if (is_numeric($this->partIVA)) {
         $select->where('`codFisc` IN (SELECT `codFisc` FROM `person_to_company` WHERE `or_partIVA` = "' . addslashes($this->partIVA) . '")');
     }
     if ($this->codFisc != '') {
         $select->where('
         (
         `codFisc` IN (SELECT `or_codFisc` FROM `person_to_person` WHERE `codFisc` = "' . addslashes($this->codFisc) . '") OR
         `codFisc` IN (SELECT `codFisc` FROM `person_to_person` WHERE `or_codFisc` = "' . addslashes($this->codFisc) . '")
         )
         ');
     }
     return $select;
 }

 public function fetchPostsByFeeds($feeds, $offset = null, $limit = null)
 {
     $select = new Select(self::$_tableName);
     if (!is_null($offset)) {
         $select->offset($offset);
     }
     if (!is_null($limit)) {
         $select->limit($limit);
     }
     $select->join('directus_social_feeds', 'directus_social_feeds.id = directus_social_posts.feed', ['feed_type' => 'type'])->order('directus_social_posts.datetime DESC');
     $select->where->equalTo('directus_social_posts.status', 1)->equalTo('directus_social_feeds.status', 1);
     $FeedWhere = new Where();
     $SocialCache = new SocialCache();
     foreach ($feeds as $feed) {
         // Run scrape if due
         $SocialCache->scrapeFeedIfDue($feed['name'], $feed['type']);
         $FeedWhere->or->nest->equalTo('directus_social_feeds.name', $feed['name'])->equalTo('directus_social_feeds.type', $feed['type'])->unnest;
         $select->where($FeedWhere);
     }
     $socialPosts = $this->selectWith($select);
     $socialPosts = $socialPosts->toArray();
     // Unserialize cached feed entry API-responses
     foreach ($socialPosts as &$post) {
         $post['data'] = json_decode($post['data'], true);
     }
     return $socialPosts;
 }

 protected function quickSearch()
 {
     if ($this->getQuickSearchQuery()) {
         $where = $this->getQuickSearchQuery()->getRawState('where');
         $this->select->where($where);
     }
 }