public function testRecursiveComplexFunctions()
{
$expected = " WITH LatestOrders AS (" . "\t\tSELECT SUM( COUNT( ID ) )," . "\t\t\t\tCOUNT( MAX( n_items ) ), " . "\t\t\t\tCustomerName " . "\t\t\tFROM dbo.Orders" . "\t\t\tRIGHT JOIN Customers AS c" . "\t\t\t\tON Orders.Customer_ID = Customers.ID " . "\t\t\tLEFT JOIN Persons" . "\t\t\t\tON Persons.name = Customer.name" . "\t\t\t\tAND Persons.lastName = Customer.lastName" . "\t\t\tGROUP BY CustomerID" . "\t\t) " . " SELECT " . " Customers.*, " . " Orders.OrderTime AS LatestOrderTime, " . " ( SELECT COUNT( * ) " . "\t\tFROM dbo.OrderItems " . "\t\tWHERE OrderID IN " . " ( SELECT ID FROM dbo.Orders WHERE CustomerID = Customers.ID ) ) " . " AS TotalItemsPurchased " . " FROM dbo.Customers " . " INNER JOIN dbo.Orders " . " USING (ID)" . " WHERE " . "\tOrders.n_items > ? " . "\t\t\tAND CustomerID IN ( ?, ?, ?, ?, ? )" . " AND Orders.ID IN ( SELECT ID FROM LatestOrders )";
$actual = StaticSQL::WITH("LatestOrders", StaticSQL::SELECT("CustomerName")->SUM(StaticSQL::COUNT("ID"))->COUNT(StaticSQL::MAX("n_items"))->FROM("dbo.Orders")->RIGHT_JOIN("Customers")->AS_('c')->ON("Orders.Customer_ID", "Customers.ID")->LEFT_JOIN("Persons")->ON("Persons.name", "Customer.name")->AND_("Persons.lastName", "Customer.lastName")->GROUP_BY("CustomerID"))->SELECT()->FIELD("Customers.*")->FIELD("Orders.OrderTime")->AS_("LatestOrderTime")->FIELD(StaticSQL::SELECT()->COUNT("*")->FROM("dbo.OrderItems")->WHERE("OrderID")->IN(StaticSQL::SELECT("ID")->FROM("dbo.Orders")->WHERE("CustomerID")->EQUAL_TO_FIELD("Customers.ID")))->AS_("TotalItemsPurchased")->FROM("dbo.Customers")->INNER_JOIN("dbo.Orders")->USING("ID")->WHERE("Orders.n_items")->GREATER_THAN(0)->AND_('CustomerID')->IN(1, 2, 3, 4, 5)->AND_("Orders.ID")->IN(StaticSQL::SELECT("ID")->FROM("LatestOrders"));
$this->assertEquals($expected, $actual);
}
public function selectProvider()
{
return [["\n\t\t\t\tSELECT *\n\t\t\t\tFROM products\n\t\t\t\tWHERE price IS NOT NULL\n\t\t\t\t", (new SQL())->SELECT_ALL()->FROM('products')->WHERE('price')->IS_NOT_NULL()], ["\n\t\t\t\tSELECT name\n\t\t\t\tFROM products\n\t\t\t\tWHERE price >= ?\n\t\t\t\t", (new SQL())->SELECT('name')->FROM('products')->WHERE('price')->GREATER_THAN_OR_EQUAL(10)], ["\n\t\t\t\tSELECT name\n\t\t\t\tFROM products\n\t\t\t\tWHERE price < ?\n\t\t\t\t", (new SQL())->SELECT('name')->FROM('products')->WHERE('price')->LESS_THAN(10)], ["\n\t\t\t\tSELECT name\n\t\t\t\tFROM products\n\t\t\t\tWHERE price > ?\n\t\t\t\tLIMIT 10\n\t\t\t\tOFFSET 1\n\t\t\t\t", (new SQL())->SELECT('name')->FROM('products')->WHERE('price')->GREATER_THAN(10)->LIMIT(10)->OFFSET(1)], ["\n\t\t\t\tSELECT name\n\t\t\t\tFROM products\n\t\t\t\t\tLEFT JOIN items\n\t\t\t\t\t\tUSING (item_id, name)\n\t\t\t\tWHERE price > ?\n\t\t\t\tLIMIT 10\n\t\t\t\tOFFSET 1\n\t\t\t\t", (new SQL())->SELECT('name')->FROM('products')->LEFT_JOIN('items')->USING('item_id', 'name')->WHERE('price')->GREATER_THAN(10)->LIMIT(10)->OFFSET(1)], ["\n\t\t\t\tSELECT name\n\t\t\t\tFROM products\n\t\t\t\t\tLEFT JOIN item\n\t\t\t\t\t\tON item_id = product_id\n\t\t\t\t\t\t\tAND products.name = item.name\n\t\t\t\tWHERE price > ?\n\t\t\t\tLIMIT 10\n\t\t\t\tOFFSET 1\n\t\t\t\t", (new SQL())->SELECT('name')->FROM('products')->LEFT_JOIN('item')->ON('item_id', 'product_id')->AND_('products.name', 'item.name')->WHERE('price')->GREATER_THAN(20)->LIMIT(10)->OFFSET(1)], ["\n\t\t\t\tSELECT name, count(*) as c\n\t\t\t\tFROM products\n\t\t\t\tGROUP BY name\n\t\t\t\tHAVING c BETWEEN ? AND ?\n\t\t\t\t", (new SQL())->SELECT('name', 'count(*) as c')->FROM('products')->GROUP_BY('name')->HAVING('c')->BETWEEN(1, 2)], ["\n\t\t\t\tSELECT name, count(*) as c\n\t\t\t\tFROM products\n\t\t\t\tGROUP BY name\n\t\t\t\tHAVING c > ?\n\t\t\t\t", (new SQL())->SELECT('name', 'count(*) as c')->FROM('products')->GROUP_BY('name')->HAVING('c')->GREATER_THAN(1)], ["\n\t\t\t\tSELECT name, address\n\t\t\t\tFROM people\n\t\t\t\tWHERE name LIKE '%nelson%'\n\t\t\t\t", (new SQL())->SELECT('name', 'address')->FROM('people')->WHERE('name')->LIKE('%nelson%'), true], ["\n\t\t\t\tSELECT name, address, birthday\n\t\t\t\tFROM people\n\t\t\t\tWHERE name LIKE '%nelson%'\n\t\t\t\t\tAND (\n\t\t\t\t\t\taddress IS NULL OR\n\t\t\t\t\t\tbirthday = '19851212'\n\t\t\t\t\t)\n\t\t\t\t\tAND some_col > '5'\n\t\t\t\t", (new SQL())->SELECT('name', 'address', 'birthday')->FROM('people')->WHERE('name')->LIKE('%nelson%')->AND_(StaticSQL::RAW('( address is null or birthday = ? )', ['19851212']))->AND_('some_col')->GREATER_THAN(5), true], ["\n\t\t\t\tSELECT *\n\t\t\t\tFROM a\n\t\t\t\t\tLEFT JOIN b\n\t\t\t\t\t\tUSING (c1, c2, c3)\n\t\t\t\t", (new SQL())->SELECT('*')->FROM('a')->LEFT_JOIN('b')->USING('c1', 'c2')->USING('c3'), true]];
}
public function testComplex()
{
$expected = "\n\t\t\tSELECT\n\t\t\t\tsomeCol,\n\t\t\t\t(\n\t\t\t\t\tCASE ( SELECT anotherCol FROM anotherTable WHERE id = '1' )\n\t\t\t\t\t\tWHEN '5' THEN 'five'\n\t\t\t\t\t\tWHEN '6' THEN 'six'\n\t\t\t\t\t\tELSE 'unknown'\n\t\t\t\t\tEND\n\t\t\t\t) AS someCol2\n\t\t\tFROM someTable\n\t\t\tWHERE (\n\t\t\t\tCASE\n\t\t\t\t\tWHEN ( SELECT anotherCol FROM anotherTable WHERE id = '2' ) = 'someValue' THEN ( SELECT anotherCol2 FROM anotherTable WHERE id = '17' )\n\t\t\t\tEND\n\t\t\t) = 'someOtherValue'\n\t\t\t\tAND id = '98'\n\t\t\tORDER BY someCol2\n\t\t\tLIMIT 5\n\t\t";
$actual = (new SQL())->SELECT('someCol')->CASE_((new SQL())->SELECT('anotherCol')->FROM('anotherTable')->WHERE('id')->EQUAL_TO(1))->WHEN(5)->THEN('five')->WHEN(6)->THEN('six')->ELSE_('unknown')->AS_('someCol2')->FROM('someTable')->WHERE(StaticSQL::CASE_()->WHEN((new SQL())->SELECT('anotherCol')->FROM('anotherTable')->WHERE('id')->EQUAL_TO(2))->EQUAL_TO('someValue')->THEN((new SQL())->SELECT('anotherCol2')->FROM('anotherTable')->WHERE('id')->EQUAL_TO(17)))->EQUAL_TO('someOtherValue')->AND_('id')->EQUAL_TO(98)->ORDER_BY('someCol2')->LIMIT(5);
$this->assertEquals($expected, $actual->injectParams(null, $actual->build()));
}
