SQLSelectTest.php

<?php

namespace SilverStripe\ORM\Tests;

use InvalidArgumentException;
use SilverStripe\ORM\DB;
use SilverStripe\ORM\Connect\MySQLDatabase;
use SilverStripe\ORM\Queries\SQLSelect;
use SilverStripe\SQLite\SQLite3Database;
use SilverStripe\PostgreSQL\PostgreSQLDatabase;
use SilverStripe\Dev\SapphireTest;

class SQLSelectTest extends SapphireTest
{

    protected static $fixture_file = 'SQLSelectTest.yml';

    protected static $extra_dataobjects = [
        SQLSelectTest\TestObject::class,
        SQLSelectTest\TestBase::class,
        SQLSelectTest\TestChild::class
    ];

    protected $oldDeprecation = null;

    public function testCount()
    {

        //basic counting
        $qry = SQLSelectTest\TestObject::get()->dataQuery()->getFinalisedQuery();
        $ids = $this->allFixtureIDs(SQLSelectTest\TestObject::class);
        $count = $qry->count('"SQLSelectTest_DO"."ID"');
        $this->assertEquals(count($ids ?? []), $count);
        $this->assertIsInt($count);
        //test with `having`
        if (DB::get_conn() instanceof MySQLDatabase) {
            $qry->setSelect([
                'Date' => 'MAX("Date")',
                'Common' => '"Common"',
            ]);
            $qry->setGroupBy('"Common"');
            $qry->setHaving('"Date" > 2012-02-01');
            $count = $qry->count('"SQLSelectTest_DO"."ID"');
            $this->assertEquals(1, $count);
            $this->assertIsInt($count);
        }
    }
    public function testUnlimitedRowCount()
    {
        //basic counting
        $qry = SQLSelectTest\TestObject::get()->dataQuery()->getFinalisedQuery();
        $ids = $this->allFixtureIDs(SQLSelectTest\TestObject::class);
        $qry->setLimit(1);
        $count = $qry->unlimitedRowCount('"SQLSelectTest_DO"."ID"');
        $this->assertEquals(count($ids ?? []), $count);
        $this->assertIsInt($count);
        // Test without column - SQLSelect has different logic for this
        $count = $qry->unlimitedRowCount();
        $this->assertEquals(2, $count);
        $this->assertIsInt($count);
        //test with `having`
        if (DB::get_conn() instanceof MySQLDatabase) {
            $qry->setHaving('"Date" > 2012-02-01');
            $count = $qry->unlimitedRowCount('"SQLSelectTest_DO"."ID"');
            $this->assertEquals(1, $count);
            $this->assertIsInt($count);
        }
    }

    public function provideIsEmpty()
    {
        return [
            [
                'query' => new SQLSelect(),
                'expected' => true,
            ],
            [
                'query' => new SQLSelect(from: 'someTable'),
                'expected' => false,
            ],
            [
                'query' => new SQLSelect(''),
                'expected' => true,
            ],
            [
                'query' => new SQLSelect('', 'someTable'),
                'expected' => true,
            ],
            [
                'query' => new SQLSelect('column', 'someTable'),
                'expected' => false,
            ],
            [
                'query' => new SQLSelect('value'),
                'expected' => false,
            ],
        ];
    }

    /**
     * @dataProvider provideIsEmpty
     */
    public function testIsEmpty(SQLSelect $query, $expected)
    {
        $this->assertSame($expected, $query->isEmpty());
    }

    public function testEmptyQueryReturnsNothing()
    {
        $query = new SQLSelect();
        $this->assertSQLEquals('', $query->sql($parameters));
    }

    public function provideSelectFrom()
    {
        return [
            [
                'from' => ['MyTable'],
                'expected' => 'SELECT * FROM MyTable',
            ],
            [
                'from' => ['MyTable', 'MySecondTable'],
                'expected' => 'SELECT * FROM MyTable, MySecondTable',
            ],
            [
                'from' => ['MyTable', 'INNER JOIN AnotherTable on AnotherTable.ID = MyTable.SomeFieldID'],
                'expected' => 'SELECT * FROM MyTable INNER JOIN AnotherTable on AnotherTable.ID = MyTable.SomeFieldID',
            ],
            [
                'from' => ['MyTable', 'MySecondTable', 'INNER JOIN AnotherTable on AnotherTable.ID = MyTable.SomeFieldID'],
                'expected' => 'SELECT * FROM MyTable, MySecondTable INNER JOIN AnotherTable on AnotherTable.ID = MyTable.SomeFieldID',
            ],
        ];
    }

    /**
     * @dataProvider provideSelectFrom
     */
    public function testSelectFrom(array $from, string $expected)
    {
        $query = new SQLSelect();
        $query->setFrom($from);
        $this->assertSQLEquals($expected, $query->sql($parameters));
    }

    public function testSelectFromUserSpecifiedFields()
    {
        $query = new SQLSelect();
        $query->setSelect(["Name", "Title", "Description"]);
        $query->setFrom("MyTable");
        $this->assertSQLEquals("SELECT Name, Title, Description FROM MyTable", $query->sql($parameters));
    }

    public function testSelectWithWhereClauseFilter()
    {
        $query = new SQLSelect();
        $query->setSelect(["Name","Meta"]);
        $query->setFrom("MyTable");
        $query->setWhere("Name = 'Name'");
        $query->addWhere("Meta = 'Test'");
        $this->assertSQLEquals(
            "SELECT Name, Meta FROM MyTable WHERE (Name = 'Name') AND (Meta = 'Test')",
            $query->sql($parameters)
        );
    }

    public function testSelectWithConstructorParameters()
    {
        $query = new SQLSelect(["Foo", "Bar"], "FooBarTable");
        $this->assertSQLEquals("SELECT Foo, Bar FROM FooBarTable", $query->sql($parameters));
        $query = new SQLSelect(["Foo", "Bar"], "FooBarTable", ["Foo = 'Boo'"]);
        $this->assertSQLEquals("SELECT Foo, Bar FROM FooBarTable WHERE (Foo = 'Boo')", $query->sql($parameters));
    }

    public function testSelectWithChainedMethods()
    {
        $query = new SQLSelect();
        $query->setSelect("Name", "Meta")->setFrom("MyTable")->setWhere("Name = 'Name'")->addWhere("Meta = 'Test'");
        $this->assertSQLEquals(
            "SELECT Name, Meta FROM MyTable WHERE (Name = 'Name') AND (Meta = 'Test')",
            $query->sql($parameters)
        );
    }

    public function testCanSortBy()
    {
        $query = new SQLSelect();
        $query->setSelect("Name", "Meta")->setFrom("MyTable")->setWhere("Name = 'Name'")->addWhere("Meta = 'Test'");
        $this->assertTrue($query->canSortBy('Name ASC'));
        $this->assertTrue($query->canSortBy('Name'));
    }

    /**
     * Test multiple order by SQL clauses.
     */
    public function testAddOrderBy()
    {
        $query = new SQLSelect();
        $query->setSelect('ID', "Title")->setFrom('Page')->addOrderBy('(ID % 2)  = 0', 'ASC')->addOrderBy('ID > 50', 'ASC');
        $this->assertSQLEquals(
            'SELECT ID, Title, (ID % 2)  = 0 AS "_SortColumn0", ID > 50 AS "_SortColumn1" FROM Page ORDER BY "_SortColumn0" ASC, "_SortColumn1" ASC',
            $query->sql($parameters)
        );
    }

    public function testSelectWithChainedFilterParameters()
    {
        $query = new SQLSelect();
        $query->setSelect(["Name","Meta"])->setFrom("MyTable");
        $query->setWhere("Name = 'Name'")->addWhere("Meta = 'Test'")->addWhere("Beta != 'Gamma'");
        $this->assertSQLEquals(
            "SELECT Name, Meta FROM MyTable WHERE (Name = 'Name') AND (Meta = 'Test') AND (Beta != 'Gamma')",
            $query->sql($parameters)
        );
    }

    public function testSelectWithLimitClause()
    {
        if (!(DB::get_conn() instanceof MySQLDatabase || DB::get_conn() instanceof SQLite3Database
            || DB::get_conn() instanceof PostgreSQLDatabase)
        ) {
            $this->markTestIncomplete();
        }

        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->setLimit(99);
        $this->assertSQLEquals("SELECT * FROM MyTable LIMIT 99", $query->sql($parameters));

        // array limit with start (MySQL specific)
        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->setLimit(99, 97);
        $this->assertSQLEquals("SELECT * FROM MyTable LIMIT 99 OFFSET 97", $query->sql($parameters));
    }


    public function testSelectWithOrderbyClause()
    {
        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->setOrderBy('MyName');
        $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName ASC', $query->sql($parameters));

        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->setOrderBy('MyName desc');
        $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName DESC', $query->sql($parameters));

        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->setOrderBy('MyName ASC, Color DESC');
        $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName ASC, Color DESC', $query->sql($parameters));

        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->setOrderBy('MyName ASC, Color');
        $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName ASC, Color ASC', $query->sql($parameters));

        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->setOrderBy(['MyName' => 'desc']);
        $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName DESC', $query->sql($parameters));

        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->setOrderBy(['MyName' => 'desc', 'Color']);
        $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName DESC, Color ASC', $query->sql($parameters));

        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->setOrderBy('implode("MyName","Color")');
        $this->assertSQLEquals(
            'SELECT *, implode("MyName","Color") AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" ASC',
            $query->sql($parameters)
        );

        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->setOrderBy('implode("MyName","Color") DESC');
        $this->assertSQLEquals(
            'SELECT *, implode("MyName","Color") AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" DESC',
            $query->sql($parameters)
        );

        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->setOrderBy('RAND()');
        $this->assertSQLEquals(
            'SELECT *, RAND() AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" ASC',
            $query->sql($parameters)
        );

        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->addFrom('INNER JOIN SecondTable USING (ID)');
        $query->addFrom('INNER JOIN ThirdTable USING (ID)');
        $query->setOrderBy('MyName');
        $this->assertSQLEquals(
            'SELECT * FROM MyTable '
            . 'INNER JOIN SecondTable USING (ID) '
            . 'INNER JOIN ThirdTable USING (ID) '
            . 'ORDER BY MyName ASC',
            $query->sql($parameters)
        );
    }

    public function testNullLimit()
    {
        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->setLimit(null);

        $this->assertSQLEquals(
            'SELECT * FROM MyTable',
            $query->sql($parameters)
        );
    }

    public function testZeroLimit()
    {
        $query = new SQLSelect();
        $query->setFrom("MyTable");
        $query->setLimit(0);

        $this->assertSQLEquals(
            'SELECT * FROM MyTable LIMIT 0',
            $query->sql($parameters)
        );
    }

    public function testNegativeLimit()
    {
        $this->expectException(\InvalidArgumentException::class);
        $query = new SQLSelect();
        $query->setLimit(-10);
    }

    public function testNegativeOffset()
    {
        $this->expectException(\InvalidArgumentException::class);
        $query = new SQLSelect();
        $query->setLimit(1, -10);
    }

    public function testNegativeOffsetAndLimit()
    {
        $this->expectException(\InvalidArgumentException::class);
        $query = new SQLSelect();
        $query->setLimit(-10, -10);
    }

    public function testReverseOrderBy()
    {
        $query = new SQLSelect();
        $query->setFrom('MyTable');

        // default is ASC
        $query->setOrderBy("Name");
        $query->reverseOrderBy();

        $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY Name DESC', $query->sql($parameters));

        $query->setOrderBy("Name DESC");
        $query->reverseOrderBy();

        $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY Name ASC', $query->sql($parameters));

        $query->setOrderBy(["Name" => "ASC"]);
        $query->reverseOrderBy();

        $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY Name DESC', $query->sql($parameters));

        $query->setOrderBy(["Name" => 'DESC', 'Color' => 'asc']);
        $query->reverseOrderBy();

        $this->assertSQLEquals('SELECT * FROM MyTable ORDER BY Name ASC, Color DESC', $query->sql($parameters));

        $query->setOrderBy('implode("MyName","Color") DESC');
        $query->reverseOrderBy();

        $this->assertSQLEquals(
            'SELECT *, implode("MyName","Color") AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" ASC',
            $query->sql($parameters)
        );
    }

    public function testFiltersOnID()
    {
        $query = new SQLSelect();
        $query->setWhere("ID = 5");
        $this->assertTrue(
            $query->filtersOnID(),
            "filtersOnID() is true with simple unquoted column name"
        );

        $query = new SQLSelect();
        $query->setWhere('"ID" = 5');
        $this->assertTrue(
            $query->filtersOnID(),
            "filtersOnID() is true with simple quoted column name"
        );

        $query = new SQLSelect();
        $query->setWhere(['"ID"' => 4]);
        $this->assertTrue(
            $query->filtersOnID(),
            "filtersOnID() is true with parameterised quoted column name"
        );

        $query = new SQLSelect();
        $query->setWhere(['"ID" = ?' => 4]);
        $this->assertTrue(
            $query->filtersOnID(),
            "filtersOnID() is true with parameterised quoted column name"
        );

        $query = new SQLSelect();
        $query->setWhere('"ID" IN (5,4)');
        $this->assertTrue(
            $query->filtersOnID(),
            "filtersOnID() is true with WHERE ID IN"
        );

        $query = new SQLSelect();
        $query->setWhere(['"ID" IN ?' => [1,2]]);
        $this->assertTrue(
            $query->filtersOnID(),
            "filtersOnID() is true with parameterised WHERE ID IN"
        );

        $query = new SQLSelect();
        $query->setWhere("ID=5");
        $this->assertTrue(
            $query->filtersOnID(),
            "filtersOnID() is true with simple unquoted column name and no spaces in equals sign"
        );

        $query = new SQLSelect();
        $query->setWhere("Identifier = 5");
        $this->assertFalse(
            $query->filtersOnID(),
            "filtersOnID() is false with custom column name (starting with 'id')"
        );

        $query = new SQLSelect();
        $query->setWhere("ParentID = 5");
        $this->assertFalse(
            $query->filtersOnID(),
            "filtersOnID() is false with column name ending in 'ID'"
        );

        $query = new SQLSelect();
        $query->setWhere("MyTable.ID = 5");
        $this->assertTrue(
            $query->filtersOnID(),
            "filtersOnID() is true with table and column name"
        );

        $query = new SQLSelect();
        $query->setWhere("MyTable.ID = 5");
        $this->assertTrue(
            $query->filtersOnID(),
            "filtersOnID() is true with table and quoted column name "
        );
    }

    public function testFiltersOnFK()
    {
        $query = new SQLSelect();
        $query->setWhere("ID = 5");
        $this->assertFalse(
            $query->filtersOnFK(),
            "filtersOnFK() is true with simple unquoted column name"
        );

        $query = new SQLSelect();
        $query->setWhere("Identifier = 5");
        $this->assertFalse(
            $query->filtersOnFK(),
            "filtersOnFK() is false with custom column name (starting with 'id')"
        );

        $query = new SQLSelect();
        $query->setWhere("MyTable.ParentID = 5");
        $this->assertTrue(
            $query->filtersOnFK(),
            "filtersOnFK() is true with table and column name"
        );

        $query = new SQLSelect();
        $query->setWhere("MyTable.`ParentID`= 5");
        $this->assertTrue(
            $query->filtersOnFK(),
            "filtersOnFK() is true with table and quoted column name "
        );
    }

    public function testInnerJoin()
    {
        $query = new SQLSelect();
        $query->setFrom('MyTable');
        $query->addInnerJoin('MyOtherTable', 'MyOtherTable.ID = 2');
        $query->addLeftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID');

        $this->assertSQLEquals(
            'SELECT * FROM MyTable ' .
            'INNER JOIN "MyOtherTable" ON MyOtherTable.ID = 2 ' .
            'LEFT JOIN "MyLastTable" ON MyOtherTable.ID = MyLastTable.ID',
            $query->sql($parameters)
        );

        $query = new SQLSelect();
        $query->setFrom('MyTable');
        $query->addInnerJoin('MyOtherTable', 'MyOtherTable.ID = 2', 'table1');
        $query->addLeftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID', 'table2');

        $this->assertSQLEquals(
            'SELECT * FROM MyTable ' .
            'INNER JOIN "MyOtherTable" AS "table1" ON MyOtherTable.ID = 2 ' .
            'LEFT JOIN "MyLastTable" AS "table2" ON MyOtherTable.ID = MyLastTable.ID',
            $query->sql($parameters)
        );
    }

    public function testJoinSubSelect()
    {

        $query = new SQLSelect();
        $query->setFrom('MyTable');
        $query->addInnerJoin(
            '(SELECT * FROM MyOtherTable)',
            'Mot.MyTableID = MyTable.ID',
            'Mot'
        );
        $query->addLeftJoin(
            '(SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable '
            . 'GROUP BY MyOtherTableID)',
            'Mlt.MyOtherTableID = Mot.ID',
            'Mlt'
        );
        $query->setOrderBy('COALESCE(Mlt.MyLastTableCount, 0) DESC');

        $this->assertSQLEquals(
            'SELECT *, COALESCE(Mlt.MyLastTableCount, 0) AS "_SortColumn0" FROM MyTable ' .
            'INNER JOIN (SELECT * FROM MyOtherTable) AS "Mot" ON Mot.MyTableID = MyTable.ID ' .
            'LEFT JOIN (SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable '
            . 'GROUP BY MyOtherTableID) AS "Mlt" ON Mlt.MyOtherTableID = Mot.ID ' .
            'ORDER BY "_SortColumn0" DESC',
            $query->sql($parameters)
        );
    }

    public function testSetWhereAny()
    {
        $query = new SQLSelect();
        $query->setFrom('MyTable');

        $query->setWhereAny(
            [
            'Monkey' => 'Chimp',
            'Color' => 'Brown'
            ]
        );
        $sql = $query->sql($parameters);
        $this->assertSQLEquals("SELECT * FROM MyTable WHERE ((Monkey = ?) OR (Color = ?))", $sql);
        $this->assertEquals(['Chimp', 'Brown'], $parameters);
    }

    public function testSelectFirst()
    {
        // Test first from sequence
        $query = new SQLSelect();
        $query->setFrom('"SQLSelectTest_DO"');
        $query->setOrderBy('"Name"');
        $result = $query->firstRow()->execute();

        $records = [];
        foreach ($result as $row) {
            $records[] = $row;
        }

        $this->assertCount(1, $records);
        $this->assertEquals('Object 1', $records[0]['Name']);

        // Test first from empty sequence
        $query = new SQLSelect();
        $query->setFrom('"SQLSelectTest_DO"');
        $query->setOrderBy('"Name"');
        $query->setWhere(['"Name"' => 'Nonexistent Object']);
        $result = $query->firstRow()->execute();

        $records = [];
        foreach ($result as $row) {
            $records[] = $row;
        }

        $this->assertCount(0, $records);

        // Test that given the last item, the 'first' in this list matches the last
        $query = new SQLSelect();
        $query->setFrom('"SQLSelectTest_DO"');
        $query->setOrderBy('"Name"');
        $query->setLimit(1, 1);
        $result = $query->firstRow()->execute();

        $records = [];
        foreach ($result as $row) {
            $records[] = $row;
        }

        $this->assertCount(1, $records);
        $this->assertEquals('Object 2', $records[0]['Name']);
    }

    public function testSelectLast()
    {
        // Test last in sequence
        $query = new SQLSelect();
        $query->setFrom('"SQLSelectTest_DO"');
        $query->setOrderBy('"Name"');
        $result = $query->lastRow()->execute();

        $records = [];
        foreach ($result as $row) {
            $records[] = $row;
        }

        $this->assertCount(1, $records);
        $this->assertEquals('Object 2', $records[0]['Name']);

        // Test last from empty sequence
        $query = new SQLSelect();
        $query->setFrom('"SQLSelectTest_DO"');
        $query->setOrderBy('"Name"');
        $query->setWhere(["\"Name\" = 'Nonexistent Object'"]);
        $result = $query->lastRow()->execute();

        $records = [];
        foreach ($result as $row) {
            $records[] = $row;
        }

        $this->assertCount(0, $records);

        // Test that given the first item, the 'last' in this list matches the first
        $query = new SQLSelect();
        $query->setFrom('"SQLSelectTest_DO"');
        $query->setOrderBy('"Name"');
        $query->setLimit(1);
        $result = $query->lastRow()->execute();

        $records = [];
        foreach ($result as $row) {
            $records[] = $row;
        }

        $this->assertCount(1, $records);
        $this->assertEquals('Object 1', $records[0]['Name']);
    }

    /**
     * Tests aggregate() function
     */
    public function testAggregate()
    {
        $query = new SQLSelect('"Common"');
        $query->setFrom('"SQLSelectTest_DO"');
        $query->setGroupBy('"Common"');

        $queryClone = $query->aggregate('COUNT(*)', 'cnt');
        $result = $queryClone->execute();
        $this->assertEquals([2], $result->column('cnt'));
    }

    /**
     * Tests that an ORDER BY is only added if a LIMIT is set.
     */
    public function testAggregateNoOrderByIfNoLimit()
    {
        $query = new SQLSelect();
        $query->setFrom('"SQLSelectTest_DO"');
        $query->setOrderBy('Common');
        $query->setLimit([]);

        $aggregate = $query->aggregate('MAX("ID")');
        $limit = $aggregate->getLimit();
        $this->assertEquals([], $aggregate->getOrderBy());
        $this->assertEquals([], $limit);

        $query = new SQLSelect();
        $query->setFrom('"SQLSelectTest_DO"');
        $query->setOrderBy('Common');
        $query->setLimit(2);

        $aggregate = $query->aggregate('MAX("ID")');
        $limit = $aggregate->getLimit();
        $this->assertEquals(['Common' => 'ASC'], $aggregate->getOrderBy());
        $this->assertEquals(['start' => 0, 'limit' => 2], $limit);
    }

    /**
     * Test that "_SortColumn0" is added for an aggregate in the ORDER BY
     * clause, in combination with a LIMIT and GROUP BY clause.
     * For some databases, like MSSQL, this is a complicated scenario
     * because a subselect needs to be done to query paginated data.
     */
    public function testOrderByContainingAggregateAndLimitOffset()
    {
        $query = new SQLSelect();
        $query->setSelect(['"Name"', '"Meta"']);
        $query->setFrom('"SQLSelectTest_DO"');
        $query->setOrderBy(['MAX("Date")']);
        $query->setGroupBy(['"Name"', '"Meta"']);
        $query->setLimit('1', '1');

        $records = [];
        foreach ($query->execute() as $record) {
            $records[] = $record;
        }

        $this->assertCount(1, $records);

        $this->assertEquals('Object 2', $records[0]['Name']);
        $this->assertEquals('2012-05-01 09:00:00', $records['0']['_SortColumn0']);
    }

    /**
     * Test that multiple order elements are maintained in the given order
     */
    public function testOrderByMultiple()
    {
        if (DB::get_conn() instanceof MySQLDatabase) {
            $query = new SQLSelect();
            $query->setSelect(['"Name"', '"Meta"']);
            $query->setFrom('"SQLSelectTest_DO"');
            $query->setOrderBy(['MID("Name", 8, 1) DESC', '"Name" ASC']);

            $records = [];
            foreach ($query->execute() as $record) {
                $records[] = $record;
            }

            $this->assertCount(2, $records);

            $this->assertEquals('Object 2', $records[0]['Name']);
            $this->assertEquals('2', $records[0]['_SortColumn0']);

            $this->assertEquals('Object 1', $records[1]['Name']);
            $this->assertEquals('1', $records[1]['_SortColumn0']);
        }
    }

    public function testSelect()
    {
        $query = new SQLSelect('"Title"', '"MyTable"');
        $query->addSelect('"TestField"');
        $this->assertSQLEquals(
            'SELECT "Title", "TestField" FROM "MyTable"',
            $query->sql()
        );

        // Test replacement of select
        $query->setSelect(
            [
            'Field' => '"Field"',
            'AnotherAlias' => '"AnotherField"'
            ]
        );
        $this->assertSQLEquals(
            'SELECT "Field", "AnotherField" AS "AnotherAlias" FROM "MyTable"',
            $query->sql()
        );

        // Check that ' as ' selects don't get mistaken as aliases
        $query->addSelect(
            [
            'Relevance' => "MATCH (Title, MenuTitle) AGAINST ('Two as One')"
            ]
        );
        $this->assertSQLEquals(
            'SELECT "Field", "AnotherField" AS "AnotherAlias", MATCH (Title, MenuTitle) AGAINST (' .
            '\'Two as One\') AS "Relevance" FROM "MyTable"',
            $query->sql()
        );
    }

    public function testSelectWithNoTable()
    {
        $query = new SQLSelect('200');
        $this->assertSQLEquals('SELECT 200 AS "200"', $query->sql());
        $this->assertSame([['200' => 200]], iterator_to_array($query->execute(), true));
    }

    /**
     * Test passing in a LIMIT with OFFSET clause string.
     */
    public function testLimitSetFromClauseString()
    {
        $query = new SQLSelect();
        $query->setSelect('*');
        $query->setFrom('"SQLSelectTest_DO"');

        $query->setLimit('20 OFFSET 10');
        $limit = $query->getLimit();
        $this->assertEquals(20, $limit['limit']);
        $this->assertEquals(10, $limit['start']);
    }

    public function testParameterisedInnerJoins()
    {
        $query = new SQLSelect();
        $query->setSelect(['"SQLSelectTest_DO"."Name"', '"SubSelect"."Count"']);
        $query->setFrom('"SQLSelectTest_DO"');
        $query->addInnerJoin(
            '(SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?)',
            '"SQLSelectTest_DO"."Name" = "SubSelect"."Title"',
            'SubSelect',
            20,
            ['%MyName%']
        );
        $query->addWhere(['"SQLSelectTest_DO"."Date" > ?' => '2012-08-08 12:00']);

        $this->assertSQLEquals(
            'SELECT "SQLSelectTest_DO"."Name", "SubSelect"."Count"
			FROM "SQLSelectTest_DO" INNER JOIN (SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase"
		   GROUP BY "Title" HAVING "Title" NOT LIKE ?) AS "SubSelect" ON "SQLSelectTest_DO"."Name" =
		   "SubSelect"."Title"
			WHERE ("SQLSelectTest_DO"."Date" > ?)',
            $query->sql($parameters)
        );
        $this->assertEquals(['%MyName%', '2012-08-08 12:00'], $parameters);
        $query->execute();
    }

    public function testParameterisedLeftJoins()
    {
        $query = new SQLSelect();
        $query->setSelect(['"SQLSelectTest_DO"."Name"', '"SubSelect"."Count"']);
        $query->setFrom('"SQLSelectTest_DO"');
        $query->addLeftJoin(
            '(SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?)',
            '"SQLSelectTest_DO"."Name" = "SubSelect"."Title"',
            'SubSelect',
            20,
            ['%MyName%']
        );
        $query->addWhere(['"SQLSelectTest_DO"."Date" > ?' => '2012-08-08 12:00']);

        $this->assertSQLEquals(
            'SELECT "SQLSelectTest_DO"."Name", "SubSelect"."Count"
			FROM "SQLSelectTest_DO" LEFT JOIN (SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase"
		   GROUP BY "Title" HAVING "Title" NOT LIKE ?) AS "SubSelect" ON "SQLSelectTest_DO"."Name" =
		   "SubSelect"."Title"
			WHERE ("SQLSelectTest_DO"."Date" > ?)',
            $query->sql($parameters)
        );
        $this->assertEquals(['%MyName%', '2012-08-08 12:00'], $parameters);
        $query->execute();
    }

    public function testBaseTableAliases()
    {
        $query = SQLSelect::create('*', ['"MyTableAlias"' => '"MyTable"']);
        $sql = $query->sql();

        $this->assertSQLEquals('SELECT * FROM "MyTable" AS "MyTableAlias"', $sql);

        $query = SQLSelect::create('*', ['MyTableAlias' => '"MyTable"']);
        $sql = $query->sql();

        $this->assertSQLEquals('SELECT * FROM "MyTable" AS "MyTableAlias"', $sql);

        $query = SQLSelect::create('*', ['"MyTableAlias"' => '"MyTable"']);
        $query->addLeftJoin('OtherTable', '"Thing" = "OtherThing"', 'OtherTableAlias');
        $sql = $query->sql();

        $this->assertSQLEquals(
            'SELECT *
              FROM "MyTable" AS "MyTableAlias"
              LEFT JOIN "OtherTable" AS "OtherTableAlias" ON "Thing" = "OtherThing"',
            $sql
        );

        // This feature is a bug that used to exist in SS4 and was removed in SS5
        // so now we test it does not exist and we end up with incorrect SQL because of that
        // In SS4 the "explicitAlias" would be ignored
        $query = SQLSelect::create('*', [
            'MyTableAlias' => '"MyTable"',
            'explicitAlias' => '(SELECT * FROM "MyTable" where "something" = "whatever") as "CrossJoin"'
        ]);
        $sql = $query->sql();

        $this->assertSQLEquals(
            'SELECT * FROM "MyTable" AS "MyTableAlias", ' .
            '(SELECT * FROM "MyTable" where "something" = "whatever") as "CrossJoin" AS "explicitAlias"',
            $sql
        );
    }
}