Gets the SQL join fragment used when selecting entities from a
many-to-many association.

@return string
169984
protected function getSelectManyToManyJoinSQL(ManyToManyAssociationMetadata $association)
    {
        $conditions        = [];
        $owningAssociation = $association;
        $sourceTableAlias  = $this->getSQLTableAlias($this->class->getTableName());

        if (! $association->isOwningSide()) {
            $targetEntity      = $this->em->getClassMetadata($association->getTargetEntity());
            $owningAssociation = $targetEntity->getProperty($association->getMappedBy());
        }

        $joinTable     = $owningAssociation->getJoinTable();
        $joinTableName = $joinTable->getQuotedQualifiedName($this->platform);
        $joinColumns   = $association->isOwningSide()
            ? $joinTable->getInverseJoinColumns()
            : $joinTable->getJoinColumns();

        foreach ($joinColumns as $joinColumn) {
            $conditions[] = sprintf(
                '%s.%s = %s.%s',
                $sourceTableAlias,
                $this->platform->quoteIdentifier($joinColumn->getReferencedColumnName()),
                $joinTableName,
                $this->platform->quoteIdentifier($joinColumn->getColumnName())
            );
        }

        return ' INNER JOIN ' . $joinTableName . ' ON ' . implode(' AND ', $conditions);
    }
950590
private function buildJoins()
    {
        $joins = array();

        foreach ($this->query['joins'] as $join) {
            // Handle join with alias
            if (is_array($join[0])) {
                $joins[] = "LEFT JOIN `{$join[0][0]}` `{$join[0][1]}` on {$join[1]}";
            }
            // Handle regular joining
            else {
                $joins[] = "LEFT JOIN `{$join[0]}` on {$join[1]}";
            }
        }

        return implode(" ", $joins);
    }
305887
public function get_completed_criteria_sql() {
        global $DB;

        $join = '';
        $where = '';
        $params = [];
        $competencyids = [];

        $badge = $DB->get_record('badge', array('id' => $this->badgeid));

        if (!self::is_enabled()) {
            return array($join, $where, $params);
        }

        if ($this->method == BADGE_CRITERIA_AGGREGATION_ANY) {
            // User has received ANY of the required competencies (we can use an in or equals list).
            foreach ($this->params as $param) {
                $competencyids[] = $param['competency'];
            }

            $where = ' AND uc2.competencyid ';
            list($sql, $params) = $DB->get_in_or_equal($competencyids, SQL_PARAMS_NAMED, 'usercomp');
            $where .= $sql;
            if ($badge->type == BADGE_TYPE_SITE) {
                $join = ' JOIN {competency_usercomp} uc2 ON uc2.userid = u.id';
            } else if ($badge->type == BADGE_TYPE_COURSE) {
                $join = ' JOIN {competency_usercompcourse} uc2 ON uc2.userid = u.id AND uc2.courseid = :competencycourseid ';
                $params['competencycourseid'] = $badge->courseid;
            }
            $where .= ' AND uc2.proficiency = :isproficient ';
            $params['isproficient'] = true;
        } else {

            // User has received ALL of the required competencies (we have to join on each one).
            $joincount = 0;
            foreach ($this->params as $param) {
                $joincount++;
                $join .= ' JOIN {competency_usercomp} uc' . $joincount . ' ON uc' . $joincount . '.userid = u.id';
                $where .= ' AND uc' . $joincount . '.competencyid = :competencyindex' . $joincount;
                $params['competencyindex' . $joincount] = $param['competency'];

                $where .= ' AND uc' . $joincount . '.userid = u.id';
                $where .= ' AND uc' . $joincount . '.proficiency = :isproficient' . $joincount;
                $params['isproficient' . $joincount] = true;
            }

        }
        return array($join, $where, $params);
    }
752590
public function GetSelect() {
      // Close off any open query elements.
      $this->_EndQuery();
      
      $Sql = (!$this->_Distinct) ? 'select ' : 'select distinct ';

      // Don't escape the field if it is numeric or an asterisk (all columns)
      $Selects = array();
      foreach($this->_Selects as $Key => $Expr) {
         $Field = $Expr['Field'];
         $Function = $Expr['Function'];
         $Alias = $Expr['Alias'];
         $CaseOptions = ArrayValue('CaseOptions', $Expr);
         if ($Field != '*' && !is_numeric($Field))
            $Field = $this->EscapeIdentifier($Field);

         if ($Alias == '' && $Function != '')
            $Alias = $Field;

         // if (in_array(strtolower($Function), array('max', 'min', 'avg', 'sum', 'count')))
         if ($Function != '') {
				if(strpos($Function, '%s') !== FALSE)
					$Field = sprintf($Function, $Field);
				else
					$Field = $Function.'('.$Field.')';
			}

         if ($CaseOptions !== FALSE)
            $Field = 'case ' . $Field . $CaseOptions . ' end';

         if ($Alias != '')
            $Field .= ' as '.$this->QuoteIdentifier($Alias);

         if ($Field != '')
            $Selects[] = $Field;
      }
      $Sql .= (count($Selects) == 0) ? '*' : implode(', ', $Selects);

      if (count($this->_Froms) > 0)
         $Sql .= "\nfrom ".$this->_FromTables($this->_Froms);

      if (count($this->_Joins) > 0) {
         $Sql .= "\n";
         $Sql .= implode("\n", $this->_Joins);
      }

      if (count($this->_Wheres) > 0)
         $Sql .= "\nwhere ";

      $Sql .= implode("\n", $this->_Wheres);

      // Close any where groups that were left open.
      for ($i = 0; $i < $this->_OpenWhereGroupCount; ++$i) {
         $Sql .= ')';
      }
      $this->_OpenWhereGroupCount = 0;

      if (count($this->_GroupBys) > 0) {
         $Sql .= "\ngroup by ";

         // special consideration for table aliases
         if (count($this->_AliasMap) > 0 && $this->Database->DatabasePrefix)
            $Sql .= implode(', ', $this->_FilterTableAliases($this->_GroupBys));
         else
            $Sql .= implode(', ', $this->_GroupBys);
      }

      if (count($this->_Havings) > 0)
         $Sql .= "\nhaving ".implode("\n", $this->_Havings);

      if (count($this->_OrderBys) > 0)
         $Sql .= "\norder by ".implode(', ', $this->_OrderBys);

      if (is_numeric($this->_Limit)) {
         $Sql .= "\n";
         $Sql = $this->GetLimit($Sql, $this->_Limit, $this->_Offset);
      }

      return $Sql;
   }
298731
protected function getSelectColumnsSQL()
    {
        // Create the column list fragment only once
        if ($this->currentPersisterContext->selectColumnListSql !== null) {
            return $this->currentPersisterContext->selectColumnListSql;
        }

        $columnList         = array();
        $discrColumn        = $this->class->discriminatorColumn['name'];
        $baseTableAlias     = $this->getSQLTableAlias($this->class->name);
        $resultColumnName   = $this->platform->getSQLResultCasing($discrColumn);

        $this->currentPersisterContext->rsm->addEntityResult($this->class->name, 'r');
        $this->currentPersisterContext->rsm->setDiscriminatorColumn('r', $resultColumnName);
        $this->currentPersisterContext->rsm->addMetaResult('r', $resultColumnName, $discrColumn);

        // Add regular columns
        foreach ($this->class->fieldMappings as $fieldName => $mapping) {
            $class = isset($mapping['inherited'])
                ? $this->em->getClassMetadata($mapping['inherited'])
                : $this->class;

            $columnList[] = $this->getSelectColumnSQL($fieldName, $class);
        }

        // Add foreign key columns
        foreach ($this->class->associationMappings as $mapping) {
            if ( ! $mapping['isOwningSide'] || ! ($mapping['type'] & ClassMetadata::TO_ONE)) {
                continue;
            }

            $tableAlias = isset($mapping['inherited'])
                ? $this->getSQLTableAlias($mapping['inherited'])
                : $baseTableAlias;

            foreach ($mapping['targetToSourceKeyColumns'] as $srcColumn) {
                $className = isset($mapping['inherited'])
                    ? $mapping['inherited']
                    : $this->class->name;

                $targetClass = $this->em->getClassMetadata($mapping['targetEntity']);

                $columnList[] = $this->getSelectJoinColumnSQL(
                    $tableAlias,
                    $srcColumn,
                    $className,
                    PersisterHelper::getTypeOfColumn(
                        $mapping['sourceToTargetKeyColumns'][$srcColumn],
                        $targetClass,
                        $this->em
                    )
                );
            }
        }

        // Add discriminator column (DO NOT ALIAS, see AbstractEntityInheritancePersister#processSQLResult).
        $tableAlias = ($this->class->rootEntityName == $this->class->name)
            ? $baseTableAlias
            : $this->getSQLTableAlias($this->class->rootEntityName);

        $columnList[] = $tableAlias . '.' . $discrColumn;

        // sub tables
        foreach ($this->class->subClasses as $subClassName) {
            $subClass   = $this->em->getClassMetadata($subClassName);
            $tableAlias = $this->getSQLTableAlias($subClassName);

            // Add subclass columns
            foreach ($subClass->fieldMappings as $fieldName => $mapping) {
                if (isset($mapping['inherited'])) {
                    continue;
                }

                $columnList[] = $this->getSelectColumnSQL($fieldName, $subClass);
            }

            // Add join columns (foreign keys)
            foreach ($subClass->associationMappings as $mapping) {
                if ( ! $mapping['isOwningSide']
                        || ! ($mapping['type'] & ClassMetadata::TO_ONE)
                        || isset($mapping['inherited'])) {
                    continue;
                }

                foreach ($mapping['targetToSourceKeyColumns'] as $srcColumn) {
                    $className = isset($mapping['inherited'])
                        ? $mapping['inherited']
                        : $subClass->name;

                    $targetClass = $this->em->getClassMetadata($mapping['targetEntity']);

                    $columnList[] = $this->getSelectJoinColumnSQL(
                        $tableAlias,
                        $srcColumn,
                        $className,
                        PersisterHelper::getTypeOfColumn(
                            $mapping['sourceToTargetKeyColumns'][$srcColumn],
                            $targetClass,
                            $this->em
                        )
                    );
                }
            }
        }

        $this->currentPersisterContext->selectColumnListSql = implode(', ', $columnList);

        return $this->currentPersisterContext->selectColumnListSql;
    }
1277097
private function getSQLForSelect()
    {
        $query = 'SELECT ' . implode(', ', $this->sqlParts['select']);

        $query .= ($this->sqlParts['from'] ? ' FROM ' . implode(', ', $this->getFromClauses()) : '')
            . ($this->sqlParts['where'] !== null ? ' WHERE ' . ((string) $this->sqlParts['where']) : '')
            . ($this->sqlParts['groupBy'] ? ' GROUP BY ' . implode(', ', $this->sqlParts['groupBy']) : '')
            . ($this->sqlParts['having'] !== null ? ' HAVING ' . ((string) $this->sqlParts['having']) : '')
            . ($this->sqlParts['orderBy'] ? ' ORDER BY ' . implode(', ', $this->sqlParts['orderBy']) : '');

        if ($this->isLimitQuery()) {
            return $this->connection->getDatabasePlatform()->modifyLimitQuery(
                $query,
                $this->maxResults,
                $this->firstResult
            );
        }

        return $query;
    }
1015640
public function toSQL()
    {
        $sql = [];
        $this->params = [];

        $sql[] = 'WHERE';

        if (! $this->conditions) {
            $sql[] = '1';
        } else {
            foreach ($this->conditions as $index => $value) {
                if ( $index > 0 ) $sql[] = $value->chain_by;
                $sql[] = $value->toSQL();
            }
        }

        return join(' ', $sql);
    }
812414
protected function get_searchsql() {
        global $DB;

        $ctxselect = ', ' . context_helper::get_preload_record_columns_sql('ctx');
        $ctxjoin = "LEFT JOIN {context} ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel)";
        $params = array(
            'contextlevel' => CONTEXT_COURSE,
            'fullnamesearch' => '%'.$this->get_search().'%',
            'shortnamesearch' => '%'.$this->get_search().'%'
        );

        $select     = " SELECT c.id, c.fullname, c.shortname, c.visible, c.sortorder ";
        $from       = " FROM {course} c ";
        $where      = " WHERE (".$DB->sql_like('c.fullname', ':fullnamesearch', false)." OR ".
            $DB->sql_like('c.shortname', ':shortnamesearch', false).")";
        $orderby    = " ORDER BY c.sortorder";

        if ($this->currentcourseid !== null && !$this->includecurrentcourse) {
            $where .= " AND c.id <> :currentcourseid";
            $params['currentcourseid'] = $this->currentcourseid;
        }

        return array($select.$ctxselect.$from.$ctxjoin.$where.$orderby, $params);
    }
17414
public function getDql()
    {
        if (null === $this->selectClause) {
            throw new MissingSelectClauseException('Select clause is missing');
        }

        $eol = PHP_EOL;
        $joinRelatives = $this->joinSingleRelatives ? $this->joinRelativesClause : '';
        if ($this->leftJoinPlugins) {
            $joinRelatives .= " LEFT JOIN node.resourceType rtp{$eol}
            LEFT JOIN rtp.plugin p{$eol}";
        }
        $joinRoles = $this->leftJoinRoles ?
            "LEFT JOIN node.workspace workspace{$eol}".
            "LEFT JOIN workspace.roles role{$eol}" :
            '';
        $joinRights = $this->leftJoinRights ?
            "LEFT JOIN node.rights rights{$eol}".
            "JOIN rights.role rightRole{$eol}" :
            '';
        $joinLogs = $this->leftJoinLogs ?
            "JOIN node.logs log{$eol}".
            "JOIN log.resourceNode log_node{$eol}" :
            '';
        $dql =
            $this->selectClause.
            $this->fromClause.
            $joinRelatives.
            $joinRoles.
            $joinRights.
            $joinLogs.
            $this->joinClause.
            $this->whereClause.
            $this->groupByClause.
            $this->orderClause;

        return $dql;
    }
604501
protected function getJoins(array $queryParts)
    {
        $result = null;

        if (isset($queryParts['join']) && !empty($queryParts['join'])) {
            $result = [];

            foreach ($queryParts['join'] as $join) {
                $piece = trim($join[0] . ' JOIN') . ' ';
                $piece .= is_a($join[1], 'Database\Table') ?
                    sprintf(
                        '%s AS %s',
                        $this->quoteTable($join[1]->getTable()),
                        $this->quoteTable($join[1]->getName())
                    ) :
                    (string)$join[1];
                $piece .= ' ON ' . $join[2];
                $result[] = $piece;
            }

            $result = implode(' ', $result);
        }

        return $result;
    }
487572
protected function getSqlForSelect($count = false)
    {
        $parts = $this->sqlParts;

        if (!$parts['select']) {
            $parts['select'] = array('*');
        }

        $query = 'SELECT ' . implode(', ', $parts['select']) . ' FROM ' . $this->getFrom();

        // JOIN
        foreach ($parts['join'] as $join) {
            $query .= ' ' . strtoupper($join['type'])
                . ' JOIN ' . $join['table']
                . ' ON ' . $join['on'];
        }

        $query .= ($parts['where'] !== null ? ' WHERE ' . ((string)$parts['where']) : '')
            . ($parts['groupBy'] ? ' GROUP BY ' . implode(', ', $parts['groupBy']) : '')
            . ($parts['having'] !== null ? ' HAVING ' . ((string)$parts['having']) : '');

        if (false === $count) {
            $query .= ($parts['orderBy'] ? ' ORDER BY ' . implode(', ', $parts['orderBy']) : '')
                . ($parts['limit'] !== null ? ' LIMIT ' . $parts['limit'] : '')
                . ($parts['offset'] !== null ? ' OFFSET ' . $parts['offset'] : '');
        }

        $query .= $this->generateLockSql();

        return $query;
    }
1239921
public function findAllJoinPage()
    {
        $qb = $this->createQueryBuilder('m');
        $qb->select('m,p');
        $qb->leftJoin('m.page', 'p');

        return $qb->getQuery()->execute();
    }
1011415
private function _prepareJoin() 
	{
		$sQuery = '';

		if (is_array($this->_aJoin) && count($this->_aJoin) > 0) {

			foreach ($this->_aJoin as $sKey => $aValue) {

				if (isset($aValue['type']) && $aValue['type'] == 'left') {

					$sQuery .= " LEFT JOIN `".$aValue['table']."` ";
				}
				else {

					$sQuery .= " INNER JOIN `".$aValue['table']."` ";
				}

				if (isset($aValue['as']) && $aValue['as']) {

					$sQuery .= " AS ".$aValue['as']." ";
				}
				else {

					$sQuery .= " AS t".$this->_iAlias." ";
					$this->_iAlias++;
				}

				$sQuery .= " ON ".$aValue['left_field']." =  ".$aValue['right_field']." ";
			}
		}

		return $sQuery;
	}
446785
public function getQuery()
    {
        $sql = $this->prepareSelectString();
        $sql .= $this->prepareJoinString();
        $sql .= $this->prepareWhereString();
        $sql .= $this->prepareGroupByString();
        $sql .= $this->prepareHavingString();
        $sql .= $this->prepareOrderByString();
        $sql .= $this->prepareLimitString();

        return $sql;
    }
219219
private function prepareGetSelectRegister()
    {
        $table_descriptor = $this->getStorageDescriptor();

        $fragment = new CNabuPHPMethodBuilder($this, 'getSelectRegister');
        $fragment->addComment('Gets SELECT sentence to load a single register from the storage.');
        $fragment->addComment('@return string Return the sentence.');

        if ($table_descriptor->hasPrimaryConstraint()) {
            $part_if = '';
            $part_where = array();
            foreach ($table_descriptor->getPrimaryConstraintFieldNames() as $field_name) {
                list($if, $where) = $this->buildSelectRegisterFragment($field_name);
                if (strlen($if) > 0 && strlen($where) > 0) {
                    $part_if .= (strlen($part_if) === 0 ? '' : ' && ') . $if;
                    $part_where[] = $where;
                }
            }

            $output = array(
                        "return ($part_if)",
                        "    ? \$this->buildSentence(",
                        "            'select * '",
                        "            . 'from $this->table_name '"
            );

            $c = 0;
            foreach ($part_where as $where) {
                $output[] = ($c++ === 0
                        ? "           . \"where $where \""
                        : "             . \"and $where \"");
            }
            $output[] = "      )";
            $output[] = "    : null;";

            $fragment->addFragment($output);
        }

        $this->addFragment($fragment);
    }
1011056
protected function getSelectColumnAssociationSQL($field, AssociationMetadata $association, ClassMetadata $class, $alias = 'r')
    {
        if (! ($association->isOwningSide() && $association instanceof ToOneAssociationMetadata)) {
            return '';
        }

        $columnList    = [];
        $targetClass   = $this->em->getClassMetadata($association->getTargetEntity());
        $sqlTableAlias = $this->getSQLTableAlias($class->getTableName(), ($alias === 'r' ? '' : $alias));

        foreach ($association->getJoinColumns() as $joinColumn) {
            /** @var JoinColumnMetadata $joinColumn */
            $columnName           = $joinColumn->getColumnName();
            $quotedColumnName     = $this->platform->quoteIdentifier($columnName);
            $referencedColumnName = $joinColumn->getReferencedColumnName();
            $resultColumnName     = $this->getSQLColumnAlias();

            if (! $joinColumn->getType()) {
                $joinColumn->setType(PersisterHelper::getTypeOfColumn($referencedColumnName, $targetClass, $this->em));
            }

            $this->currentPersisterContext->rsm->addMetaResult(
                $alias,
                $resultColumnName,
                $columnName,
                $association->isPrimaryKey(),
                $joinColumn->getType()
            );

            $columnList[] = sprintf('%s.%s AS %s', $sqlTableAlias, $quotedColumnName, $resultColumnName);
        }

        return implode(', ', $columnList);
    }
667280
public function getSelectQuery()
    {
        $sql = 'SELECT ' . $this->getSelect();
        $sql .= "\nFROM " . $this->getFrom();
        if ($this->where) {
            $sql .= "\nWHERE " . $this->getWhere();
        }
        if ($this->groupBy) {
            $sql .= "\nGROUP BY " . $this->getGroupBy();
        }
        if ($this->having) {
            $sql .= "\nHAVING " . $this->getHaving();
        }
        if ($this->orderBy) {
            $sql .= "\nORDER BY " . $this->getOrderBy();
        }
        if ($this->limit) {
            $sql .= "\nLIMIT " . $this->getLimit();
        }

        return $sql;
    }