public function testSumIsCalculatedOnRepository() { $examples = new Collection("Company"); list($sumTotal) = $examples->calculateAggregates([new MySqlCountDistinct("CompanyName")]); $this->assertEquals(2, $sumTotal); $lastStatement = MySql::getPreviousStatement(false); $this->assertContains("COUNT( DISTINCT `CompanyName` ) AS `DistinctCountOfCompanyName`", $lastStatement); }
public function testSumIsCalculatedOnRepository() { $examples = new Collection("Company"); list($sumTotal) = $examples->calculateAggregates([new MySqlAverage("Balance")]); $this->assertEquals(3, $sumTotal); $lastStatement = MySql::getPreviousStatement(false); $this->assertContains("AVG( `Balance` ) AS `AverageOfBalance`", $lastStatement); }
public function testSumIsCalculatedOnRepository() { $examples = new Collection("Company"); list($sumTotal) = $examples->calculateAggregates([new MySqlSum("Balance")]); $this->assertEquals(6, $sumTotal); $lastStatement = MySql::getPreviousStatement(false); $this->assertContains("SUM( `Balance` ) AS `SumOfBalance`", $lastStatement); $examples = new Collection("Company"); $examples->filter(new GreaterThan("Balance", 1)); list($sumTotal) = $examples->calculateAggregates(new MySqlSum("Balance")); $this->assertEquals(5, $sumTotal); $lastStatement = MySql::getPreviousStatement(false); $this->assertContains("SUM( `Balance` ) AS `SumOfBalance`", $lastStatement); $this->assertContains("WHERE `tblCompany`.`Balance` > ", $lastStatement); }
public function testLimits() { MySql::executeStatement("TRUNCATE TABLE tblCompany"); $company = new Company(); $repos = $company->getRepository(); $repos->clearObjectCache(); $company = new Company(); $company->CompanyName = "A"; $company->save(); $company = new Company(); $company->CompanyName = "B"; $company->save(); $company = new Company(); $company->CompanyName = "B"; $company->save(); $company = new Company(); $company->CompanyName = "B"; $company->save(); $company = new Company(); $company->CompanyName = "C"; $company->save(); $company = new Company(); $company->CompanyName = "D"; $company->save(); $list = new Collection("\\Rhubarb\\Stem\\Tests\\Fixtures\\Company"); $list->setRange(2, 6); $this->assertCount(6, $list); $this->assertEquals("C", $list[2]->CompanyName); $sql = MySql::getPreviousStatement(true); $this->assertContains("LIMIT 2, 6", $sql); // Sorting by a computed column should mean that limits are no longer used. $list->addSort("CompanyIDSquared", true); $this->assertCount(6, $list); $this->assertEquals("C", $list[2]->CompanyName); $sql = MySql::getPreviousStatement(); $this->assertNotContains("LIMIT 2, 6", $sql); $sql = MySql::getPreviousStatement(true); $this->assertNotContains("LIMIT 2, 6", $sql); }
public function testMySqlAggregateSupport() { MySql::executeStatement("TRUNCATE TABLE tblCompany"); MySql::executeStatement("TRUNCATE TABLE tblUser"); $company1 = new Company(); $company1->getRepository()->clearObjectCache(); $company1->CompanyName = "1"; $company1->save(); $company2 = new Company(); $company2->CompanyName = "2"; $company2->save(); $user1 = new User(); $user1->Wage = 100; $company1->Users->Append($user1); $user2 = new User(); $user2->Wage = 200; $company1->Users->Append($user2); $user3 = new User(); $user3->Wage = 300; $company2->Users->Append($user3); $user4 = new User(); $user4->Wage = 400; $company2->Users->Append($user4); $companies = new Collection("Company"); $companies->addAggregateColumn(new Sum("Users.Wage")); $results = []; foreach ($companies as $company) { $results[] = $company->SumOfUsersWage; } $this->assertEquals([300, 700], $results); $sql = MySql::getPreviousStatement(); $this->assertEquals("SELECT `tblCompany`.*, SUM( `Users`.`Wage` ) AS `SumOfUsersWage` FROM `tblCompany` LEFT JOIN `tblUser` AS `Users` ON `tblCompany`.`CompanyID` = `Users`.`CompanyID` GROUP BY `tblCompany`.`CompanyID`", $sql); $companies = new Collection("Company"); $companies->addAggregateColumn(new Sum("Users.BigWage")); $results = []; foreach ($companies as $company) { $results[] = $company->SumOfUsersBigWage; } $this->assertEquals([3000, 7000], $results); }