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);
}
