public function testAddCountClause()
{
$quote = DatabaseCompatibilityUtil::getQuote();
$adapter = new RedBeanModelSelectQueryAdapter();
$this->assertEquals(0, $adapter->getClausesCount());
$adapter->addCountClause('a', 'b', 'c');
$this->assertEquals(1, $adapter->getClausesCount());
$compareString = "select count({$quote}a{$quote}.{$quote}b{$quote}) c ";
$this->assertEquals($compareString, $adapter->getSelect());
$adapter = new RedBeanModelSelectQueryAdapter(true);
$this->assertEquals(0, $adapter->getClausesCount());
$adapter->addCountClause('a', 'b', 'c');
$this->assertEquals(1, $adapter->getClausesCount());
$compareString = "select count(distinct {$quote}a{$quote}.{$quote}b{$quote}) c ";
$this->assertEquals($compareString, $adapter->getSelect());
}
/**
* 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 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 boolean $selectCount If true then make this a count query. If false, select ids from rows.
* @param array $quotedExtraSelectColumnNameAndAliases - extra columns to select.
* @return string - sql statement.
*/
public static function makeSubsetOrCountSqlQuery($tableName, RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter, $offset = null, $count = null, $where = null, $orderBy = null, $selectCount = false, $selectDistinct = false, array $quotedExtraSelectColumnNameAndAliases = array())
{
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('is_bool($selectCount)');
assert('is_bool($selectDistinct)');
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct);
if ($selectCount) {
$selectQueryAdapter->addCountClause($tableName);
} else {
$selectQueryAdapter->addClause($tableName, 'id', 'id');
}
foreach ($quotedExtraSelectColumnNameAndAliases as $columnName => $columnAlias) {
$selectQueryAdapter->addClauseWithColumnNameOnlyAndNoEnclosure($columnName, $columnAlias);
}
return SQLQueryUtil::makeQuery($tableName, $selectQueryAdapter, $joinTablesAdapter, $offset, $count, $where, $orderBy);
}
/**
* @param RedBeanModelSelectQueryAdapter $selectQueryAdapter
* @param string $attribute
* @param string $tableName
* @param string $columnName
* @param string $columnAliasName
* @param null|string $queryStringExtraPart
*/
public function resolveDisplayAttributeTypeAndAddSelectClause(RedBeanModelSelectQueryAdapter $selectQueryAdapter, $attribute, $tableName, $columnName, $columnAliasName, $queryStringExtraPart = null)
{
assert('is_string($attribute)');
assert('is_string($columnAliasName)');
assert('is_string($queryStringExtraPart) || $queryStringExtraPart == null');
$type = $this->getDisplayAttributeForMakingViaSelectType($attribute);
if ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_COUNT) {
$selectQueryAdapter->addCountClause($tableName, $columnName, $columnAliasName);
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_SUMMMATION) {
$selectQueryAdapter->addSummationClause($tableName, $columnName, $columnAliasName, $queryStringExtraPart);
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_AVERAGE) {
$selectQueryAdapter->addAverageClause($tableName, $columnName, $columnAliasName, $queryStringExtraPart);
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_MINIMUM) {
$selectQueryAdapter->addMinimumClause($tableName, $columnName, $columnAliasName, $queryStringExtraPart);
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_MAXIMUM) {
$selectQueryAdapter->addMaximumClause($tableName, $columnName, $columnAliasName, $queryStringExtraPart);
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_DAY) {
$selectQueryAdapter->addDayClause($tableName, $columnName, $columnAliasName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute));
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_WEEK) {
$selectQueryAdapter->addWeekClause($tableName, $columnName, $columnAliasName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute));
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_MONTH) {
$selectQueryAdapter->addMonthClause($tableName, $columnName, $columnAliasName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute));
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_QUARTER) {
$selectQueryAdapter->addQuarterClause($tableName, $columnName, $columnAliasName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute));
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_YEAR) {
$selectQueryAdapter->addYearClause($tableName, $columnName, $columnAliasName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute));
} else {
$selectQueryAdapter->addClause($tableName, $columnName, $columnAliasName);
}
}
