PHP Query::join方法代码示例

/**
 * Get pages relative to a given page
 *
 * Filters:
 * Add an arg of the type $args['filter_foo'] = bar
 * will add a condition to the SELECT as
 * WHERE foo = bar
 *
 */
function publications_userapi_get_sitemap_pages($args)
{
    if (empty($args['itemid'])) {
        $args['itemid'] = 0;
    }
    if (empty($args['scope'])) {
        $args['scope'] = 'descendants';
    }
    if ($args['itemid'] == 0 && $args['scope'] == 'descendants') {
        $args['scope'] = 'all';
    }
    if (empty($args['sort'])) {
        $args['sort'] = 0;
    }
    // Make sure we have the base translation id
    if (!empty($args['itemid'])) {
        $args['itemid'] = xarMod::apiFunc('publications', 'user', 'gettranslationid', array('id' => $args['itemid'], 'locale' => xarModVars::get('publications', 'defaultlanguage')));
    }
    // Identify any filters
    $filters = array();
    foreach ($args as $k => $v) {
        if (strpos($k, 'filter_') === 0) {
            $argname = substr($k, 7);
            $filters[$argname] = $v;
        }
    }
    $xartable = xarDB::getTables();
    sys::import('xaraya.structures.query');
    $q = new Query();
    $q->addtable($xartable['publications'], 'p');
    switch ($args['scope']) {
        case 'all':
            $q->gt('p.leftpage_id', 0);
            break;
        case 'descendants':
            $q->addtable($xartable['publications'], 'root');
            $q->eq('root.id', $args['itemid']);
            $q->le('root.leftpage_id', 'expr:p.leftpage_id');
            $q->ge('root.rightpage_id', 'expr:p.rightpage_id');
            break;
        case 'children':
            $q->eq('p.parentpage_id', $args['itemid']);
            break;
        case 'siblings':
            $q->addtable($xartable['publications'], 'p1');
            $q->join('p.parentpage_id', 'p1.parentpage_id');
            $q->eq('p1.id', $args['itemid']);
            break;
    }
    if (!empty($args['itemtype'])) {
        $q->eq('p.pubtype_id', $args['itemtype']);
    }
    $q->eq('p.sitemap_flag', 1);
    $q->gt('p.state', 2);
    $q->addfield('p.id AS id');
    $q->addfield('p.name AS name');
    $q->addfield('p.title AS title');
    $q->addfield('p.description AS description');
    $q->addfield('p.sitemap_source_flag AS sitemap_source_flag');
    $q->addfield('p.sitemap_alias AS sitemap_alias');
    $q->addfield('p.pubtype_id AS pubtype_id');
    $q->addfield('p.rightpage_id AS rightpage_id');
    // Add any fiters we found
    foreach ($filters as $k => $v) {
        $q->eq('p.' . $k, $v);
    }
    // We can force alpha sorting, or else sort according to tree position
    if ($args['sort']) {
        $q->setorder('p.title');
    } else {
        $q->setorder('p.leftpage_id');
    }
    //    $q->qecho();
    $q->run();
    $pages = $q->output();
    $depthstack = array();
    foreach ($pages as $key => $page) {
        // Calculate the relative nesting level.
        // 'depth' is 0-based. Top level (root node) is zero.
        if (!empty($depthstack)) {
            while (!empty($depthstack) && end($depthstack) < $page['rightpage_id']) {
                array_pop($depthstack);
            }
        }
        $depthstack[$page['id']] = $page['rightpage_id'];
        $pages[$key]['depth'] = empty($depthstack) ? 0 : count($depthstack) - 1;
        // This item is the path for each page, based on page IDs.
        // It is effectively a list of ancestor IDs for a page.
        // FIXME: some paths seem to get a '0' root ID. They should only have real page IDs.
        $pages[$key]['idpath'] = array_keys($depthstack);
        $pathstack[$key] = $page['name'];
//.........这里部分代码省略.........

 /**
  * @covers Query::joinOnce
  */
 function testJoinOnce()
 {
     $q = new Query('table');
     $q->join('table.column', 'table2.column');
     $q->joinOnce('table.column', 'table2.column');
     $q->joinOnce('table2', 'column = column');
     $joins = $q->getJoins();
     $this->assertCount(1, $joins);
     $join = array_shift($joins);
     $this->assertEquals('JOIN `table2` ON (`table`.`column` = `table2`.`column`)', $join->getQueryStatement() . '');
 }

 /**
  * Add left join commands for the instance to a query.
  *
  *  @param Query $query Query instance to apply the joins to
  *  @private
  */
 private function _left_join($query)
 {
     if (count($this->_leftJoin)) {
         for ($i = 0, $ien = count($this->_leftJoin); $i < $ien; $i++) {
             $join = $this->_leftJoin[$i];
             $query->join($join['table'], $join['field1'] . ' ' . $join['operator'] . ' ' . $join['field2'], 'LEFT');
         }
     }
 }

/**
 * Publications Module
 *
 * @package modules
 * @subpackage publications module
 * @category Third Party Xaraya Module
 * @version 2.0.0
 * @copyright (C) 2011 Netspan AG
 * @license GPL {@link http://www.gnu.org/licenses/gpl.html}
 * @author Marc Lutolf <mfl@netspan.ch>
 */
function publications_userapi_gettranslationid($args)
{
    if (!isset($args['id'])) {
        throw new BadParameterException('id');
    }
    if (empty($args['id'])) {
        return 0;
    }
    // We can check on a full locale or just a partial one (excluding charset)
    if (empty($args['partiallocale'])) {
        $args['partiallocale'] = 0;
    }
    // We can look for a specific translation
    if (empty($args['locale'])) {
        $locale = xarUserGetNavigationLocale();
    } else {
        $locale = $args['locale'];
    }
    sys::import('xaraya.structures.query');
    if ($args['partiallocale']) {
        $parts = explode('.', $locale);
        $locale = $parts[0];
    }
    $xartable = xarDB::getTables();
    if (empty($args['locale'])) {
        // Return the id of the translation if it exists, or else the base document
        $q = new Query('SELECT', $xartable['publications']);
        $q->addfield('id');
        $q->eq('locale', $locale);
        $c[] = $q->peq('id', $args['id']);
        $c[] = $q->peq('parent_id', $args['id']);
        $q->qor($c);
        if (!$q->run()) {
            return $args['id'];
        }
        $result = $q->row();
        if (empty($result)) {
            return $args['id'];
        }
        return $result['id'];
    } elseif ($args['locale'] == xarUserGetNavigationLocale()) {
        // No need to look further
        return $args['id'];
    } elseif ($args['locale'] == xarModVars::get('publications', 'defaultlanguage')) {
        // Force getting the base document
        $q = new Query('SELECT', $xartable['publications']);
        $q->addfield('parent_id');
        $q->eq('id', $args['id']);
        if (!$q->run()) {
            return $args['id'];
        }
        $result = $q->row();
        if (empty($result)) {
            return $args['id'];
        }
        // If this was already the base document, return its ID
        if (empty($result['parent_id'])) {
            return $args['id'];
        }
        // Else return the parent ID
        return $result['parent_id'];
    } else {
        // Force getting another translation
        $q = new Query('SELECT');
        $q->addtable($xartable['publications'], 'p1');
        $q->addtable($xartable['publications'], 'p2');
        $q->join('p2.parent_id', 'p1.parent_id');
        $q->addfield('p2.id');
        $q->eq('p2.locale', $locale);
        $q->eq('p1.id', $args['id']);
        if (!$q->run()) {
            return $args['id'];
        }
        $result = $q->row();
        if (empty($result)) {
            return $args['id'];
        }
        return $result['id'];
    }
    if (xarUserGetVar('uname') == 'random') {
        $xartable = xarDB::getTables();
        $q = new Query('SELECT');
        $q->addtable($xartable['publications'], 'p1');
        $q->addtable($xartable['publications'], 'p2');
        $q->join('p2.id', 'p1.parent_id');
        $q->addfield('p1.id');
        $c[] = $q->peq('p1.id', $args['id']);
        $c[] = $q->peq('p1.parent_id', $args['id']);
        $c[] = $q->peq('p2.id', $args['id']);
//.........这里部分代码省略.........

 protected function manyToMany($prop, $extraColumns = array())
 {
     $options = [];
     if (gettype(static::$manyToMany[$prop]) == 'array') {
         $className = static::$manyToMany[$prop]['class'];
         if (isset(static::$manyToMany[$prop]['options'])) {
             $options = static::$manyToMany[$prop]['options'];
         }
     } else {
         // get classname defined in the static $manyToMany (k/v) array
         $className = static::$manyToMany[$prop];
     }
     self::setDefaults($options, ["direction" => ['pattern' => "/ASC|DESC/i", 'value' => 'ASC'], "limit" => ['pattern' => "/^\\d+\$/"], "offset" => ['pattern' => "/^\\d+\$/"]]);
     $foreignTable = $className::tableName();
     // get the proper order of tables as per naming convention for join table.
     $joint = static::tableJoin($this->tableName, $foreignTable);
     // query from the joint table
     $query = new Query($joint);
     // select only the columns from requested class
     $select = [$foreignTable => $className::$columns];
     if (!empty($extraColumns)) {
         $select[$joint] = $extraColumns;
         // add in additional columns
         static::$readOnly = array_merge(static::$readOnly, $extraColumns);
         // don't save these
     }
     $query->select($select);
     // join the requested class on the join table based on primary key
     $query->join("INNER JOIN `{$className::tableName()}` ON `{$joint}`.`{$className::tableName()}` = `{$className::tableName()}`.`{$className::$primaryKey}`");
     // limit to the primary key of this table
     $query->where("`{$joint}`.`{$this->tableName}` = ?", [$this->primaryKey]);
     // run the query and get back a 2d array, set to the requested prop
     self::applyOptions($query, $options);
     $this->{$prop} = new ModelCollection($className::query($query, $query->params, function ($row) use($className) {
         return new $className($row);
     }), $this);
 }

    /** Return unsatisfied prerequsites.
     * 
     * Note: code is an sql expression.
     */
    static function query_prerequisites($code, $completed, $taking = [], $values = [])
    {
        global $db;
        // Convert all the courses to strings.
        foreach ($completed as &$c) {
            if (is_a($c, 'Course')) {
                $c = $c->getcode();
            }
        }
        foreach ($taking as &$c) {
            if (is_a($c, 'Course')) {
                $c = $c->getcode();
            }
        }
        // Warning: Dragons Ahead!  This is a complex query. With a lot
        // of parts.
        ///// Inner query.
        // This inner query counts how many courses in $completed are
        // in the elgible group and not in the excluded group.
        $haspreq = new Query('coursegroups elg');
        $haspreq->select('count(elg.id)');
        $haspreq->where('elg.id = prerequisites.eligible');
        $haspreq->join('coursegroup_courses elgc', 'elgc.id = elg.id');
        // Where the course is in the completed group.
        // Or it can be taken concurrently and it is in the taking group.
        $haspreq->where('(
				elgc.course_code IN ' . Query::valuelistsql($completed) . '
				OR (
					elgc.course_code IN ' . Query::valuelistsql($taking) . '
					AND elgc.concurrent
				)
			)', array_merge($completed, $taking));
        // And it is not one of the courses that is excluded.
        $haspreq->where('NOT EXISTS (
				SELECT course_code
				FROM coursegroup_courses
				WHERE coursegroup_courses.id = prerequisites.excluded
				AND   coursegroup_courses.course_code = elgc.course_code
			)', []);
        ///// Outer Query
        // This query selects prerequsites where the number of credits
        // earned is less then the required credits.
        $q = new Query('prerequisites');
        $q->select('course_code');
        $q->select('eligible');
        $q->select('excluded');
        $q->where('course_code = ' . $code, $values);
        $q->where('(' . $haspreq->sql() . ') < credits', $haspreq->values());
        return $q;
    }

function publications_userapi_getpages($args)
{
    extract($args);
    if (!xarVarValidate('enum:id:index:name:left:right', $key, true)) {
        $key = 'index';
    }
    // Define if we are looking for the number of pages or the pages themselves
    $count = empty($count) ? false : true;
    // Assemble the query
    sys::import('xaraya.structures.query');
    $xartable = xarDB::getTables();
    $q = new Query();
    $q->addtable($xartable['publications'], 'tpages');
    $q->addtable($xartable['publications_types'], 'pt');
    $q->join('pt.id', 'tpages.pubtype_id');
    if ($count) {
        $q->addfield('COUNT(*)');
    } else {
        $q->setdistinct(true);
        $q->addfield('tpages.id AS id');
        $q->addfield('tpages.name AS name');
        $q->addfield('tpages.title AS title');
        $q->addfield('tpages.pubtype_id AS ptid');
        $q->addfield('tpages.parent_id AS base_id');
        $q->addfield('tpages.sitemap_flag AS sitemap_flag');
        $q->addfield('tpages.menu_flag AS menu_flag');
        $q->addfield('tpages.locale AS locale');
        $q->addfield('tpages.leftpage_id AS leftpage_id');
        $q->addfield('tpages.rightpage_id AS rightpage_id');
        $q->addfield('tpages.parentpage_id AS parentpage');
        $q->addfield('tpages.access AS access');
        $q->addfield('tpages.state AS status');
        $q->addfield('pt.description AS pubtype_name');
    }
    if (isset($baseonly)) {
        $q->eq('tpages.parent_id', 0);
    }
    if (isset($name)) {
        $q->eq('tpages.name', (string) $name);
    }
    if (isset($status)) {
        // If a list of statuses have been provided, then select for any of them.
        if (strpos($status, ',') === false) {
            $numeric_status = convert_status($status);
            $q->eq('tpages.state', strtoupper($status));
        } else {
            $statuses = explode(',', strtoupper($status));
            $numeric_statuses = array();
            foreach ($statuses as $stat) {
                $numeric_statuses[] = convert_status($stat);
            }
            $q->in('tpages.state', $numeric_statuses);
        }
    }
    if (isset($id)) {
        $q->eq('tpages.id', (int) $id);
        $where[] = 'tpages.id = ?';
        $bind[] = (int) $id;
    } elseif (!empty($ids)) {
        $addwhere = array();
        foreach ($ids as $myid) {
            if (!empty($myid) && is_numeric($myid)) {
                $addwhere[] = (int) $myid;
            }
        }
        $q->in('tpages.state', $addwhere);
    }
    if (isset($itemtype)) {
        $q->eq('tpages.pubtype_id', (int) $itemtype);
    }
    if (isset($parent)) {
        $q->eq('tpages.parentpage_id', (int) $parent);
    }
    // Used to retrieve descendants.
    if (isset($left_range) && is_array($left_range)) {
        $q->between('tpages.leftpage_id', $left_range);
    }
    // Used to prune a single branch of the tree.
    if (isset($left_exclude) && is_array($left_exclude)) {
        //'tpages.leftpage_id NOT between ? AND ?' - does not work on some databases
        $c[] = $q->plt('tpages.leftpage_id', (int) $left_exclude[0]);
        $c[] = $q->pgt('tpages.leftpage_id', (int) $left_exclude[1]);
        $q->qor($c);
        unset($c);
    }
    // Used to retrieve ancestors.
    if (isset($wrap_range) && is_numeric($wrap_range)) {
        $c[] = $q->ple('tpages.leftpage_id', (int) $wrap_range[0]);
        $c[] = $q->pge('tpages.leftpage_id', (int) $left_range[1]);
        // can't be right: this is an array
        $q->qand($c);
        unset($c);
    }
    // If the request is to fetch a tree that *contains* a particular
    // page, then add the extra sub-queries in here.
    if (!empty($tree_contains_id) || !empty($tree_contains_name)) {
        $q->addtable($xartable['publications'], 'tpages_member');
        if (!empty($tree_contains_id)) {
            $q->eq('tpages_member.id', (int) $tree_contains_id);
        }
//.........这里部分代码省略.........

 public function get_source_rows($name, $field, $id = false)
 {
     if ($id) {
         return where('id = %d', $id)->get($name);
     }
     if (isset($field['where'])) {
         $query = new Query();
         $path = explode('>', FW4_Admin::$parent_structure['path']);
         $whereval = strval($field['where']);
         if (preg_match_all('/\\[(.*?)\\]/is', $whereval, $matches, PREG_SET_ORDER)) {
             foreach ($matches as $match) {
                 $parent_item = false;
                 $current_item = FW4_Admin::$current_item;
                 foreach (explode('.', $match[1]) as $part) {
                     if ($part == 'parent') {
                         array_pop($path);
                         if ($parent_item === false) {
                             $parent_item = FW4_Admin::$parent_item;
                         } else {
                             $parent_item = $parent_item->parent();
                         }
                     } else {
                         if (!$parent_item && !$current_item) {
                             return false;
                         }
                         $data = $parent_item ? $parent_item : $current_item;
                         $structure = FW4_Structure::get_object_structure(implode('>', $path), false);
                         $other_field = $structure->xpath('*[@name="' . addslashes($part) . '"]');
                         if (is_array($other_field)) {
                             $other_field = reset($other_field);
                         }
                         if ($other_field && $other_field->getName() == 'choice') {
                             if (isset($other_field['multiple'])) {
                                 $ids = $data->{$part}->ids();
                                 if (!count($ids)) {
                                     $ids[] = 0;
                                 }
                                 $whereval = str_replace($match[0], '(' . implode(',', $ids) . ')', $whereval);
                             } else {
                                 $fieldname = $part . '_id';
                                 if (!isset($data->{$fieldname})) {
                                     return false;
                                 }
                                 $whereval = str_replace($match[0], $data->{$fieldname}, $whereval);
                             }
                         } else {
                             if (!property_exists($data, $part)) {
                                 return false;
                             }
                             $whereval = str_replace($match[0], is_null($data->{$part}) ? 'NULL' : $data->{$part}, $whereval);
                         }
                     }
                 }
             }
         }
         if (preg_match_all('/(\\S+)\\s+contains\\s+(\\S+)/is', $whereval, $matches, PREG_SET_ORDER)) {
             foreach ($matches as $match) {
                 $structure = FW4_Structure::get_object_structure($name, false);
                 $other_field = $structure->xpath('*[@name="' . addslashes($match[1]) . '"]');
                 if (is_array($other_field)) {
                     $other_field = reset($other_field);
                 }
                 if (!isset($other_field['multiple'])) {
                     throw new Exception('"contains" only possible on choice with multiple attribute');
                 }
                 if (!$other_field) {
                     return false;
                 }
                 $query->join($structure['path'] . '>' . $other_field['name'], $other_field['name'] . '.' . $structure['name'] . '_id = ' . $structure['name'] . '.id');
                 $whereval = str_replace($match[0], $other_field['name'] . '.' . $other_field['name'] . ' = ' . $match[2], $whereval);
             }
         }
         return $query->where($whereval)->get($name);
     }
     if (!isset(self::$cache[$name])) {
         self::$cache[$name] = get($name);
     }
     return self::$cache[$name];
 }

/**
 * Publications Module
 *
 * @package modules
 * @subpackage publications module
 * @category Third Party Xaraya Module
 * @version 2.0.0
 * @copyright (C) 2011 Netspan AG
 * @license GPL {@link http://www.gnu.org/licenses/gpl.html}
 * @author Marc Lutolf <mfl@netspan.ch>
 */
function publications_admin_clone()
{
    if (!xarSecurityCheck('ManagePublications')) {
        return;
    }
    if (!xarVarFetch('name', 'isset', $objectname, NULL, XARVAR_DONT_SET)) {
        return;
    }
    if (!xarVarFetch('ptid', 'isset', $ptid, NULL, XARVAR_DONT_SET)) {
        return;
    }
    if (!xarVarFetch('itemid', 'isset', $data['itemid'], NULL, XARVAR_DONT_SET)) {
        return;
    }
    if (!xarVarFetch('confirm', 'int', $confirm, 0, XARVAR_DONT_SET)) {
        return;
    }
    if (empty($data['itemid'])) {
        return xarResponse::NotFound();
    }
    // If a pubtype ID was passed, get the name of the pub object
    if (isset($ptid)) {
        $pubtypeobject = DataObjectMaster::getObject(array('name' => 'publications_types'));
        $pubtypeobject->getItem(array('itemid' => $ptid));
        $objectname = $pubtypeobject->properties['name']->value;
    }
    if (empty($objectname)) {
        return xarResponse::NotFound();
    }
    sys::import('modules.dynamicdata.class.objects.master');
    $data['object'] = DataObjectMaster::getObject(array('name' => $objectname));
    if (empty($data['object'])) {
        return xarResponse::NotFound();
    }
    // Security
    if (!$data['object']->checkAccess('update')) {
        return xarResponse::Forbidden(xarML('Clone #(1) is forbidden', $object->label));
    }
    $data['object']->getItem(array('itemid' => $data['itemid']));
    $data['authid'] = xarSecGenAuthKey();
    $data['name'] = $data['object']->properties['name']->value;
    $data['label'] = $data['object']->label;
    xarTplSetPageTitle(xarML('Clone Publication #(1) in #(2)', $data['itemid'], $data['label']));
    if ($confirm) {
        if (!xarSecConfirmAuthKey()) {
            return;
        }
        // Get the name for the clone
        if (!xarVarFetch('newname', 'str', $newname, "", XARVAR_NOT_REQUIRED)) {
            return;
        }
        if (empty($newname)) {
            $newname = $data['name'] . "_copy";
        }
        if ($newname == $data['name']) {
            $newname = $data['name'] . "_copy";
        }
        $newname = strtolower(str_ireplace(" ", "_", $newname));
        // Create the clone
        $data['object']->properties['name']->setValue($newname);
        $data['object']->properties['id']->setValue(0);
        $cloneid = $data['object']->createItem(array('itemid' => 0));
        // Create the clone's translations
        if (!xarVarFetch('clone_translations', 'int', $clone_translations, 0, XARVAR_NOT_REQUIRED)) {
            return;
        }
        if ($clone_translations) {
            // Get the info on all the objects to be cloned
            sys::import('xaraya.structures.query');
            $tables = xarDB::getTables();
            $q = new Query();
            $q->addtable($tables['publications'], 'p');
            $q->addtable($tables['publications_types'], 'pt');
            $q->join('p.pubtype_id', 'pt.id');
            $q->eq('parent_id', $data['itemid']);
            $q->addfield('p.id AS id');
            $q->addfield('pt.name AS name');
            $q->run();
            // Clone each one
            foreach ($q->output() as $item) {
                $object = DataObjectMaster::getObject(array('name' => $item['name']));
                $object->getItem(array('itemid' => $item['id']));
                $object->properties['parent']->value = $cloneid;
                $object->properties['id']->value = 0;
                $object->createItem(array('itemid' => 0));
            }
        }
        // Redirect if we came from somewhere else
        $current_listview = xarSession::getVar('publications_current_listview');
//.........这里部分代码省略.........