/** * Test to confirm joins are working correctly when ordering by an attribute on item. * Testing when the join adapter has already added a display attribute from a related account */ public function testOrderByNoteCreatedDateTimeAndConfirmJoinsAreCorrect() { $q = DatabaseCompatibilityUtil::getQuote(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Note'); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter(); $builder = new DisplayAttributesReportQueryBuilder($joinTablesAdapter, $selectQueryAdapter); $displayAttribute = new DisplayAttributeForReportForm('NotesModule', 'Note', Report::TYPE_ROWS_AND_COLUMNS); $displayAttribute->attributeIndexOrDerivedType = 'Account__activityItems__Inferred___name'; //First build the display attributes from clauses $builder->makeQueryContent(array($displayAttribute)); $builder = new OrderBysReportQueryBuilder($joinTablesAdapter); $orderBy = new OrderByForReportForm('NotesModule', 'Note', Report::TYPE_ROWS_AND_COLUMNS); $orderBy->attributeIndexOrDerivedType = 'createdDateTime'; //Second build the order by from clauses $builder->makeQueryContent(array($orderBy)); $fromTablesAndAliases = $joinTablesAdapter->getFromTablesAndAliases(); $this->assertEquals('activity', $fromTablesAndAliases[0]['tableAliasName']); $this->assertEquals('note', $fromTablesAndAliases[0]['onTableAliasName']); $this->assertEquals('ownedsecurableitem1', $fromTablesAndAliases[1]['tableAliasName']); $this->assertEquals('activity', $fromTablesAndAliases[1]['onTableAliasName']); $this->assertEquals('securableitem1', $fromTablesAndAliases[2]['tableAliasName']); $this->assertEquals('ownedsecurableitem1', $fromTablesAndAliases[2]['onTableAliasName']); $this->assertEquals('item1', $fromTablesAndAliases[3]['tableAliasName']); $this->assertEquals('securableitem1', $fromTablesAndAliases[3]['onTableAliasName']); }
/** * For a give User name, run a partial search by * full name and retrieve user models. * */ public static function getUsersByPartialFullName($partialName, $pageSize) { assert('is_string($partialName)'); assert('is_int($pageSize)'); $personTableName = RedBeanModel::getTableName('Person'); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('User'); $joinTablesAdapter->addFromTableAndGetAliasName($personTableName, "{$personTableName}_id"); $fullNameSql = DatabaseCompatibilityUtil::concat(array('person.firstname', '\' \'', 'person.lastname')); $where = " (person.firstname like lower('{$partialName}%') or " . " person.lastname like lower('{$partialName}%') or " . " {$fullNameSql} like lower('{$partialName}%')) "; return User::getSubset($joinTablesAdapter, null, $pageSize, $where, "person.firstname, person.lastname"); }
protected function resolveExtraSql(RedBeanModelJoinTablesQueryAdapter &$joinTablesAdapter, &$where) { if ($where != '') { $where .= ' and '; } $user = Yii::app()->user->userModel; $quote = DatabaseCompatibilityUtil::getQuote(); $starredTableName = StarredUtil::getStarredTableName($this->modelClassName); $extraOnQueryPart = "and {$quote}{$starredTableName}{$quote}.{$quote}user_id{$quote} = {$user->id}"; $starredTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($starredTableName, 'id', null, 'model_id', $extraOnQueryPart); $where .= "{$quote}{$starredTableAliasName}{$quote}.{$quote}user_id{$quote} = {$user->id}"; }
public static function getModelsByFullName($modelClassName, $fullName) { assert('is_string($modelClassName)'); assert('is_subclass_of($modelClassName, "Person") && $modelClassName != "Person" ||' . '$modelClassName == "User"'); assert('is_string($fullName)'); $personTableName = RedBeanModel::getTableName('Person'); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName); $joinTablesAdapter->addFromTableAndGetAliasName($personTableName, "{$personTableName}_id"); $fullNameSql = DatabaseCompatibilityUtil::concat(array('person.firstname', '\' \'', 'person.lastname')); $where = "{$fullNameSql} = '{$fullName}'"; return $modelClassName::getSubset($joinTablesAdapter, null, null, $where); }
/** * Given a model class name, return all the derived attributes based on the called class. * @param string $modelClassName */ public static function getAllByModelClassName($modelClassName) { assert('$modelClassName != ""'); $derivedAttirbuteMetadataTableName = RedBeanModel::getTableName('DerivedAttributeMetadata'); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter(get_called_class()); $joinTablesAdapter->addFromTableAndGetAliasName($derivedAttirbuteMetadataTableName, "{$derivedAttirbuteMetadataTableName}_id"); $where = "{$derivedAttirbuteMetadataTableName}.modelclassname = '{$modelClassName}'"; $models = static::getSubset($joinTablesAdapter, null, null, $where); if (count($models) == 0) { return array(); } return $models; }
/** * For a give User name, run a partial search by * full name and retrieve user models. * @param $partialName * @param $pageSize * @param $autoCompleteOptions * @return Array */ public static function getUsersByPartialFullName($partialName, $pageSize, $autoCompleteOptions = null) { assert('is_string($partialName)'); assert('is_int($pageSize)'); static::sanitizeSearchTerm($partialName); $personTableName = Person::getTableName(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('User'); $joinTablesAdapter->addFromTableAndGetAliasName($personTableName, "{$personTableName}_id"); $fullNameSql = DatabaseCompatibilityUtil::concat(array('person.firstname', '\' \'', 'person.lastname')); $where = '(_user.hidefromselecting is null OR _user.hidefromselecting = 0) and '; $where .= " (person.firstname like lower('{$partialName}%') or " . " person.lastname like lower('{$partialName}%') or " . " {$fullNameSql} like lower('{$partialName}%')) "; static::handleAutoCompleteOptions($joinTablesAdapter, $where, $autoCompleteOptions); return User::getSubset($joinTablesAdapter, null, $pageSize, $where, "person.firstname, person.lastname"); }
protected static function makeSqlQuery($searchAttributeData) { $quote = DatabaseCompatibilityUtil::getQuote(); $where = null; $selectDistinct = false; $autoresponderTableName = Autoresponder::getTableName('Autoresponder'); $autoresponderItemTableName = AutoresponderItem::getTableName('AutoresponderItem'); $emailMessageTableName = EmailMessage::getTableName('EmailMessage'); $sentDateTimeColumnName = EmailMessage::getColumnNameByAttribute('sentDateTime'); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Autoresponder'); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $queuedEmailsSelectPart = "sum(CASE WHEN {$quote}{$emailMessageTableName}{$quote}.{$quote}{$sentDateTimeColumnName}" . $quote . " = '0000-00-00 00:00:00' OR {$quote}{$emailMessageTableName}{$quote}" . ".{$quote}{$sentDateTimeColumnName}{$quote} IS NULL THEN 1 ELSE 0 END)"; // Not Coding Standard $sentEmailsSelectPart = "sum(CASE WHEN {$quote}{$emailMessageTableName}{$quote}.{$quote}{$sentDateTimeColumnName}" . $quote . " > '0000-00-00 00:00:00' THEN 1 ELSE 0 END)"; $uniqueOpensSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_OPEN); $uniqueClicksSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_CLICK); $bouncedSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_BOUNCE); $optedOutSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_UNSUBSCRIBE); static::addEmailMessageDayDateClause($selectQueryAdapter, $sentDateTimeColumnName); static::addEmailMessageFirstDayOfWeekDateClause($selectQueryAdapter, $sentDateTimeColumnName); static::addEmailMessageFirstDayOfMonthDateClause($selectQueryAdapter, $sentDateTimeColumnName); $selectQueryAdapter->addNonSpecificCountClause(); $selectQueryAdapter->addClauseByQueryString($queuedEmailsSelectPart, static::QUEUED); $selectQueryAdapter->addClauseByQueryString($sentEmailsSelectPart, static::SENT); $selectQueryAdapter->addClauseByQueryString("count((" . $uniqueOpensSelectPart . "))", static::UNIQUE_OPENS); $selectQueryAdapter->addClauseByQueryString("count((" . $uniqueClicksSelectPart . "))", static::UNIQUE_CLICKS); $selectQueryAdapter->addClauseByQueryString("count((" . $bouncedSelectPart . "))", static::BOUNCED); $selectQueryAdapter->addClauseByQueryString("count((" . $optedOutSelectPart . "))", static::UNSUBSCRIBED); $joinTablesAdapter->addLeftTableAndGetAliasName($autoresponderItemTableName, 'id', $autoresponderTableName, 'autoresponder_id'); $joinTablesAdapter->addLeftTableAndGetAliasName($emailMessageTableName, 'emailmessage_id', $autoresponderItemTableName, 'id'); $where = RedBeanModelDataProvider::makeWhere('Autoresponder', $searchAttributeData, $joinTablesAdapter); $sql = SQLQueryUtil::makeQuery($autoresponderTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where); return $sql; }
protected function resolveExtraSql(RedBeanModelJoinTablesQueryAdapter &$joinTablesAdapter, &$where) { $user = Yii::app()->user->userModel; $quote = DatabaseCompatibilityUtil::getQuote(); if ($where != '') { $where .= ' and '; } $modelClassName = $this->modelClassName; $starredClassName = StarredUtil::getStarredModelClassName($modelClassName); $starredTableName = $starredClassName::getTableName(); $modelIdColumnName = $modelClassName::getTableName() . '_id'; $baseStarredTableName = BaseStarredModel::getTableName(); $baseStarredColumnName = $baseStarredTableName . '_id'; $starredTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($starredTableName, 'id', null, $modelIdColumnName); $baseStarredTableAliasName = $joinTablesAdapter->addFromTableAndGetAliasName($baseStarredTableName, $baseStarredColumnName, $starredTableAliasName); $where .= "{$quote}{$baseStarredTableAliasName}{$quote}.{$quote}_user_id{$quote} = {$user->id}"; }
public static function makeQuery($tableName, RedBeanModelSelectQueryAdapter $selectQueryAdapter, RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter, $offset = null, $count = null, $where = null, $orderBy = null, $groupBy = null) { assert('is_string($tableName) && $tableName != ""'); assert('$offset === null || is_integer($offset) && $offset >= 0'); assert('$count === null || is_integer($count) && $count >= 1'); assert('$where === null || is_string ($where) && $where != ""'); assert('$orderBy === null || is_string ($orderBy) && $orderBy != ""'); assert('$groupBy === null || is_string ($groupBy) && $groupBy != ""'); $quote = DatabaseCompatibilityUtil::getQuote(); $sql = $selectQueryAdapter->getSelect(); $sql .= "from "; //Added ( ) around from tables to ensure precedence over joins. $joinFromPart = $joinTablesAdapter->getJoinFromQueryPart(); if ($joinFromPart !== null) { $sql .= "("; $sql .= "{$quote}{$tableName}{$quote}"; $sql .= ", {$joinFromPart}) "; } else { $sql .= "{$quote}{$tableName}{$quote}"; $sql .= ' '; } $sql .= $joinTablesAdapter->getJoinQueryPart(); $joinWherePart = $joinTablesAdapter->getJoinWhereQueryPart(); if ($where !== null) { $sql .= "where {$where}"; if ($joinWherePart != null) { $sql .= " and {$joinWherePart}"; } } elseif ($joinWherePart != null) { $sql .= " where {$joinWherePart}"; } if ($groupBy !== null) { $sql .= " group by {$groupBy}"; } if ($orderBy !== null) { $sql .= " order by {$orderBy}"; } if ($count !== null) { $sql .= " limit {$count}"; } if ($offset !== null) { $sql .= " offset {$offset}"; } return $sql; }
/** * For a give Contact name, run a partial search by * full name and retrieve contact models. * */ public static function getContactsByPartialFullName($partialName, $pageSize, $stateMetadataAdapterClassName = null) { assert('is_string($partialName)'); assert('is_int($pageSize)'); assert('$stateMetadataAdapterClassName == null || is_string($stateMetadataAdapterClassName)'); $personTableName = RedBeanModel::getTableName('Person'); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Contact'); if (!$joinTablesAdapter->isTableInFromTables('person')) { $joinTablesAdapter->addFromTableAndGetAliasName($personTableName, "{$personTableName}_id"); } $metadata = array('clauses' => array(), 'structure' => ''); if ($stateMetadataAdapterClassName != null) { $stateMetadataAdapter = new $stateMetadataAdapterClassName($metadata); $metadata = $stateMetadataAdapter->getAdaptedDataProviderMetadata(); $metadata['structure'] = '(' . $metadata['structure'] . ')'; } $where = RedBeanModelDataProvider::makeWhere('Contact', $metadata, $joinTablesAdapter); if ($where != null) { $where .= 'and'; } $where .= self::getWherePartForPartialNameSearchByPartialName($partialName); return Contact::getSubset($joinTablesAdapter, null, $pageSize, $where, "person.firstname, person.lastname"); }
/** * Query a model's table by attributeName to get the count of attribute values. * An example usage is if you want to know how many records have a certain contact state for all states. * @param $filterByAttributeName - string identifying attribute that should be filtered on. * @param $filterByAttributeValue - string of value to filter the attribute by. * @return array of atributeValue / count pairings. */ public static function getCountData($modelClassName, $attributeName, $filterByAttributeName = null, $filterByAttributeValue = null) { assert('($filterByAttributeName == null && $filterByAttributeValue == null) || ($filterByAttributeName != null && $filterByAttributeValue != null)'); $model = new $modelClassName(); $tableName = RedBeanModel::getTableName($modelClassName); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter(); if ($model->isRelation($attributeName) && $model->getRelationType($attributeName) == RedBeanModel::HAS_MANY) { assert('$attributeName == "values"'); //until we expand support on this method. $relationModelClassName = $model->getRelationModelClassName($attributeName); $attributeTableName = RedBeanModel::getTableName($relationModelClassName); $columnName = 'value'; $relationTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($attributeTableName, 'id', $tableName, $tableName . '_id'); } else { $attributeTableName = $tableName; $columnName = $model->getColumnNameByAttribute($attributeName); } $where = null; if ($filterByAttributeName != null) { $attributeModelClassName = $model->resolveAttributeModelClassName($filterByAttributeName); $filterByAttributeTableName = RedBeanModel::getTableName($attributeModelClassName); $filterByColumnName = $model->getColumnNameByAttribute($filterByAttributeName); $where = $filterByAttributeTableName . '.' . $filterByColumnName . '=' . $filterByAttributeValue; if ($filterByAttributeTableName != $tableName) { $joinTablesAdapter->addFromTableAndGetAliasName($filterByAttributeTableName, $filterByAttributeTableName . '_id', $tableName); } } $where = $where; $selectDistinct = false; $selectQueryAdapter->addClause($attributeTableName, $columnName, 'attribute'); $selectQueryAdapter->addCountClause($tableName, 'id', 'count'); $groupBy = static::getGroupBySqlPart($attributeTableName, $columnName); $sql = SQLQueryUtil::makeQuery($tableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where, null, $groupBy); return static::runQueryBySqlAndGetCountData($sql); }
/** * @param $onTableAliasName * @param $modelClassName * @param $attributeModelClassName * @return null|string * @throws NotSupportedException */ private function processLeftJoinsForAttributeThatIsCastedUp($onTableAliasName, $modelClassName, $attributeModelClassName) { assert('is_string($onTableAliasName)'); assert('is_string($modelClassName)'); assert('is_string($attributeModelClassName)'); $attributeTableName = $attributeModelClassName::getTableName($attributeModelClassName); $castedDownModelClassName = $modelClassName; while (get_parent_class($modelClassName) != $attributeModelClassName && get_parent_class($modelClassName) != 'RedBeanModel') { $castedDownFurtherModelClassName = $castedDownModelClassName; $castedDownModelClassName = $modelClassName; $modelClassName = get_parent_class($modelClassName); if ($modelClassName::getCanHaveBean()) { $castedUpAttributeTableName = $modelClassName::getTableName($modelClassName); /** if ($castedDownModelClassName::getCanHaveBean()) { $resolvedTableJoinIdName = $castedDownModelClassName::getTableName($castedDownModelClassName); } elseif ($castedDownFurtherModelClassName::getCanHaveBean()) { $resolvedTableJoinIdName = $castedDownModelClassName::getTableName($castedDownFurtherModelClassName); } else { throw new NotSupportedException(); } * */ $onTableAliasName = $this->joinTablesAdapter->addLeftTableAndGetAliasName($castedUpAttributeTableName, self::resolveForeignKey($castedUpAttributeTableName), $onTableAliasName); //, //$resolvedTableJoinIdName); } } //Add left table if it is not already added $modelClassName = static::resolveModelClassNameThatCanHaveTable($modelClassName, $castedDownModelClassName); $onTableAliasName = $this->joinTablesAdapter->addLeftTableAndGetAliasName($attributeTableName, self::resolveForeignKey($attributeTableName), $onTableAliasName); //, //$modelClassName::getTableName($modelClassName)); return $onTableAliasName; }
public function testAddLeftTableAndGetAliasNameWithSpecifiedOnTableAliasName() { $quote = DatabaseCompatibilityUtil::getQuote(); $adapter = new RedBeanModelJoinTablesQueryAdapter('QueryFromModel'); $alias = $adapter->addLeftTableAndGetAliasName('zz', 'joinid'); $this->assertEquals('zz', $alias); $this->assertEquals(0, $adapter->getFromTableJoinCount()); $this->assertEquals(1, $adapter->getLeftTableJoinCount()); $fromPart = $adapter->getJoinFromQueryPart(); $joinPart = $adapter->getJoinQueryPart(); $wherePart = $adapter->getJoinWhereQueryPart(); $compareFromPart = null; $compareJoinPart = "left join {$quote}zz{$quote} "; $compareJoinPart .= "on {$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}joinid{$quote} "; $compareWherePart = null; $this->assertEquals($compareFromPart, $fromPart); $this->assertEquals($compareJoinPart, $joinPart); $this->assertEquals($compareWherePart, $wherePart); //Now add a specified onTableAliasName $alias = $adapter->addLeftTableAndGetAliasName('xyz', 'ajoinid', 'zz'); $this->assertEquals('xyz', $alias); $this->assertEquals(0, $adapter->getFromTableJoinCount()); $this->assertEquals(2, $adapter->getLeftTableJoinCount()); $fromPart = $adapter->getJoinFromQueryPart(); $joinPart = $adapter->getJoinQueryPart(); $wherePart = $adapter->getJoinWhereQueryPart(); $compareFromPart = null; $compareJoinPart = "left join {$quote}zz{$quote} "; $compareJoinPart .= "on {$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}joinid{$quote} "; $compareJoinPart .= "left join {$quote}xyz{$quote} "; $compareJoinPart .= "on {$quote}xyz{$quote}.{$quote}id{$quote} = {$quote}zz{$quote}.{$quote}ajoinid{$quote} "; $compareWherePart = null; $this->assertEquals($compareFromPart, $fromPart); $this->assertEquals($compareJoinPart, $joinPart); $this->assertEquals($compareWherePart, $wherePart); }
/** * @param string $type * @return string */ protected static function makeUserLeaderboardCountSqlQuery($type) { assert('is_string($type)'); $quote = DatabaseCompatibilityUtil::getQuote(); $where = '_user.hidefromleaderboard is null OR _user.hidefromleaderboard = 0'; $selectDistinct = true; $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('GamePointTransaction'); static::resolveLeaderboardWhereClausesByType($type, $where); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $selectQueryAdapter->addCountClause('_user', 'id'); $joinTablesAdapter->addFromTableAndGetAliasName('gamepoint', 'gamepoint_id', 'gamepointtransaction'); $joinTablesAdapter->addFromTableAndGetAliasName('permitable', 'person_item_id', 'gamepoint', 'item_id'); $joinTablesAdapter->addFromTableAndGetAliasName('_user', 'id', 'permitable', 'permitable_id'); $sql = SQLQueryUtil::makeQuery('gamepointtransaction', $selectQueryAdapter, $joinTablesAdapter, null, null, $where); return $sql; }
/** * After work on reporting branch, this test was breaking. So we switched the test to show left joins as * 0. This might be ok, just depends how you are using the adapter. Normally you would add more * filters in which case a join would be added if you are filtering on something specific with industry * @depends testResolveShouldAddFromTableWithAttributeOnModelSameTable */ public function testResolveShouldAddFromTableWithOwnedCustomFieldAttribute() { $adapter = new RedBeanModelAttributeToDataProviderAdapter('Account', 'industry'); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Account'); $builder = new ModelWhereAndJoinBuilder($adapter, $joinTablesAdapter, true); $tableAliasName = $builder->resolveJoins(); $this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount()); $this->assertEquals(0, $joinTablesAdapter->getLeftTableJoinCount()); $adapter = new RedBeanModelAttributeToDataProviderAdapter('Account', 'industry'); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Account'); $builder = new ModelWhereAndJoinBuilder($adapter, $joinTablesAdapter, false); $tableAliasName = $builder->resolveJoins(); $this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount()); $this->assertEquals(0, $joinTablesAdapter->getLeftTableJoinCount()); }
/** * @param int $type * @return string */ public static function resolveAutoresponderTypeSubQuery($type) { assert('is_int($type)'); $quote = DatabaseCompatibilityUtil::getQuote(); $where = null; $selectDistinct = true; $autoresponderItemActivityTableName = AutoresponderItemActivity::getTableName('AutoresponderItemActivity'); $emailMessageActivityTableName = EmailMessageActivity::getTableName('EmailMessageActivity'); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('AutoresponderItemActivity'); $selectQueryAdapter->addClauseByQueryString("autoresponder_id"); $joinTablesAdapter->addFromTableAndGetAliasName($emailMessageActivityTableName, 'emailmessageactivity_id', $autoresponderItemActivityTableName); $where = "type = " . $type . " and {$quote}{$autoresponderItemActivityTableName}{$quote}" . ".autoresponderitem_id = autoresponderitem.id"; $sql = SQLQueryUtil::makeQuery($autoresponderItemActivityTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where); return $sql; }
public function testInferredRelationModelAttributeWithCastingHintToNotCastDownSoFarWithItemAttribute() { $q = DatabaseCompatibilityUtil::getQuote(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Meeting'); $builder = new FiltersReportQueryBuilder($joinTablesAdapter, '1'); $filter = new FilterForReportForm('MeetingsModule', 'Meeting', Report::TYPE_ROWS_AND_COLUMNS); $filter->attributeIndexOrDerivedType = 'Account__activityItems__Inferred___ReadOptimization'; $content = $builder->makeQueryContent(array($filter)); $compareContent = "({$q}ownedsecurableitem{$q}.{$q}securableitem_id{$q} = (select securableitem_id " . "from {$q}account_read{$q} where {$q}securableitem_id{$q} = {$q}ownedsecurableitem" . "{$q}.{$q}securableitem_id{$q} and {$q}munge_id{$q} in ('U" . self::$superUserId . "', 'G" . self::$everyoneGroupId . "') limit 1))"; $this->assertEquals($compareContent, $content); $this->assertEquals(1, $joinTablesAdapter->getFromTableJoinCount()); $this->assertEquals(4, $joinTablesAdapter->getLeftTableJoinCount()); }
/** * @param User $user * @param RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter * @param $where * @param $selectDistinct * @throws NotSupportedException */ public static function resolveReadPermissionsOptimizationToSqlQuery(User $user, RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter, &$where, &$selectDistinct) { assert('$where == null || is_string($where)'); assert('is_bool($selectDistinct)'); $modelClassName = get_called_class(); $moduleClassName = $modelClassName::getModuleClassName(); //Currently only adds munge if the module is securable and this model supports it. if (static::hasReadPermissionsOptimization() && $moduleClassName != null && is_subclass_of($moduleClassName, 'SecurableModule')) { $permission = PermissionsUtil::getActualPermissionDataForReadByModuleNameForUser($moduleClassName); if (($permission == Permission::NONE || $permission == Permission::DENY) && !static::bypassReadPermissionsOptimizationToSqlQueryBasedOnWhere($where)) { $quote = DatabaseCompatibilityUtil::getQuote(); $modelAttributeToDataProviderAdapter = new OwnedSecurableItemIdToDataProviderAdapter($modelClassName, null); $builder = new ModelJoinBuilder($modelAttributeToDataProviderAdapter, $joinTablesAdapter); $ownedTableAliasName = $builder->resolveJoins(); $ownerColumnName = static::getForeignKeyName('OwnedSecurableItem', 'owner'); $mungeIds = AllPermissionsOptimizationUtil::getMungeIdsByUser($user); if ($where != null) { $where = '(' . $where . ') and '; } if (count($mungeIds) > 0 && $permission == Permission::NONE) { $extraOnQueryPart = " and {$quote}munge_id{$quote} in ('" . join("', '", $mungeIds) . "')"; $mungeTableName = ReadPermissionsOptimizationUtil::getMungeTableName($modelClassName); $mungeTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($mungeTableName, 'securableitem_id', $ownedTableAliasName, 'securableitem_id', $extraOnQueryPart); $where .= "({$quote}{$ownedTableAliasName}{$quote}.{$quote}{$ownerColumnName}{$quote} = {$user->id} OR "; // Not Coding Standard $where .= "{$quote}{$mungeTableName}{$quote}.{$quote}munge_id{$quote} IS NOT NULL)"; // Not Coding Standard $selectDistinct = true; //must use distinct since adding munge table query. } elseif ($permission == Permission::DENY) { $where .= "{$quote}{$ownedTableAliasName}{$quote}.{$quote}{$ownerColumnName}{$quote} = {$user->id}"; // Not Coding Standard } else { throw new NotSupportedException(); } } } }
/** * Override of the parent method using RedBean. * @param $category * @param $languageCode * @return array */ protected function loadMessagesFromDb($category, $languageCode) { assert('is_string($category)'); assert('is_string($languageCode)'); $sourceTableName = MessageSource::getTableName(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('MessageTranslation'); $joinTablesAdapter->addFromTableAndGetAliasName($sourceTableName, "{$sourceTableName}_id"); $where = " messagesource.`category` = '{$category}' AND" . " messagetranslation.`language` = '{$languageCode}' "; $beans = MessageTranslation::getSubset($joinTablesAdapter, null, null, $where); $messages = array(); foreach ($beans as $bean) { $messages[$bean->messagesource->source] = $bean->translation; } return $messages; }
public function testHasManyForHasManyBelongsToOnCastedUpModel() { $q = DatabaseCompatibilityUtil::getQuote(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('ReportModelTestItem9'); $builder = new GroupBysReportQueryBuilder($joinTablesAdapter); $groupBy = new GroupByForReportForm('ReportsTestModule', 'ReportModelTestItem9', Report::TYPE_SUMMATION); $groupBy->attributeIndexOrDerivedType = 'reportModelTestItem9s___owner__User'; $content = $builder->makeQueryContent(array($groupBy)); $compareContent = "{$q}_user{$q}.{$q}id{$q}"; $this->assertEquals($compareContent, $content); $leftTablesAndAliases = $joinTablesAdapter->getLeftTablesAndAliases(); $fromTablesAndAliases = $joinTablesAdapter->getFromTablesAndAliases(); $this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount()); $this->assertEquals(3, $joinTablesAdapter->getLeftTableJoinCount()); $this->assertEquals('reportmodeltestitem91', $leftTablesAndAliases[0]['tableAliasName']); $this->assertEquals('reportmodeltestitem9', $leftTablesAndAliases[0]['onTableAliasName']); $this->assertEquals('ownedsecurableitem', $leftTablesAndAliases[1]['tableAliasName']); $this->assertEquals('reportmodeltestitem91', $leftTablesAndAliases[1]['onTableAliasName']); $this->assertEquals('id', $leftTablesAndAliases[1]['tableJoinIdName']); $this->assertEquals('_user', $leftTablesAndAliases[2]['tableAliasName']); $this->assertEquals('ownedsecurableitem', $leftTablesAndAliases[2]['onTableAliasName']); }
/** * For both non related and related attributes, this method resolves whether a from join is needed. This occurs * for example if a model attribute is castedUp. And that attribute is a relation that needs to be joined in * order to search. Since that attribute is castedUp, the castedUp model needs to be from joined first. This * also applies if the attribute is not a relation and just a member on the castedUp model. In that scenario, * the castedUp model also needs to be joined. * * This methhod assumes if the attribute is not on the base model, that it is casted up not down from it. */ public static function resolveShouldAddFromTableAndGetAliasName(RedBeanModelAttributeToDataProviderAdapter $modelAttributeToDataProviderAdapter, RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter) { $attributeTableName = $modelAttributeToDataProviderAdapter->getAttributeTableName(); $tableAliasName = $attributeTableName; if ($modelAttributeToDataProviderAdapter->getModelClassName() == 'User' && $modelAttributeToDataProviderAdapter->getAttributeModelClassName() == 'Person') { $modelTableName = $modelAttributeToDataProviderAdapter->getModelTableName(); if (!$joinTablesAdapter->isTableInFromTables('person')) { $personTableName = $attributeTableName; $joinTablesAdapter->addFromTableAndGetAliasName($personTableName, "{$personTableName}_id", $modelTableName); } } elseif ($modelAttributeToDataProviderAdapter->getAttributeModelClassName() != $modelAttributeToDataProviderAdapter->getModelClassName()) { $modelClassName = $modelAttributeToDataProviderAdapter->getModelClassName(); while (get_parent_class($modelClassName) != $modelAttributeToDataProviderAdapter->getAttributeModelClassName()) { $castedDownModelClassName = $modelClassName; $modelClassName = get_parent_class($modelClassName); $castedUpAttributeTableName = $modelClassName::getTableName($modelClassName); if (!$joinTablesAdapter->isTableInFromTables($castedUpAttributeTableName)) { $joinTablesAdapter->addFromTableAndGetAliasName($castedUpAttributeTableName, "{$castedUpAttributeTableName}_id", $castedDownModelClassName::getTableName($castedDownModelClassName)); } } if (!$joinTablesAdapter->isTableInFromTables($attributeTableName)) { $tableAliasName = $joinTablesAdapter->addFromTableAndGetAliasName($attributeTableName, "{$attributeTableName}_id", $modelClassName::getTableName($modelClassName)); } } return $tableAliasName; }
public function testDisplayCalculationInferredRelationModelAttributeWithCastingHintToNotCastDowButAlsoWithFullCastDown() { $q = DatabaseCompatibilityUtil::getQuote(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Meeting'); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter(); $builder = new DisplayAttributesReportQueryBuilder($joinTablesAdapter, $selectQueryAdapter); $displayAttribute = new DisplayAttributeForReportForm('MeetingsModule', 'Meeting', Report::TYPE_SUMMATION); $displayAttribute->attributeIndexOrDerivedType = 'Account__activityItems__Inferred___createdDateTime__Maximum'; $displayAttribute2 = new DisplayAttributeForReportForm('MeetingsModule', 'Meeting', Report::TYPE_SUMMATION); $displayAttribute2->attributeIndexOrDerivedType = 'Account__activityItems__Inferred___name'; $content = $builder->makeQueryContent(array($displayAttribute, $displayAttribute2)); $compareContent = "select max({$q}item{$q}.{$q}createddatetime{$q}) col0, " . "{$q}account{$q}.{$q}id{$q} accountid "; $this->assertEquals($compareContent, $content); $leftTablesAndAliases = $joinTablesAdapter->getLeftTablesAndAliases(); $fromTablesAndAliases = $joinTablesAdapter->getFromTablesAndAliases(); $this->assertEquals(1, $joinTablesAdapter->getFromTableJoinCount()); $this->assertEquals(5, $joinTablesAdapter->getLeftTableJoinCount()); $this->assertEquals('activity_item', $leftTablesAndAliases[0]['tableAliasName']); $this->assertEquals('activity', $leftTablesAndAliases[0]['onTableAliasName']); $this->assertEquals('item', $leftTablesAndAliases[1]['tableAliasName']); $this->assertEquals('activity_item', $leftTablesAndAliases[1]['onTableAliasName']); $this->assertEquals('securableitem', $leftTablesAndAliases[2]['tableAliasName']); $this->assertEquals('item', $leftTablesAndAliases[2]['onTableAliasName']); $this->assertEquals('ownedsecurableitem', $leftTablesAndAliases[3]['tableAliasName']); $this->assertEquals('securableitem', $leftTablesAndAliases[3]['onTableAliasName']); $this->assertEquals('account', $leftTablesAndAliases[4]['tableAliasName']); $this->assertEquals('ownedsecurableitem', $leftTablesAndAliases[4]['onTableAliasName']); }
public function testCreatedDateTimeFilterWithInferredRelationModelAttributeAsDisplay() { $q = DatabaseCompatibilityUtil::getQuote(); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Meeting'); $builder = new DisplayAttributesReportQueryBuilder($joinTablesAdapter, $selectQueryAdapter, Report::CURRENCY_CONVERSION_TYPE_ACTUAL); $displayAttribute = new DisplayAttributeForReportForm('MeetingsModule', 'Meeting', Report::TYPE_ROWS_AND_COLUMNS); $displayAttribute->attributeIndexOrDerivedType = 'Account__activityItems__Inferred___name'; $builder->makeQueryContent(array($displayAttribute)); $builder = new FiltersReportQueryBuilder($joinTablesAdapter, '1'); $filter = new FilterForReportForm('MeetingsModule', 'Meeting', Report::TYPE_ROWS_AND_COLUMNS); $filter->attributeIndexOrDerivedType = 'createdDateTime'; $filter->valueType = MixedDateTypesSearchFormAttributeMappingRules::TYPE_BETWEEN; $filter->value = '1991-05-05'; $filter->secondValue = '1991-06-05'; $filter->availableAtRunTime = true; $content = $builder->makeQueryContent(array($filter)); $compareContent = "((({$q}item1{$q}.{$q}createddatetime{$q} >= '1991-05-05 00:00:00') " . "and ({$q}item1{$q}.{$q}createddatetime{$q} <= '1991-06-05 23:59:59')))"; $this->assertEquals($compareContent, $content); $this->assertEquals(4, $joinTablesAdapter->getFromTableJoinCount()); $this->assertEquals(5, $joinTablesAdapter->getLeftTableJoinCount()); $builder = new FiltersReportQueryBuilder($joinTablesAdapter, 'not 1'); $content = $builder->makeQueryContent(array($filter)); $compareContent = "(not (({$q}item1{$q}.{$q}createddatetime{$q} >= '1991-05-05 00:00:00') " . "and ({$q}item1{$q}.{$q}createddatetime{$q} <= '1991-06-05 23:59:59')))"; $this->assertEquals($compareContent, $content); }
/** * @param array $searchAttributeData * @param string $groupBy * @return string */ protected static function makeAutorespondersSqlQuery($searchAttributeData, $groupBy) { $quote = DatabaseCompatibilityUtil::getQuote(); $where = null; $selectDistinct = false; $marketingListTableName = MarketingList::getTableName(); $autoresponderTableName = Autoresponder::getTableName(); $autoresponderItemTableName = AutoresponderItem::getTableName(); $emailMessageTableName = EmailMessage::getTableName(); $sentDateTimeColumnName = EmailMessage::getColumnNameByAttribute('sentDateTime'); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Autoresponder'); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $uniqueOpensSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_OPEN); $uniqueClicksSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_CLICK); static::addEmailMessageDayDateClause($selectQueryAdapter, $sentDateTimeColumnName); static::addEmailMessageFirstDayOfWeekDateClause($selectQueryAdapter, $sentDateTimeColumnName); static::addEmailMessageFirstDayOfMonthDateClause($selectQueryAdapter, $sentDateTimeColumnName); $selectQueryAdapter->addNonSpecificCountClause(); $selectQueryAdapter->addClauseByQueryString("count((" . $uniqueOpensSelectPart . "))", static::UNIQUE_OPENS); $selectQueryAdapter->addClauseByQueryString("count((" . $uniqueClicksSelectPart . "))", static::UNIQUE_CLICKS); $joinTablesAdapter->addFromTableAndGetAliasName($marketingListTableName, 'marketinglist_id'); $joinTablesAdapter->addLeftTableAndGetAliasName($autoresponderItemTableName, 'id', $autoresponderTableName, 'autoresponder_id'); $joinTablesAdapter->addLeftTableAndGetAliasName($emailMessageTableName, 'emailmessage_id', $autoresponderItemTableName, 'id'); $where = RedBeanModelDataProvider::makeWhere('Autoresponder', $searchAttributeData, $joinTablesAdapter); MarketingList::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct); $sql = SQLQueryUtil::makeQuery($autoresponderTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where, null, $groupBy); return $sql; }
public function testInferredRelationModelAttributeWithCastingHintToNotCastDowButAlsoWithFullCastDown() { $q = DatabaseCompatibilityUtil::getQuote(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Meeting'); $builder = new OrderBysReportQueryBuilder($joinTablesAdapter); $orderBy = new OrderByForReportForm('MeetingsModule', 'Meeting', Report::TYPE_ROWS_AND_COLUMNS); $orderBy->attributeIndexOrDerivedType = 'Account__activityItems__Inferred___createdDateTime'; $orderBy2 = new OrderByForReportForm('MeetingsModule', 'Meeting', Report::TYPE_ROWS_AND_COLUMNS); $orderBy2->attributeIndexOrDerivedType = 'Account__activityItems__Inferred___name'; $content = $builder->makeQueryContent(array($orderBy, $orderBy2)); $compareContent = "{$q}item{$q}.{$q}createddatetime{$q} asc, " . "{$q}account{$q}.{$q}name{$q} asc"; $this->assertEquals($compareContent, $content); $leftTablesAndAliases = $joinTablesAdapter->getLeftTablesAndAliases(); $fromTablesAndAliases = $joinTablesAdapter->getFromTablesAndAliases(); $this->assertEquals(1, $joinTablesAdapter->getFromTableJoinCount()); $this->assertEquals(5, $joinTablesAdapter->getLeftTableJoinCount()); $this->assertEquals('activity_item', $leftTablesAndAliases[0]['tableAliasName']); $this->assertEquals('activity', $leftTablesAndAliases[0]['onTableAliasName']); $this->assertEquals('item', $leftTablesAndAliases[1]['tableAliasName']); $this->assertEquals('activity_item', $leftTablesAndAliases[1]['onTableAliasName']); $this->assertEquals('securableitem', $leftTablesAndAliases[2]['tableAliasName']); $this->assertEquals('item', $leftTablesAndAliases[2]['onTableAliasName']); $this->assertEquals('ownedsecurableitem', $leftTablesAndAliases[3]['tableAliasName']); $this->assertEquals('securableitem', $leftTablesAndAliases[3]['onTableAliasName']); $this->assertEquals('account', $leftTablesAndAliases[4]['tableAliasName']); $this->assertEquals('ownedsecurableitem', $leftTablesAndAliases[4]['onTableAliasName']); }
/** * Tag cloud should utilize a sub-query as part of its query. */ public function testASingleTagCloudAttributeWithEqualsOperator() { $q = DatabaseCompatibilityUtil::getQuote(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('ReportModelTestItem'); $builder = new FiltersReportQueryBuilder($joinTablesAdapter, '1'); $filter = new FilterForReportForm('ReportsTestModule', 'ReportModelTestItem', Report::TYPE_ROWS_AND_COLUMNS); $filter->attributeIndexOrDerivedType = 'tagCloud'; $filter->value = 'a'; $filter->operator = OperatorRules::TYPE_EQUALS; $content = $builder->makeQueryContent(array($filter)); $leftTablesAndAliases = $joinTablesAdapter->getLeftTablesAndAliases(); $fromTablesAndAliases = $joinTablesAdapter->getFromTablesAndAliases(); $compareContent = "((1 = (select 1 from {$q}customfieldvalue{$q} customfieldvalue where " . "{$q}customfieldvalue{$q}.{$q}multiplevaluescustomfield_id{$q} = {$q}" . "multiplevaluescustomfield{$q}.id and {$q}customfieldvalue{$q}.{$q}value{$q}" . " = 'a' limit 1)))"; $this->assertEquals($compareContent, $content); $this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount()); $this->assertEquals(1, $joinTablesAdapter->getLeftTableJoinCount()); $this->assertEquals('multiplevaluescustomfield', $leftTablesAndAliases[0]['tableAliasName']); $this->assertEquals('reportmodeltestitem', $leftTablesAndAliases[0]['onTableAliasName']); }