PHP DB::select_array方法代码示例

 public function action_index()
 {
     //车型分类
     $cartype = ORM::factory('car_kind')->order_by("displayorder", 'asc')->get_all();
     //租车类型
     $carattr = ORM::factory('car_attr')->where("pid=0 and isopen=1")->order_by("displayorder", 'asc')->get_all();
     if (!empty($carattr)) {
         foreach ($carattr as $key => $value) {
             $carattr[$key]['nextlist'] = ORM::factory('car_attr')->where("pid=" . $value['id'] . " and isopen=1")->order_by("displayorder", 'asc')->get_all();
         }
     }
     //租车价格
     $carpricelist = ORM::factory('car_pricelist')->order_by("id", 'asc')->get_all();
     //推荐租车信息
     //$list = ORM::factory('car')->select(array('id','id'))->where("ishidden=0 and carprice>0")->order_by("displayorder",'asc')->limit("0,4")->get_all();
     $list = DB::select_array(array('id', 'title', 'litpic', 'price'))->from('car')->where("ishidden=0 and price>0")->order_by("displayorder", 'asc')->limit("0,4")->execute()->as_array();
     if (empty($list)) {
         $sql = "SELECT a.id,a.title,a.litpic,min(b.adultprice) as carprice FROM sline_car a LEFT JOIN sline_car_suit_price b ON (b.carid = a.id and b.adultprice!=0) WHERE a.ishidden=0 GROUP BY a.id ORDER BY a.displayorder ASC limit 0,4 ";
         $query = DB::query(Database::SELECT, $sql);
         $list = $query->execute()->as_array();
     }
     foreach ($list as $key => $value) {
         if (empty($value['litpic'])) {
             $list[$key]['litpic'] = Common::getDefaultImage();
         }
     }
     //数据压入
     $this->assign('cartype', $cartype);
     $this->assign('carattr', $carattr);
     $this->assign('carpricelist', $carpricelist);
     $this->assign('list', $list);
     $this->display('cars/index');
 }

 /**
  * News detail
  * 
  * @access  public
  * @return  Response
  */
 public function get_detail($slug = null)
 {
     $cols = array('news.id', 'news.news_category_id', 'news.title', 'news.body', 'news.format', 'news.published_at', 'news.slug', array('news_category.name', 'news_category_name'), array('news_category.label', 'news_category_label'));
     $query = \DB::select_array($cols)->from('news')->join('news_category', 'LEFT')->on('news_category.id', '=', 'news.news_category_id')->where('slug', $slug)->and_where('is_published', 1);
     if (!($response = $query->execute()->current())) {
         throw new \HttpNotFoundException();
     }
     $response['body'] = convert_body_by_format($response['body'], $response['format']);
     unset($response['format']);
     $cols = array('news_image.name', array('file.name', 'file_name'), array('file.type', 'file_type'));
     $query = \DB::select_array($cols)->from('news_image')->join('file', 'LEFT')->on('file.name', '=', 'news_image.file_name')->where('news_id', $response['id']);
     $response['images'] = self::add_file_options($query->execute()->as_array(), 'img');
     $cols = array('news_file.name', array('file.name', 'file_name'), array('file.original_filename', 'file_original_filename'), array('file.type', 'file_type'));
     $query = \DB::select_array($cols)->from('news_file')->join('file', 'LEFT')->on('file.name', '=', 'news_file.file_name')->where('news_id', $response['id']);
     $response['files'] = self::add_file_options($query->execute()->as_array(), 'file');
     $cols = array('uri', 'label');
     $query = \DB::select_array($cols)->from('news_link')->where('news_id', $response['id']);
     $response['links'] = $query->execute()->as_array();
     if (\Config::get('news.tags.isEnabled')) {
         $cols = array('tag.name');
         $query = \DB::select_array($cols)->from('news_tag')->join('tag', 'LEFT')->on('tag.id', '=', 'news_tag.tag_id')->where('news_id', $response['id']);
         $response['tags'] = $query->execute()->as_array();
     }
     return $this->response($response);
 }

 /**
  * Load role
  *
  * @param  integer|string  $id
  */
 public function __construct($id = null)
 {
     parent::__construct();
     if ($id !== null) {
         $this->load(DB::select_array($this->fields())->where(is_numeric($id) ? 'id' : 'name', '=', $id));
     }
 }

	protected function get_filter_type() {
		$types = DB::select_array(array('type'))
			->distinct(TRUE)
			->from(Kohana::config('dblog.table'))
			->execute()
			->as_array('type', 'type');
		return Arr::merge($types, array('' => __('any')));
	}

 /**
  * Action: search
  */
 public function action_search()
 {
     $this->data['users'] = array();
     // Search term
     $term = trim(Arr::get($_REQUEST, 'q', ''));
     // Search fields
     $search = explode(':', Arr::get($_REQUEST, 'search', 'username'));
     // Result limit
     $limit = (int) Arr::get($_REQUEST, 'limit', 25);
     // Result order
     $order = Arr::get($_REQUEST, 'order', 'username.asc');
     // Result fields
     $field = explode(':', Arr::get($_REQUEST, 'field', 'id:username'));
     // Term must be at least 2 characters
     if (strlen($term) >= 2) {
         // 500 events max
         $limit = min($limit, 500);
         // Get order
         $orders = $this->_prepare_order($order, self::$_orderable);
         $orders = empty($orders) ? array('username' => 'asc') : $orders;
         // Get fields
         $fields = empty($field) ? self::$_fields : array_intersect($field, self::$_fields);
         $fields = empty($fields) ? array('id', 'username') : $fields;
         // Get search
         $searches = empty($search) ? self::$_searchable : array_intersect($search, self::$_searchable);
         $searches = empty($searches) ? array('username') : $searches;
         // Build query
         $user = new Model_User();
         $users = DB::select_array($user->fields());
         // Find friends first
         if ($user_id = Arr::get($_REQUEST, 'user', 0)) {
             $users->join('friends', 'LEFT')->on('users.id', '=', 'friends.friend_id')->on('friends.user_id', '=', DB::expr((int) $user_id))->order_by('friends.created', 'ASC');
         }
         foreach ($orders as $column => $direction) {
             $users->order_by($column, $direction);
         }
         $users->where_open();
         foreach ($searches as $search) {
             $search_term = $term;
             switch ($search) {
                 case 'city':
                     $search = 'city_name';
                     break;
                 case 'username':
                     $search = 'username_clean';
                     $search_term = Text::clean($term);
                     break;
             }
             $users->or_where($search, 'ILIKE', '%' . $search_term . '%');
         }
         $users->where_close();
         // Build data
         foreach ($user->load($users, $limit) as $user) {
             $this->data['users'][] = $this->_prepare_user($user, $fields);
         }
     }
 }

 /**
  * Find Newsfeed items.
  *
  * @static
  * @param   integer  $limit
  * @param   array    $users  User ids
  * @return  Database_Result
  */
 public static function find_items($limit = 20, array $users = null)
 {
     $newsfeeditem = new Model_NewsfeedItem();
     $query = DB::select_array($newsfeeditem->fields())->order_by('id', 'DESC');
     if (is_array($users)) {
         $query = $query->where('user_id', 'IN', $users);
     }
     return $newsfeeditem->load($query, $limit);
 }

 /**
  * メールアドレス、パスワードの整合性テスト
  * 
  * @dataProvider login_user_data_provider
  */
 public function test_validate_user($input, $expected)
 {
     $test = Auth::instance()->validate_user($input['email'], $input['password']);
     if ($expected['id']) {
         $expected = \DB::select_array(\Config::get('web2auth.table_columns', array('*')))->where('id', '=', $expected['id'])->from(\Config::get('web2auth.table_name'))->execute(\Config::get('web2auth.db_connection'))->current();
     } else {
         $expected = false;
     }
     $this->assertEquals($expected, $test);
 }

 /**
  * Find Notifications for user.
  *
  * @static
  * @param   Model_User  $target
  * @return  Model_Notification[]
  */
 public function find_by_target(Model_User $target)
 {
     // User notifications
     $query = DB::select_array($this->fields())->where('target_id', '=', $target->id)->order_by('id', 'DESC');
     // Admin notifications
     if ($target->has_role('admin', 'photo moderator')) {
         $query = $query->or_where_open()->where('class', '=', Notification_Galleries::CLASS_GALLERIES)->and_where('type', '=', Notification_Galleries::TYPE_IMAGE_REPORT)->or_where_close();
     }
     return $this->load($query, 0);
 }

 public function force_login($user_id = '')
 {
     if (empty($user_id)) {
         return false;
     }
     $this->user = \DB::select_array(\Config::get('complexauth.table_columns', array('*')))->from(\Config::get('complexauth.table_name'))->where('id', '=', $user_id)->execute(\Config::get('complexauth.db_connection'))->current();
     if ($this->user == false) {
         $this->user = \Config::get('complexauth.guest_login', true) ? static::$guest_login : false;
         \Session::delete(\Config::get('complexauth.username_post_key'));
         return false;
     }
     \Session::set(\Config::get('complexauth.username_post_key'), $this->user[\Config::get('complexauth.username_post_key')]);
     return true;
 }

 public static function run()
 {
     try {
         $users = \DB::select_array(['id', 'username'])->from('users')->execute();
         \DB::start_transaction();
         foreach ($users as $user) {
             \DB::update('auctions')->value('won_user', $user['id'])->where('won_user', '=', $user['username'])->execute();
         }
         \DB::commit_transaction();
         \DBUtil::modify_fields('auctions', ['won_user' => ['constraint' => 11, 'type' => 'int', 'name' => 'user_id']]);
     } catch (Exception $e) {
         \DB::rollback_transaction();
     }
 }

 /**
  * Find Newsfeed items.
  *
  * @static
  * @param   integer  $limit
  * @param   array    $users  User ids
  * @return  Model_NewsfeedItem[]
  */
 public static function find_items($limit = 20, array $users = null)
 {
     $newsfeeditem = new Model_NewsfeedItem();
     $query = DB::select_array($newsfeeditem->fields())->order_by('stamp', 'DESC');
     if (is_array($users)) {
         $query = $query->where('user_id', 'IN', $users);
         // Include friend events
         $friend_ids = array();
         foreach ($users as $user_id) {
             $friend_ids[] = json_encode(array('friend_id' => $user_id));
         }
         $query = $query->or_where_open()->where('class', '=', 'user')->and_where('type', '=', 'friend')->and_where('data', 'IN', $friend_ids)->or_where_close();
     }
     return $newsfeeditem->load($query, $limit);
 }

 public function action_reset_password()
 {
     if (\Input::post()) {
         $to = trim(\Input::post('email'));
         $user = \DB::select_array(\Config::get('simpleauth.table_columns', array('*')))->where('email', '=', $to)->from(\Config::get('simpleauth.table_name'))->as_object()->execute(\Config::get('simpleauth.db_connection'))->current();
         $data['password'] = \Auth::reset_password($user->username);
         $val = \Validation::forge();
         $val->add_field('email', 'email', 'required|valid_email');
         if ($val->run()) {
             // Create an instance
             $email = \Email::forge();
             // Set the from address
             $email->from('jobs@esl.ch', 'Les RHs');
             // Set the to address
             $email->to($to);
             // Set a subject
             $email->subject('New password');
             // And set the body.
             $email->html_body(\View::forge('email/template.twig', $data));
             try {
                 $email->send();
             } catch (\EmailValidationFailedException $e) {
                 // The validation failed
             } catch (\EmailSendingFailedException $e) {
                 // The driver could not send the email
             }
             \Session::set_flash('success', 'A new password has been sent');
             \Response::redirect('/');
         } else {
             // repopulate the email field and give some error text back to the view.
             $data['email'] = $to;
             \Session::set_flash('error', $val->error());
         }
     }
     $data['actions'] = ['back' => ['label' => 'Back', 'url' => 'auth']];
     $this->template->title = "Reset password";
     $this->template->content = \View::forge('auth/password.twig', $data);
 }

 /**
  * Find all groups
  *
  * @return  Model_Forum_Group[]
  */
 public function find_all()
 {
     return $this->load(DB::select_array($this->fields())->where('status', '=', self::STATUS_NORMAL)->order_by('sort', 'ASC'), null);
 }

 /**
  * Get new notes
  *
  * @param   Model_User  $user
  * @return  Database_Result
  */
 public function find_new_notes(Model_User $user)
 {
     return $this->load(DB::select_array($this->fields())->where('user_id', '=', $user->id)->and_where('new_note', '>', 0), null);
 }

 /**
  * Checks if given field(s) in a given table exists.
  *
  * @param   string         $table    Table name
  * @param   string|array   $columns  columns to check
  * @return  bool
  */
 public static function field_exists($table, $columns, $db = null)
 {
     if (!is_array($columns)) {
         $columns = array($columns);
     }
     try {
         \DB::select_array($columns)->from($table)->limit(1)->execute($db ? $db : static::$connection);
         return true;
     } catch (\Database_Exception $e) {
         return false;
     }
 }