PHP SelectQuery::filter方法代码示例

 public static function search(TableCtl $controller, $term, $filter = false)
 {
     $object = call_user_func(array(get_class($controller), 'getObject'));
     if (!$object) {
         return false;
     }
     $terms = preg_split('/[ ,]/', $term);
     if (!count($terms)) {
         return false;
     }
     //Check for results containing the word
     $search = array();
     foreach ($terms as $oneTerm) {
         $search[] = '`word` LIKE CONCAT("%", ?, "%")';
     }
     //Check for results with the exact word
     $search[] = '`word` IN (' . implode(', ', array_fill(0, count($terms), '?')) . ')';
     $search = '(' . implode(') OR (', $search) . ')';
     $params = array_merge(array($object->getSource()), $terms, $terms);
     $query = new SelectQuery(get_called_class());
     $query->field('DISTINCT `' . $object->getMeta('table') . '`.*')->leftJoin(get_class($controller), '`' . $object->getMeta('table') . '`.`' . $object->getMeta('id_field') . '` = `table_id`')->filter('`table` = ?')->filter($search)->order('`count` DESC, `sequence`');
     if ($filter) {
         if (is_array($filter)) {
             foreach ($filter as $one_fil) {
                 $query->filter($one_fil);
             }
         } else {
             $query->filter($filter);
         }
     }
     $result = $query->fetchAll($params);
     return $result;
 }

 public static function check()
 {
     if (!empty($_COOKIE['remembered'])) {
         $query = new SelectQuery('PersistUser');
         $persist = $query->filter('MD5(CONCAT(`id`, `user_id`, `random`)) = :hash')->fetchAssoc(array(':hash' => $_COOKIE['remembered']));
         if ($persist) {
             //Get User
             $User = self::getObject('BackendUser');
             if (!$User instanceof DBObject) {
                 return false;
             }
             $query = BackendUser::getQuery();
             $query->filter('`backend_users`.`id` = :id');
             $params = array(':id' => $persist['user_id']);
             $User->read(array('query' => $query, 'parameters' => $params, 'mode' => 'object'));
             if ($User->object) {
                 $_SESSION['BackendUser'] = $User->object;
                 //Remove, and reremember
                 if (self::remember($User->object)) {
                     $query = new DeleteQuery('PersistUser');
                     $query->filter('`id` = :id')->limit(1);
                     $query->execute(array(':id' => $persist['id']));
                 } else {
                     Backend::addError('Could not reremember');
                 }
                 return $User->object;
             } else {
                 //Backend::addError('Invalid remembered user');
             }
         }
     }
     return false;
 }

 public function action_filter($pageId = 1)
 {
     $query = new SelectQuery('BackendRequest');
     $query->setFields(array('user_id', 'ip', 'user_agent', 'mode', 'request', 'query', 'COUNT(id) AS `occured`', 'MAX(`added`) AS `last_occured`'));
     $query->setGroup(array('user_id', 'ip', 'user_agent', 'mode', 'request', 'query'));
     $params = $queryFilter = array();
     $parameters = Controller::getVar('params');
     $sort = Controller::getVar('sort');
     if (!empty($parameters['userId'])) {
         $queryFilter[] = 'user_id = :userId';
         $params[':userId'] = $parameters['userId'];
     }
     if (!empty($parameters['query'])) {
         $queryFilter[] = "query LIKE('%{$parameters['query']}%')";
     }
     if (!empty($parameters['ip'])) {
         $queryFilter[] = "ip LIKE('%{$parameters['ip']}%')";
     }
     if (!empty($parameters['user_agent'])) {
         $queryFilter[] = "user_agent LIKE('%{$parameters['user_agent']}%')";
     }
     $query->filter($queryFilter);
     $count = 10;
     if (!empty($sort['field'])) {
         $query->setOrder(array($sort['field'] . '  ' . $sort['order']));
     }
     if ($pageId == 1) {
         $start = 0;
     } elseif ($pageId == 0) {
         $start = false;
         $count = false;
     } else {
         $start = floor(($pageId - 1) * $count);
     }
     $pager = array();
     if ($start === 'all') {
         $limit = 'all';
     } else {
         if ($start || $count) {
             $limit = "{$start}, {$count}";
         } else {
             $limit = false;
         }
     }
     $query->limit($limit);
     $items = $query->fetchAll($params);
     $totalItems = $query->getCount($params);
     $pager = '';
     if ($start || $count) {
         $pager = array('currentPage' => $pageId, 'itemCount' => count($items), 'itemTotal' => $totalItems, 'totalPages' => round(($totalItems - 1) / $count, 0));
     }
     $retArray['pager'] = $pager;
     $retArray['data'] = $items;
     $retArray['params'] = $parameters;
     $retArray['sort'] = $sort;
     return $retArray;
 }

    public function action_create()
    {
        if (is_post()) {
            $parameters = get_previous_parameters();
            $object = new CommentObj();
            $object = $object->fromRequest();
            $object['foreign_id'] = empty($object['foreign_id']) ? reset($parameters) : $object['foreign_id'];
            $object['foreign_table'] = empty($object['foreign_table']) ? table_name(get_previous_area()) : $object['foreign_table'];
            //If we don't have a logged in user, create a dummy account
            if (!BackendUser::check()) {
                $query = new SelectQuery('BackendUser');
                $query->filter('`email` = :email');
                if ($old_user = Controller::getVar('user')) {
                    $existing_user = $query->fetchAssoc(array(':email' => $old_user['email']));
                }
                switch (true) {
                    case $existing_user && $existing_user['confirmed'] && $existing_user['active']:
                        //Attribute quote to user? Seems risque, actually, if I know a user's email address, I can just attribute to him. Auth first
                        Backend::addError('Comment not added. Please login first');
                        return false;
                        break;
                    case $existing_user && !$existing_user['confirmed'] && $existing_user['active']:
                        //Unregistered user commented before
                        $object['user_id'] = $existing_user['id'];
                        break;
                    default:
                    case !$existing_user:
                        $user_data = array('name' => $old_user['name'], 'surname' => '', 'email' => $old_user['email'], 'website' => $old_user['website'], 'username' => $old_user['email'], 'password' => get_random(), 'confirmed' => 0, 'active' => 1);
                        $user = self::getObject('BackendUser');
                        if ($user->create($user_data)) {
                            $object['user_id'] = $user->array['id'];
                            $url = SITE_LINK . '/?q=backend_user/confirm/' . $user->array['salt'];
                            $app_name = ConfigValue::get('Title');
                            $message = <<<END
Hi {$user->array['name']}!

Thank you for your comment on {$app_name}. An account has automatically been created for you. To activate it, please click on the following link:

{$url}

Please note that you don't need to do this for your comments to show, but this account will be deleted if it isn't confirmed in a weeks time.

Regards
END;
                            send_email($user->array['email'], 'Thank you for your comment.', $message);
                        } else {
                            Backend::addError('Could not create user to add Comment');
                            return false;
                        }
                        break;
                }
            }
            $object = array_filter($object, create_function('$var', 'return !is_null($var);'));
            Controller::setVar('obj', $object);
        }
        return parent::action_create();
    }

 public static function get($hook, $type = 'pre')
 {
     if (!BACKEND_WITH_DATABASE) {
         return false;
     }
     $params = array(':type' => $type, ':hook' => $hook);
     $query = new SelectQuery('Hook');
     $query->leftJoin('Component', array('`hooks`.`class` = `components`.`name`'))->filter('`hooks`.`hook` = :hook')->filter('`hooks`.`type` = :type')->filter('`hooks`.`active` = 1')->filter('`components`.`active` = 1');
     if (Controller::$area) {
         $query->filter('`global` = 1 OR `class` = :area');
         $params[':area'] = Controller::$area;
     }
     if (Controller::$view && Controller::$view->mode) {
         $query->filter('`mode` IN (:mode, \'*\')');
         $params[':mode'] = Controller::$view->mode;
     }
     $query->order('`sequence`');
     return $query->fetchAll($params);
 }

 public function action_display($id)
 {
     $query = new SelectQuery('ContentRevision');
     $query->filter('`content_id` = :id')->order('`added` DESC');
     $revisions = $query->fetchAll(array(':id' => $id));
     $content = new ContentObj($id);
     if ($content->object) {
         $content->object->revisions = $revisions;
     } else {
         $content = false;
     }
     return $content;
 }

 public static function get($id, array $options = array())
 {
     $tag = Tag::retrieve($id, 'dbobject');
     if (!$tag || !$tag->array) {
         return false;
     }
     $links = self::getObject($tag->array['foreign_table']);
     list($query, $params) = $links->getSelectSQL();
     if (!$query instanceof SelectQuery) {
         return false;
     }
     $query_links = new SelectQuery('TagLink');
     $query_links->field('`foreign_id`')->filter('`tag_id` = :tag_id');
     if (array_key_exists('active', $links->getMeta('fields'))) {
         $query_links->filter('`active` = 1');
     }
     $order = $query_links->getOrder();
     if (empty($order) && array_key_exists('added', $links->getMeta('fields'))) {
         $query_links->order('`added` DESC');
     }
     $start = array_key_exists('start', $options) ? $options['start'] : 0;
     $count = array_key_exists('count', $options) ? $options['count'] : Value::get('list_length', 5);
     $query->field(':tag_id AS `tag_id`')->filter('`' . $links->getMeta('id_field') . '` IN (' . $query_links . ')')->limit("{$start}, {$count}");
     $params = array(':tag_id' => $tag->getMeta('id'));
     $links->load(array('mode' => 'list', 'query' => $query, 'parameters' => $params));
     $tag->array['list'] = $links->list;
     $tag->array['list_count'] = $links->list_count;
     return $tag;
 }

 private static function permissionHolders($action = '*', $subject = '*', $subject_id = 0)
 {
     $result = false;
     $query = new SelectQuery('Permission');
     $params = array();
     if ($action != '*') {
         $query->filter("(`action` = :action OR `action` = '*')");
         $params[':action'] = $action;
     }
     if ($subject != '*') {
         $query->filter("(`subject` = :subject OR `subject` = '*')");
         $params[':subject'] = $subject;
     }
     if ($subject_id != '0') {
         $query->filter("(`subject_id` = :subject_id OR `subject_id` = 0)");
         $params[':subject_id'] = $subject_id;
     }
     $result = $query->fetchAll($params);
     return $result;
 }

 public static function getSitemap()
 {
     $query = new SelectQuery('Content');
     $query->filter('`active` = 1');
     $list = $query->fetchAll();
     return array('list' => $list, 'options' => array());
 }

 /**
  * Return all users within a specific role
  */
 public static function withRole($roles)
 {
     if (!is_array($roles)) {
         $roles = array($roles);
     }
     $roleObj = new RoleObj();
     $query = new SelectQuery('Role');
     $query->filter('`name` IN (' . implode(', ', array_pad(array(), count($roles), '?')) . ')');
     $roleObj->read(array('query' => $query, 'parameters' => $roles));
     if (!$roleObj->list) {
         return false;
     }
     $roleIds = array_flatten($roleObj->list, null, 'id');
     $query = self::getQuery();
     $query->distinct()->field('`' . self::getTable() . '`.*')->leftJoin('Assignment', array('`access_type` = "users"', '`access_id` = `' . self::getTable() . '`.`id`'))->filter('`role_id` IN (' . implode(', ', array_pad(array(), count($roleIds), '?')) . ')');
     return $query->fetchAll($roleIds);
 }

 public function get_permissions($component = false)
 {
     $toret = new stdClass();
     //Base Permissions
     $parameters = array();
     $query = new SelectQuery('Permission');
     $query->distinct()->field(array('action', 'subject'))->filter('`active` = 1')->filter('`subject_id` = 0')->group('`subject`, `action` WITH ROLLUP');
     if ($component) {
         $query->filter('`subject` = :component');
         $parameters[':component'] = class_for_url($component);
     }
     $toret->base_perms = $query->fetchAll($parameters);
     //Roles
     $query = new SelectQuery('Role');
     $query->filter('`active` = 1');
     $toret->roles = $query->fetchAll();
     //Activated Permissions
     $parameters = array();
     $query = new SelectQuery('Permission', array('fields' => "CONCAT(`subject`, '::', `action`), GROUP_CONCAT(DISTINCT `role` ORDER BY `role`) AS `roles`"));
     $query->filter('`active` = 1')->filter('`subject_id` = 0')->filter("`role` != 'nobody'")->group('`subject`, `action`');
     if ($component) {
         $query->filter('`subject` = :component');
         $parameters[':component'] = class_for_url($component);
     }
     $permissions = $query->fetchAll($parameters, array('with_key' => 1));
     $toret->permissions = array();
     foreach ($permissions as $key => $value) {
         $toret->permissions[$key] = explode(',', current($value));
     }
     return $toret;
 }

 public function getSelectSQL($options = array())
 {
     //Check the DB Connection
     $this->error_msg = false;
     if (!$this->checkConnection()) {
         if (class_exists('BackendError', false)) {
             BackendError::add(get_class($this) . ': DB Connection Error', 'getSelectSQL');
         }
         $this->error_msg = 'DB Connection Error';
         return false;
     }
     $mode = array_key_exists('mode', $options) ? $options['mode'] : 'list';
     $query = new SelectQuery($this, array('connection' => $this->db));
     //Fields
     $fields = array_key_exists('fields', $options) ? $options['fields'] : array();
     if (empty($fields)) {
         $query->field("`{$this->meta['table']}`.*");
     } else {
         $query->field($fields);
     }
     //Joins
     $joins = array_key_exists('joins', $options) ? $options['joins'] : array();
     if (count($joins)) {
         foreach ($joins as $join) {
             if (is_array($join)) {
                 $query->joinArray($join);
             }
         }
     }
     $q_params = array();
     if (!empty($options['conditions'])) {
         $query->filter($options['conditions']);
     }
     //Mode specific
     $limit = false;
     switch ($mode) {
         case 'object':
         case 'array':
         case 'full_object':
             if (!empty($this->meta['id'])) {
                 $query->filter("`{$this->meta['table']}`.`{$this->meta['id_field']}` = :{$this->meta['table']}_id");
                 $q_params[":{$this->meta['table']}_id"] = $this->meta['id'];
             } else {
                 $query->limit(empty($limit) ? 1 : $limit);
             }
             break;
         case 'list':
             if (array_key_exists('limit', $options) && $options['limit'] != 'all') {
                 $query->limit($options['limit']);
             }
             break;
     }
     //Parameters
     if (array_key_exists('parameters', $options)) {
         if (is_array($options['parameters'])) {
             $q_params = array_merge($q_params, $options['parameters']);
         } else {
             $q_params[] = $options['parameters'];
         }
     } else {
         if (!empty($this->meta['parameters'])) {
             if (is_array($this->meta['parameters'])) {
                 $q_params = array_merge($q_params, $this->meta['parameters']);
             } else {
                 $q_params[] = $parameters;
             }
         }
     }
     //Filters
     if (array_key_exists('filters', $options)) {
         $query->filter($options['filters']);
     } else {
         if (!empty($this->meta['filters'])) {
             $query->filter($this->meta['filters']);
         }
     }
     //Order
     if (array_key_exists('order', $options)) {
         $query->order($options['order']);
     } else {
         if (!empty($this->meta['order'])) {
             $query->order($this->meta['order']);
         }
     }
     //Group
     if (array_key_exists('group', $options)) {
         $query->group($options['group']);
     } else {
         if (!empty($this->meta['group'])) {
             $query->group($this->meta['group']);
         }
     }
     //Check Ownership
     if (array_key_exists('owner_id', $this->meta['fields'])) {
         if ($user = BackendUser::check()) {
             if (!in_array('superadmin', $user->roles)) {
                 $query->filter("`{$this->meta['table']}`.`owner_id` = :owner_id");
                 $q_params[':owner_id'] = $user->id;
             }
         }
//.........这里部分代码省略.........