protected static function makeUserLeaderboardSqlQuery($type) { assert('is_string($type)'); $quote = DatabaseCompatibilityUtil::getQuote(); $where = null; $selectDistinct = false; $orderBy = "points desc"; $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('GamePointTransaction'); static::resolveLeaderboardWhereClausesByType($type, $joinTablesAdapter, $where); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $selectQueryAdapter->addClause('_user', 'id', 'userid'); $selectQueryAdapter->addSummationClause('gamepointtransaction', 'value', 'points'); $joinTablesAdapter->addFromTableAndGetAliasName('gamepoint', 'gamepoint_id', 'gamepointtransaction'); $joinTablesAdapter->addFromTableAndGetAliasName('permitable', 'person_item_id', 'gamepoint', 'item_id'); $joinTablesAdapter->addFromTableAndGetAliasName('_user', 'id', 'permitable', 'permitable_id'); $groupBy = "{$quote}_user{$quote}.{$quote}id{$quote}"; $sql = SQLQueryUtil::makeQuery('gamepointtransaction', $selectQueryAdapter, $joinTablesAdapter, null, null, $where, $orderBy, $groupBy); return $sql; }
public function testAddSummationClause() { $quote = DatabaseCompatibilityUtil::getQuote(); $adapter = new RedBeanModelSelectQueryAdapter(); $this->assertEquals(0, $adapter->getClausesCount()); $adapter->addSummationClause('abc', 'c'); $this->assertEquals(1, $adapter->getClausesCount()); $compareString = "select sum(abc) c "; $this->assertEquals($compareString, $adapter->getSelect()); $adapter = new RedBeanModelSelectQueryAdapter(true); $this->assertEquals(0, $adapter->getClausesCount()); $adapter->addSummationClause('def', 'c'); $this->assertEquals(1, $adapter->getClausesCount()); $compareString = "select distinct sum(def) c "; $this->assertEquals($compareString, $adapter->getSelect()); }
/** * @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); } }