/**
* 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']);
}
