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}"; }
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}"; }
/** * @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; }
/** * 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 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 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(); } } } }
/** * @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; }
/** * @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; }
protected static function resolveJoinsForRelatedAttributeAndGetRelationAttributeTableAliasName(RedBeanModelAttributeToDataProviderAdapter $modelAttributeToDataProviderAdapter, RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter, $onTableAliasName) { assert('$modelAttributeToDataProviderAdapter->getRelationType() != RedBeanModel::MANY_MANY'); assert('is_string($onTableAliasName)'); if ($modelAttributeToDataProviderAdapter->getRelationType() == RedBeanModel::HAS_MANY || $modelAttributeToDataProviderAdapter->getRelationType() == RedBeanModel::HAS_MANY_BELONGS_TO) { $onTableJoinIdName = 'id'; $tableJoinIdName = $onTableAliasName . '_id'; //HAS_MANY have the potential to produce more than one row per model, so we need //to signal the query to be distinct. if ($modelAttributeToDataProviderAdapter->getRelationType() == RedBeanModel::HAS_MANY) { $joinTablesAdapter->setSelectDistinctToTrue(); } } elseif ($modelAttributeToDataProviderAdapter->getRelationType() == RedBeanModel::HAS_ONE_BELONGS_TO) { $tableJoinIdName = $onTableAliasName . '_id'; $onTableJoinIdName = 'id'; } else { $onTableJoinIdName = $modelAttributeToDataProviderAdapter->getColumnName(); $tableJoinIdName = 'id'; } $relationTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($modelAttributeToDataProviderAdapter->getRelationTableName(), $onTableJoinIdName, $onTableAliasName, $tableJoinIdName); $relationAttributeTableAliasName = $relationTableAliasName; //the second left join check being performed is if you //are in a contact filtering on related account email as an example. if ($modelAttributeToDataProviderAdapter->getRelatedAttributeModelClassName() != $modelAttributeToDataProviderAdapter->getRelationModelClassName()) { $relationAttributeTableName = $modelAttributeToDataProviderAdapter->getRelatedAttributeTableName(); //Handling special scenario for casted down Person. Todo: Automatically determine a //casted down scenario instead of specifically looking for Person. if ($modelAttributeToDataProviderAdapter->getRelatedAttributeModelClassName() == 'Person') { $onTableJoinIdName = "{$relationAttributeTableName}_id"; } elseif (get_parent_class($modelAttributeToDataProviderAdapter->getRelationModelClassName()) == $modelAttributeToDataProviderAdapter->getRelatedAttributeModelClassName()) { $onTableJoinIdName = "{$relationAttributeTableName}_id"; } else { $onTableJoinIdName = "{$modelAttributeToDataProviderAdapter->getRelatedAttributeColumnName()}" . "_{$relationAttributeTableName}_id"; } $relationAttributeTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($relationAttributeTableName, $onTableJoinIdName, $relationTableAliasName); } return $relationAttributeTableAliasName; }