PHP Select::join方法代码示例

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

 /**
  * @param array $fields
  * @param string|array $order Multi-column sort should be given as an array
  * @param bool $paginated Whether to return a paginator or a raw resultSet
  * @param int $limit
  */
 public function find($fields = null, $order = 'tickets.enteredDate desc', $paginated = false, $limit = null)
 {
     $select = new Select('tickets');
     $select->join(['c' => 'categories'], 'tickets.category_id=c.id', [], $select::JOIN_LEFT);
     if (count($fields)) {
         foreach ($fields as $key => $value) {
             if ($value) {
                 switch ($key) {
                     case 'reportedByPerson_id':
                         $select->join(['i' => 'issues'], 'tickets.id=i.ticket_id', [], $select::JOIN_LEFT);
                         $select->where(["i.{$key}" => $value]);
                         break;
                     case 'start_date':
                         $d = date(ActiveRecord::MYSQL_DATE_FORMAT, strtotime($value));
                         $select->where("tickets.enteredDate>='{$d}'");
                         break;
                     case 'end_date':
                         $d = date(ActiveRecord::MYSQL_DATE_FORMAT, strtotime($value));
                         $select->where("tickets.enteredDate<='{$d}'");
                         break;
                     case 'lastModified_before':
                         $d = date(ActiveRecord::MYSQL_DATE_FORMAT, strtotime($value));
                         $select->where("tickets.lastModified<='{$d}'");
                         break;
                     case 'lastModified_after':
                         $d = date(ActiveRecord::MYSQL_DATE_FORMAT, strtotime($value));
                         $select->where("tickets.lastModified>='{$d}'");
                         break;
                     case 'bbox':
                         $bbox = explode(',', $value);
                         if (count($bbox) == 4) {
                             $minLat = (double) $bbox[0];
                             $minLong = (double) $bbox[1];
                             $maxLat = (double) $bbox[2];
                             $maxLong = (double) $bbox[3];
                             $select->where('tickets.latitude is not null and tickets.longitude is not null');
                             $select->where("tickets.latitude  > {$minLat}");
                             $select->where("tickets.longitude > {$minLong}");
                             $select->where("tickets.latitude  < {$maxLat}");
                             $select->where("tickets.longitude < {$maxLong}");
                         }
                         break;
                     default:
                         $select->where(["tickets.{$key}" => $value]);
                 }
             }
         }
     }
     // Only get tickets for categories this user is allowed to see
     if (!isset($_SESSION['USER'])) {
         $select->where("c.displayPermissionLevel='anonymous'");
     } elseif ($_SESSION['USER']->getRole() != 'Staff' && $_SESSION['USER']->getRole() != 'Administrator') {
         $select->where("c.displayPermissionLevel in ('public','anonymous')");
     }
     return parent::performSelect($select, $order, $paginated, $limit);
 }

 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);
     }
 }

 public function fetchAll($paginate = true, $filter = array(), $orderBy = array())
 {
     if ($paginate) {
         $select = new Select('feedback');
         $select->columns(array('*', new Expression("service_provider_contact.first_name AS sp_first_name, service_provider_contact.last_name AS sp_last_name")));
         $select->join(array('service_provider_contact' => 'users'), 'service_provider_contact.id = feedback.users_id', array(), 'inner');
         $select->join('users', 'users.id = feedback.created_by', array('first_name', 'last_name'), 'inner');
         $select->join('service_provider_service', 'service_provider_service.id = feedback.service_id', array('duration'), 'inner');
         $select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'inner');
         $select->join('lookup_status', 'lookup_status.status_id = feedback.status_id', array('status'), 'left');
         /* Data filter code start here*/
         if (count($filter) > 0) {
             isset($filter['name']) && $filter['name'] != "" ? $select->where("CONCAT(service_provider_contact.first_name,' ',service_provider_contact.last_name) LIKE '%" . $filter['name'] . "%'") : "";
             if (isset($filter['from_date']) && $filter['from_date'] != "" && isset($filter['to_date']) && $filter['to_date'] != "") {
                 $select->where("DATE_FORMAT(feedback.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(feedback.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(feedback.created_date , '%Y-%m-%d') = '" . $filter['to_date'] . "'");
                     }
                 }
             }
             if (isset($filter['service_id']) && $filter['service_id'] != "") {
                 $select->where("feedback.service_id = " . $filter['service_id']);
             }
             isset($filter['status_id']) && $filter['status_id'] != "" ? $select->where("users.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('service_provider_contact.first_name ' . $orderBy['sort_order']);
                     break;
                 case 'service':
                     $select->order('service_category.category_name ' . $orderBy['sort_order']);
                     break;
                 case 'date':
                     $select->order('feedback.created_date ' . $orderBy['sort_order']);
                     break;
             }
         }
         /* Data sorting code ends here */
         //echo str_replace('"','',$select->getSqlString()); exit;
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new Feedbacks());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         $paginator = new Paginator($paginatorAdapter);
         return $paginator;
     }
     return $this->tableGateway->select();
 }

 public function get($id, $user)
 {
     $select = new Select($this->tableGateway->getTable());
     $select->columns(array("id", "register_date", "guide", "observations"));
     $select->join('customers', "customers.id = " . $this->tableGateway->getTable() . ".client", array('client_first_name' => 'first_name', 'client_last_name' => 'first_name'), 'inner');
     $select->join(array('sellers' => 'customers'), "sellers.id = " . $this->tableGateway->getTable() . ".seller", array('seller_first_name' => 'first_name', 'seller_last_name' => 'last_name', 'seller_company' => 'company'), 'inner');
     $select->join('payments_methods', "payments_methods.id = " . $this->tableGateway->getTable() . ".payment_method", array('payment_method_name' => 'name'), 'inner');
     $select->where(array($this->tableGateway->getTable() . ".id" => $id, $this->tableGateway->getTable() . ".user" => $user));
     $rowset = $this->tableGateway->selectWith($select);
     $row = $rowset->current();
     return $row;
 }

 public function searchSerial($serial)
 {
     $select = new Select($this->tableGateway->getTable());
     $select->columns(array());
     $select->join("products", "products.id = " . $this->tableGateway->getTable() . ".product", array('id' => 'id'));
     $select->join("products_receive_inventory", "products_receive_inventory.details_receive_inventory = " . $this->tableGateway->getTable() . ".id", array('serial' => 'serial'));
     $select->where(array("products_receive_inventory.status" => 0));
     $select->where->like("products_receive_inventory.serial", "%" . $serial . "%");
     error_log($select->getSqlString());
     $result = $this->tableGateway->selectWith($select);
     return $result;
 }

 public function fetchCartOnItemId($item_id)
 {
     $select = new Select();
     $select->from('items');
     $select->join('brands', "brands.b_id = items.item_brand", array('b_name'), 'left');
     $select->join('categories', "categories.cat_id = items.item_category", array('cat_name'), 'left');
     $select->join('sub_categories', "sub_categories.subcat_id = items.item_sub_category", array('subcat_name'), 'left');
     $select->join('images', "images.img_item_id = items.item_id", array('img_link'), 'left');
     $select->where("items.item_id = {$item_id}");
     $resultSet = $this->cartTableGateway->selectWith($select);
     return $resultSet;
 }

 public function getByProduct($product)
 {
     $select = new Select($this->tableGateway->getTable());
     $select->columns(array("id" => "id", "measure_value" => "measure_value", "image" => "image"));
     $select->join('products_measures', 'products_measures.measure = ' . $this->tableGateway->getTable() . '.id', array(), 'inner');
     $select->join('measures_types', 'measures_types.id = ' . $this->tableGateway->getTable() . '.measure_type', array('mt_name' => 'name'), $select::JOIN_LEFT);
     $select->join('specifications', $this->tableGateway->getTable() . ".specification = specifications.id", array('s_name' => 'name', 'image' => 'image', 'specification' => 'id'), 'inner');
     $select->join('categories_specifications', "categories_specifications.specification = specifications.id", array(), 'inner');
     $select->order('categories_specifications.order');
     $select->where(array("products_measures.product" => $product));
     $rows = $this->tableGateway->selectWith($select);
     return $rows ? $rows : false;
 }

 public function getOrdersList()
 {
     $sql = new Sql($this->tableGateway->getAdapter());
     $db = $this->tableGateway->getAdapter()->getDriver()->getConnection()->getResource();
     $select = new Select(self::TABLE_NAME);
     $columns = array('orderId' => 'aufri_orders_id', 'productId' => 'aufri_orders_product_id_fk', 'shopkeeperId' => 'aufri_orders_shopkeeper_id_fk', 'userId' => 'aufri_orders_user_id_fk', 'orderDate' => 'aufri_orders_date', 'couponId' => 'aufri_orders_coupon_id_fk', 'orderStatus' => 'aufri_orders_status');
     $select->columns($columns);
     $select->join(array('productTable' => ProductsTable::TABLE_NAME), 'productTable.aufri_products_id=aufri_orders.aufri_orders_product_id_fk', array('productName' => 'aufri_products_name', 'productRent' => 'aufri_products_rent', 'productDeposit' => 'aufri_products_security'), Select::JOIN_LEFT);
     $select->join(array('shopkeeperTable' => ShopkeeperTable::TABLE_NAME), 'shopkeeperTable.aufri_shopkeeper_id=aufri_orders.aufri_orders_shopkeeper_id_fk', array('shopkeeperName' => 'aufri_shopkeeper_name'), Select::JOIN_LEFT);
     $select->join(array('couponTable' => CouponTable::TABLE_NAME), 'couponTable.aufri_coupons_id=aufri_orders.aufri_orders_coupon_id_fk', array('couponName' => 'aufri_coupons_name', 'couponCode' => 'aufri_coupons_code', 'couponDiscount' => 'aufri_coupons_discount'), Select::JOIN_LEFT);
     $select->join(array('userTable' => UserTable::TABLE_NAME), 'userTable.aufri_users_id=aufri_orders.aufri_orders_user_id_fk', array('userName' => 'aufri_users_name', 'userPhone' => 'aufri_users_phone_no', 'userAddressId' => 'aufri_users_address_id_fk'), Select::JOIN_LEFT);
     $select->join(array('addressTable' => AddressTable::TABLE_NAME), 'addressTable.aufri_address_id=userTable.aufri_users_address_id_fk', array('addressId' => 'aufri_address_id', 'userAddress' => 'aufri_address_address'), Select::JOIN_LEFT);
     return $this->getSqlContent($db, $sql, $select);
 }

 public function fetchAll($userType = false)
 {
     $select = new Select($this->tableGateway->getTable());
     $select->join('cities', "cities.id = " . $this->tableGateway->getTable() . ".city", array('city_name' => 'name'), 'inner');
     $select->join('customers_classifications', "customers_classifications.customer = " . $this->tableGateway->getTable() . ".id", array(), 'inner');
     $select->join('classifications', "classifications.id = customers_classifications.classification", array(), 'inner');
     $select->join('user_types', "user_types.id = classifications.user_type", array(), 'inner');
     if ($userType) {
         $select->where(array("user_types.id" => $userType));
     }
     $resultSet = $this->tableGateway->selectWith($select);
     $resultSet->buffer();
     return $resultSet;
 }

 public function fetchAllReplies($id)
 {
     $select = new Select('messages');
     $select->columns(array('*', new Expression("from_user.user_name as from_user, to_user.user_name as to_user")));
     $select->join(array('from_user' => 'users'), 'from_user.id = messages.from_user_id', array(), 'left');
     $select->join(array('to_user' => 'users'), 'to_user.id = messages.to_user_id', array(), 'left');
     $select->where(array('topLevel_id' => $id));
     $select->order('created_date desc');
     $rowset = $this->tableGateway->selectwith($select);
     if (!$rowset) {
         return false;
     }
     return $rowset;
 }

 /**
  * Returns all accepted and declined applications
  */
 public function getProcessedApplications($paginated = false)
 {
     if ($paginated) {
         $select = new Select('application');
         $select->join(['a' => 'account'], 'application.processed_by = a.id', ['account_name' => 'name'])->where('processed > 0')->order('date_applied DESC');
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new Application());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         return new Paginator($paginatorAdapter);
     }
     return $this->tableGateway->select(function (Select $select) {
         $select->join(['a' => 'account'], 'application.processed_by = a.id', ['account_name' => 'name'])->where('processed > 0')->order('date_applied DESC');
     });
 }

 /**
  * Returns all Media in db
  *
  * @return \Zend\Db\ResultSet\ResultSet
  */
 public function fetchAll($paginated = false)
 {
     if ($paginated) {
         $select = new Select('media');
         $select->join(['a' => 'account'], 'media.account_id = a.id', ['name'])->order('date_posted DESC');
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new Media());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         return new Paginator($paginatorAdapter);
     }
     return $this->tableGateway->select(function (Select $select) {
         $select->join(['a' => 'account'], 'media.account_id = a.id', ['name'])->order('date_posted DESC');
     });
 }

 /**
  * Returns all News in db
  *
  * @return \Zend\Db\ResultSet\ResultSet
  */
 public function fetchAll($paginated = false)
 {
     if ($paginated) {
         $select = new Select('news');
         $select->join(['a' => 'account'], 'news.account_id = a.id', ['name'])->join(['c' => 'news_category'], 'news.category_id = c.id', ['cname' => 'name'])->join(['m' => 'comment'], 'news.id = m.news_id', ['cid' => 'id'], $select::JOIN_LEFT)->order('news.date_posted DESC')->columns(['id', 'title', 'content', 'date_posted', 'comment_count' => new Expression('COUNT(m.id)')])->group('news.id');
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new News());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         return new Paginator($paginatorAdapter);
     }
     return $this->tableGateway->select(function (Select $select) {
         $select->join(['a' => 'account'], 'news.account_id = a.id', ['name'])->join(['c' => 'news_category'], 'news.category_id = c.id', ['category'])->order('date_posted DESC');
     });
 }