/** * @param $modelClassName * @param $attributeModelClassName * @return string * @throws NotSupportedException */ private function processFromJoinsForAttributeThatIsCastedUp($modelClassName, $attributeModelClassName) { assert('is_string($modelClassName)'); assert('is_string($attributeModelClassName)'); $attributeTableName = $attributeModelClassName::getTableName($attributeModelClassName); $tableAliasName = $attributeTableName; $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 (!$this->joinTablesAdapter->isTableInFromTables($castedUpAttributeTableName)) { if ($castedDownModelClassName::getCanHaveBean()) { $onTableAliasName = $castedDownModelClassName::getTableName($castedDownModelClassName); } elseif ($castedDownFurtherModelClassName::getCanHaveBean()) { $onTableAliasName = $castedDownModelClassName::getTableName($castedDownFurtherModelClassName); } else { throw new NotSupportedException(); } $onTableAliasName = $this->joinTablesAdapter->addFromTableAndGetAliasName($castedUpAttributeTableName, self::resolveForeignKey($castedUpAttributeTableName), $onTableAliasName); } } } if (!$this->joinTablesAdapter->isTableInFromTables($attributeTableName)) { $modelClassName = static::resolveModelClassNameThatCanHaveTable($modelClassName, $castedDownModelClassName); $tableAliasName = $this->joinTablesAdapter->addFromTableAndGetAliasName($attributeTableName, self::resolveForeignKey($attributeTableName), $modelClassName::getTableName($modelClassName)); } return $tableAliasName; }
/** * 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"); }
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 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}"; }
/** * 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); }
public function testAddFromTable() { $quote = DatabaseCompatibilityUtil::getQuote(); $adapter = new RedBeanModelJoinTablesQueryAdapter('QueryFromModel'); $alias = $adapter->addFromTableAndGetAliasName('zz', 'somejoinid'); $this->assertEquals(1, $adapter->getFromTableJoinCount()); $this->assertEquals(0, $adapter->getLeftTableJoinCount()); $this->assertEquals('zz', $alias); $fromPart = $adapter->getJoinFromQueryPart(); $joinPart = $adapter->getJoinQueryPart(); $wherePart = $adapter->getJoinWhereQueryPart(); $compareFromPart = "{$quote}zz{$quote}"; $compareJoinPart = null; $compareWherePart = "{$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote}"; $this->assertEquals($compareFromPart, $fromPart); $this->assertEquals($compareJoinPart, $joinPart); $this->assertEquals($compareWherePart, $wherePart); //Add second from table $alias = $adapter->addFromTableAndGetAliasName('a', 'somejoinid'); $this->assertEquals(2, $adapter->getFromTableJoinCount()); $this->assertEquals(0, $adapter->getLeftTableJoinCount()); $this->assertEquals('a', $alias); $fromPart = $adapter->getJoinFromQueryPart(); $joinPart = $adapter->getJoinQueryPart(); $wherePart = $adapter->getJoinWhereQueryPart(); $compareFromPart = "{$quote}zz{$quote}, {$quote}a{$quote}"; $compareJoinPart = null; $compareWherePart = "{$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and "; $compareWherePart .= "{$quote}a{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote}"; $this->assertEquals($compareFromPart, $fromPart); $this->assertEquals($compareJoinPart, $joinPart); $this->assertEquals($compareWherePart, $wherePart); //add third from table that was a table alread added $alias = $adapter->addFromTableAndGetAliasName('zz', 'somejoinid'); $this->assertEquals(3, $adapter->getFromTableJoinCount()); $this->assertEquals(0, $adapter->getLeftTableJoinCount()); $this->assertEquals('zz1', $alias); $fromPart = $adapter->getJoinFromQueryPart(); $joinPart = $adapter->getJoinQueryPart(); $wherePart = $adapter->getJoinWhereQueryPart(); $compareFromPart = "{$quote}zz{$quote}, {$quote}a{$quote}, {$quote}zz{$quote} zz1"; $compareJoinPart = null; $compareWherePart = "{$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and "; $compareWherePart .= "{$quote}a{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and "; $compareWherePart .= "{$quote}zz1{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote}"; $this->assertEquals($compareFromPart, $fromPart); $this->assertEquals($compareJoinPart, $joinPart); $this->assertEquals($compareWherePart, $wherePart); //Add a fourth table, that is a left join. $alias = $adapter->addLeftTableAndGetAliasName('z', 'joinid'); $this->assertEquals(3, $adapter->getFromTableJoinCount()); $this->assertEquals(1, $adapter->getLeftTableJoinCount()); $this->assertEquals('z', $alias); $fromPart = $adapter->getJoinFromQueryPart(); $joinPart = $adapter->getJoinQueryPart(); $wherePart = $adapter->getJoinWhereQueryPart(); $compareFromPart = "{$quote}zz{$quote}, {$quote}a{$quote}, {$quote}zz{$quote} zz1"; $compareJoinPart = "left join {$quote}z{$quote} "; $compareJoinPart .= "on {$quote}z{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}joinid{$quote} "; $compareWherePart = "{$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and "; $compareWherePart .= "{$quote}a{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and "; $compareWherePart .= "{$quote}zz1{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote}"; $this->assertEquals($compareFromPart, $fromPart); $this->assertEquals($compareJoinPart, $joinPart); $this->assertEquals($compareWherePart, $wherePart); //Add a fifth table, that is a left join of an existing from table. $alias = $adapter->addLeftTableAndGetAliasName('queryfrommodel', 'joinid'); $this->assertEquals('queryfrommodel1', $alias); $this->assertEquals(3, $adapter->getFromTableJoinCount()); $this->assertEquals(2, $adapter->getLeftTableJoinCount()); $fromPart = $adapter->getJoinFromQueryPart(); $joinPart = $adapter->getJoinQueryPart(); $wherePart = $adapter->getJoinWhereQueryPart(); $compareFromPart = "{$quote}zz{$quote}, {$quote}a{$quote}, {$quote}zz{$quote} zz1"; $compareJoinPart = "left join {$quote}z{$quote}"; $compareJoinPart .= " on {$quote}z{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}joinid{$quote}"; $compareJoinPart .= " left join {$quote}queryfrommodel{$quote} queryfrommodel1"; $compareJoinPart .= " on {$quote}queryfrommodel1{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}joinid{$quote} "; $compareWherePart = "{$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and "; $compareWherePart .= "{$quote}a{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and "; $compareWherePart .= "{$quote}zz1{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote}"; $this->assertEquals($compareFromPart, $fromPart); $this->assertEquals($compareJoinPart, $joinPart); $this->assertEquals($compareWherePart, $wherePart); //Add a sixth table that exists already to demonstrate the alias working for a third table. //You must use a different join id so that it creates a new alias. using cjoinid instead of joinid $alias = $adapter->addLeftTableAndGetAliasName('queryfrommodel', 'cjoinid'); $this->assertEquals('queryfrommodel2', $alias); //If we just use joinid, then it will not create a new alias. $alias = $adapter->addLeftTableAndGetAliasName('queryfrommodel', 'joinid'); $this->assertEquals('queryfrommodel1', $alias); $alias = $adapter->addLeftTableAndGetAliasName('queryfrommodel', 'cjoinid'); $this->assertEquals('queryfrommodel2', $alias); }
/** * @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; }
/** * @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; }
/** * @param array $searchAttributeData * @param string $groupBy * @return string */ protected static function makeAutorespondersSqlQuery($searchAttributeData, $groupBy) { $quote = DatabaseCompatibilityUtil::getQuote(); $where = null; $selectDistinct = false; $itemTableName = Item::getTableName(); $marketingListTableName = MarketingList::getTableName(); $autoresponderTableName = Autoresponder::getTableName(); $autoresponderItemTableName = AutoresponderItem::getTableName(); $emailMessageTableName = EmailMessage::getTableName(); $sentDateTimeColumnName = EmailMessage::getColumnNameByAttribute('sentDateTime'); $createdDateTimeColumnName = Item::getColumnNameByAttribute('createdDateTime'); $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); $selectQueryAdapter->addDayDateClause($itemTableName, $createdDateTimeColumnName, static::DAY_DATE); $selectQueryAdapter->addFirstDayOfWeekDateClause($itemTableName, $createdDateTimeColumnName, static::FIRST_DAY_OF_WEEK_DATE); $selectQueryAdapter->addFirstDayOfMonthDateClause($itemTableName, $createdDateTimeColumnName, static::FIRST_DAY_OF_MONTH_DATE); $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->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; }
/** * 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; }
/** * @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; }
/** * 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 testGetAlreadyFromJoinedTableAliasName() { $adapter = new RedBeanModelJoinTablesQueryAdapter('QueryFromModel'); $this->assertNull($adapter->GetAlreadyFromJoinedTableAliasName('zz')); $alias = $adapter->addFromTableAndGetAliasName('z', 'joinid'); $this->assertEquals($alias, $adapter->getAlreadyFromJoinedTableAliasName('z')); }