DataQueryTest::testSurrogateFieldSort() - Code Metrics - Inspection of "Merge branch '4'" - silverstripe/silverstripe-framework - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 72e173...aab521 )

by Robbie

created 2019-05-29 21:38 UTC

DataQueryTest::testSurrogateFieldSort() A

↳ Parent: DataQueryTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	23
Code Lines	17

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	17
nc	1
nop	0
dl	0
loc	23
rs	9.7
c	0
b	0
f	0

<?php

namespace SilverStripe\ORM\Tests;

use SilverStripe\ORM\Connect\Query;
use SilverStripe\ORM\DataQuery;
use SilverStripe\ORM\DataObject;
use SilverStripe\ORM\DB;
use SilverStripe\Dev\SapphireTest;
use SilverStripe\Security\Member;

/**
 * @skipUpgrade
 */
class DataQueryTest extends SapphireTest
{

    protected static $fixture_file = 'DataQueryTest.yml';

    protected static $extra_dataobjects = array(
        DataQueryTest\ObjectA::class,
        DataQueryTest\ObjectB::class,
        DataQueryTest\ObjectC::class,
        DataQueryTest\ObjectD::class,
        DataQueryTest\ObjectE::class,
        DataQueryTest\ObjectF::class,
        DataQueryTest\ObjectG::class,
        SQLSelectTest\TestObject::class,
        SQLSelectTest\TestBase::class,
        SQLSelectTest\TestChild::class,
    );

    public function testSortByJoinedFieldRetainsSourceInformation()
    {
        $bar = new DataQueryTest\ObjectC();
        $bar->Title = "Bar";
        $bar->write();

        $foo = new DataQueryTest\ObjectB();
        $foo->Title = "Foo";
        $foo->TestC = $bar->ID;

        $foo->write();

        $query = new DataQuery(DataQueryTest\ObjectB::class);
        $result = $query->leftJoin(
            'DataQueryTest_C',
            "\"DataQueryTest_B\".\"TestCID\" = \"DataQueryTest_B\".\"ID\""
        )->sort('"DataQueryTest_B"."Title"', 'ASC');

        $result = $result->execute()->record();
        $this->assertEquals('Foo', $result['Title']);
    }

    /**
     * Test the leftJoin() and innerJoin method of the DataQuery object
     */
    public function testJoins()
    {
        $dq = new DataQuery(Member::class);
        $dq->innerJoin("Group_Members", "\"Group_Members\".\"MemberID\" = \"Member\".\"ID\"");
        $this->assertSQLContains(
            "INNER JOIN \"Group_Members\" ON \"Group_Members\".\"MemberID\" = \"Member\".\"ID\"",
            $dq->sql($parameters)
        );

        $dq = new DataQuery(Member::class);
        $dq->leftJoin("Group_Members", "\"Group_Members\".\"MemberID\" = \"Member\".\"ID\"");
        $this->assertSQLContains(
            "LEFT JOIN \"Group_Members\" ON \"Group_Members\".\"MemberID\" = \"Member\".\"ID\"",
            $dq->sql($parameters)
        );
    }

    public function testApplyRelation()
    {
        // Test applyRelation with two has_ones pointing to the same class
        $dq = new DataQuery(DataQueryTest\ObjectB::class);
        $dq->applyRelation('TestC');
        $this->assertTrue($dq->query()->isJoinedTo('testc_DataQueryTest_C'));
        $this->assertContains('"testc_DataQueryTest_C"."ID" = "DataQueryTest_B"."TestCID"', $dq->sql());

        $dq = new DataQuery(DataQueryTest\ObjectB::class);
        $dq->applyRelation('TestCTwo');
        $this->assertTrue($dq->query()->isJoinedTo('testctwo_DataQueryTest_C'));
        $this->assertContains('"testctwo_DataQueryTest_C"."ID" = "DataQueryTest_B"."TestCTwoID"', $dq->sql());
    }

    public function testApplyRelationDeepInheritance()
    {
        //test has_one relation
        $newDQ = new DataQuery(DataQueryTest\ObjectE::class);
        //apply a relation to a relation from an ancestor class
        $newDQ->applyRelation('TestA');
        $this->assertTrue($newDQ->query()->isJoinedTo('DataQueryTest_C'));
        $this->assertContains('"testa_DataQueryTest_A"."ID" = "DataQueryTest_C"."TestAID"', $newDQ->sql($params));

        //test many_many relation

        //test many_many with separate inheritance
        $newDQ = new DataQuery(DataQueryTest\ObjectC::class);
        $baseDBTable = DataObject::getSchema()->baseDataTable(DataQueryTest\ObjectC::class);
        $newDQ->applyRelation('ManyTestAs');
        //check we are "joined" to the DataObject's table (there is no distinction between FROM or JOIN clauses)
        $this->assertTrue($newDQ->query()->isJoinedTo($baseDBTable));
        //check we are explicitly selecting "FROM" the DO's table
        $this->assertContains("FROM \"$baseDBTable\"", $newDQ->sql());

        //test many_many with shared inheritance
        $newDQ = new DataQuery(DataQueryTest\ObjectE::class);
        $baseDBTable = DataObject::getSchema()->baseDataTable(DataQueryTest\ObjectE::class);
        //check we are "joined" to the DataObject's table (there is no distinction between FROM or JOIN clauses)
        $this->assertTrue($newDQ->query()->isJoinedTo($baseDBTable));
        //check we are explicitly selecting "FROM" the DO's table
        $this->assertContains("FROM \"$baseDBTable\"", $newDQ->sql(), 'The FROM clause is missing from the query');
        $newDQ->applyRelation('ManyTestGs');
        //confirm we are still joined to the base table
        $this->assertTrue($newDQ->query()->isJoinedTo($baseDBTable));
        //double check it is the "FROM" clause
        $this->assertContains(
            "FROM \"$baseDBTable\"",
            $newDQ->sql(),
            'The FROM clause has been removed from the query'
        );
        //another (potentially less crude check) for checking "FROM" clause
        $fromTables = $newDQ->query()->getFrom();
        $this->assertEquals('"' . $baseDBTable . '"', $fromTables[$baseDBTable]);
    }

    public function testRelationReturn()
    {
        $dq = new DataQuery(DataQueryTest\ObjectC::class);
        $this->assertEquals(
            DataQueryTest\ObjectA::class,
            $dq->applyRelation('TestA'),
            'DataQuery::applyRelation should return the name of the related object.'
        );
        $this->assertEquals(
            DataQueryTest\ObjectA::class,
            $dq->applyRelation('TestAs'),
            'DataQuery::applyRelation should return the name of the related object.'
        );
        $this->assertEquals(
            DataQueryTest\ObjectA::class,
            $dq->applyRelation('ManyTestAs'),
            'DataQuery::applyRelation should return the name of the related object.'
        );

        $this->assertEquals(
            DataQueryTest\ObjectB::class,
            $dq->applyRelation('TestB'),
            'DataQuery::applyRelation should return the name of the related object.'
        );
        $this->assertEquals(
            DataQueryTest\ObjectB::class,
            $dq->applyRelation('TestBs'),
            'DataQuery::applyRelation should return the name of the related object.'
        );
        $this->assertEquals(
            DataQueryTest\ObjectB::class,
            $dq->applyRelation('ManyTestBs'),
            'DataQuery::applyRelation should return the name of the related object.'
        );
        $newDQ = new DataQuery(DataQueryTest\ObjectE::class);
        $this->assertEquals(
            DataQueryTest\ObjectA::class,
            $newDQ->applyRelation('TestA'),
            'DataQuery::applyRelation should return the name of the related object.'
        );
    }

    public function testRelationOrderWithCustomJoin()
    {
        $dataQuery = new DataQuery(DataQueryTest\ObjectB::class);
        $dataQuery->innerJoin('DataQueryTest_D', '"DataQueryTest_D"."RelationID" = "DataQueryTest_B"."ID"');
        $this->assertInstanceOf(Query::class, $dataQuery->execute());
    }

    public function testDisjunctiveGroup()
    {
        $dq = new DataQuery(DataQueryTest\ObjectA::class);

        $dq->where('DataQueryTest_A.ID = 2');
        $subDq = $dq->disjunctiveGroup();
        $subDq->where('DataQueryTest_A.Name = \'John\'');
        $subDq->where('DataQueryTest_A.Name = \'Bob\'');

        $this->assertSQLContains(
            "WHERE (DataQueryTest_A.ID = 2) AND ((DataQueryTest_A.Name = 'John') OR (DataQueryTest_A.Name = 'Bob'))",
            $dq->sql($parameters)
        );
    }

    public function testConjunctiveGroup()
    {
        $dq = new DataQuery(DataQueryTest\ObjectA::class);

        $dq->where('DataQueryTest_A.ID = 2');
        $subDq = $dq->conjunctiveGroup();
        $subDq->where('DataQueryTest_A.Name = \'John\'');
        $subDq->where('DataQueryTest_A.Name = \'Bob\'');

        $this->assertSQLContains(
            "WHERE (DataQueryTest_A.ID = 2) AND ((DataQueryTest_A.Name = 'John') AND (DataQueryTest_A.Name = 'Bob'))",
            $dq->sql($parameters)
        );
    }

    /**
     * @todo Test paramaterised
     */
    public function testNestedGroups()
    {
        $dq = new DataQuery(DataQueryTest\ObjectA::class);

        $dq->where('DataQueryTest_A.ID = 2');
        $subDq = $dq->disjunctiveGroup();
        $subDq->where('DataQueryTest_A.Name = \'John\'');
        $subSubDq = $subDq->conjunctiveGroup();
        $subSubDq->where('DataQueryTest_A.Age = 18');
        $subSubDq->where('DataQueryTest_A.Age = 50');
        $subDq->where('DataQueryTest_A.Name = \'Bob\'');

        $this->assertSQLContains(
            "WHERE (DataQueryTest_A.ID = 2) AND ((DataQueryTest_A.Name = 'John') OR ((DataQueryTest_A.Age = 18) "
                . "AND (DataQueryTest_A.Age = 50)) OR (DataQueryTest_A.Name = 'Bob'))",
            $dq->sql($parameters)
        );
    }

    public function testEmptySubgroup()
    {
        $dq = new DataQuery(DataQueryTest\ObjectA::class);
        $dq->conjunctiveGroup();

        // Empty groups should have no where condition at all
        $this->assertSQLNotContains('WHERE', $dq->sql($parameters));
    }

    public function testSubgroupHandoff()
    {
        $dq = new DataQuery(DataQueryTest\ObjectA::class);
        $subDq = $dq->disjunctiveGroup();

        $orgDq = clone $dq;

        $subDq->sort('"DataQueryTest_A"."Name"');
        $orgDq->sort('"DataQueryTest_A"."Name"');

        $this->assertSQLEquals($dq->sql($parameters), $orgDq->sql($parameters));

        $subDq->limit(5, 7);
        $orgDq->limit(5, 7);

        $this->assertSQLEquals($dq->sql($parameters), $orgDq->sql($parameters));
    }

    public function testOrderByMultiple()
    {
        $dq = new DataQuery(SQLSelectTest\TestObject::class);
        $dq = $dq->sort('"Name" ASC, MID("Name", 8, 1) DESC');
        $this->assertContains(
            'ORDER BY "SQLSelectTest_DO"."Name" ASC, "_SortColumn0" DESC',
            $dq->sql($parameters)
        );
    }

    public function testDefaultSort()
    {
        $query = new DataQuery(DataQueryTest\ObjectE::class);
        $result = $query->column('Title');
        $this->assertEquals(array('First', 'Second', 'Last'), $result);
    }

    public function testDistinct()
    {
        $query = new DataQuery(DataQueryTest\ObjectE::class);
        $this->assertContains('SELECT DISTINCT', $query->sql($params), 'Query is set as distinct by default');

        $query = $query->distinct(false);
        $this->assertNotContains('SELECT DISTINCT', $query->sql($params), 'Query does not contain distinct');

        $query = $query->distinct(true);
        $this->assertContains('SELECT DISTINCT', $query->sql($params), 'Query contains distinct');
    }

    public function testComparisonClauseInt()
    {
        DB::query("INSERT INTO \"DataQueryTest_F\" (\"SortOrder\") VALUES (2)");
        $query = new DataQuery(DataQueryTest\ObjectF::class);
        $query->where(DB::get_conn()->comparisonClause('"SortOrder"', '2'));
        $this->assertGreaterThan(0, $query->count(), "Couldn't find SortOrder");
        static::resetDBSchema(true);
    }

    public function testComparisonClauseDateFull()
    {
        DB::query("INSERT INTO \"DataQueryTest_F\" (\"MyDate\") VALUES ('1988-03-04 06:30')");
        $query = new DataQuery(DataQueryTest\ObjectF::class);
        $query->where(DB::get_conn()->comparisonClause('"MyDate"', '1988-03-04%'));
        $this->assertGreaterThan(0, $query->count(), "Couldn't find MyDate");
        static::resetDBSchema(true);
    }

    public function testSurrogateFieldSort()
    {
        $query = new DataQuery(DataQueryTest\ObjectE::class);
        $query->sort(
            sprintf(
                '(case when "Title" = %s then 1 else 0 end)',
                DB::get_conn()->quoteString('Second')
            ),
            'DESC',
            true
        );
        $query->sort('SortOrder', 'ASC', false);
        $query->sort(
            sprintf(
                '(case when "Title" = %s then 0 else 1 end)',
                DB::get_conn()->quoteString('Fourth')
            ),
            'DESC',
            false
        );
        $this->assertEquals(
            $query->execute()->column('Title'),
            $query->column('Title')
        );
    }

    public function testComparisonClauseDateStartsWith()
    {
        DB::query("INSERT INTO \"DataQueryTest_F\" (\"MyDate\") VALUES ('1988-03-04 06:30')");
        $query = new DataQuery(DataQueryTest\ObjectF::class);
        $query->where(DB::get_conn()->comparisonClause('"MyDate"', '1988%'));
        $this->assertGreaterThan(0, $query->count(), "Couldn't find MyDate");
        static::resetDBSchema(true);
    }

    public function testComparisonClauseDateStartsPartial()
    {
        DB::query("INSERT INTO \"DataQueryTest_F\" (\"MyDate\") VALUES ('1988-03-04 06:30')");
        $query = new DataQuery(DataQueryTest\ObjectF::class);
        $query->where(DB::get_conn()->comparisonClause('"MyDate"', '%03-04%'));
        $this->assertGreaterThan(0, $query->count(), "Couldn't find MyDate");
        static::resetDBSchema(true);
    }

    public function testComparisonClauseTextCaseInsensitive()
    {
        DB::query("INSERT INTO \"DataQueryTest_F\" (\"MyString\") VALUES ('HelloWorld')");
        $query = new DataQuery(DataQueryTest\ObjectF::class);
        $query->where(DB::get_conn()->comparisonClause('"MyString"', 'helloworld'));
        $this->assertGreaterThan(0, $query->count(), "Couldn't find MyString");
        static::resetDBSchema(true);
    }

    public function testComparisonClauseTextCaseSensitive()
    {
        DB::query("INSERT INTO \"DataQueryTest_F\" (\"MyString\") VALUES ('HelloWorld')");
        $query = new DataQuery(DataQueryTest\ObjectF::class);
        $query->where(DB::get_conn()->comparisonClause('"MyString"', 'HelloWorld', false, false, true));
        $this->assertGreaterThan(0, $query->count(), "Couldn't find MyString");

        $query2 = new DataQuery(DataQueryTest\ObjectF::class);
        $query2->where(DB::get_conn()->comparisonClause('"MyString"', 'helloworld', false, false, true));
        $this->assertEquals(0, $query2->count(), "Found mystring. Shouldn't be able too.");
        static::resetDBSchema(true);
    }

    /**
     * Tests that getFinalisedQuery can include all tables
     */
    public function testConditionsIncludeTables()
    {
        // Including filter on parent table only doesn't pull in second
        $query = new DataQuery(DataQueryTest\ObjectC::class);
        $query->sort('"SortOrder"');
        $query->where(
            array(
            '"DataQueryTest_C"."Title" = ?' => array('First')
            )
        );
        $result = $query->getFinalisedQuery(array('Title'));
        $from = $result->getFrom();
        $this->assertContains('DataQueryTest_C', array_keys($from));
        $this->assertNotContains('DataQueryTest_E', array_keys($from));

        // Including filter on sub-table requires it
        $query = new DataQuery(DataQueryTest\ObjectC::class);
        $query->sort('"SortOrder"');
        $query->where(
            array(
            '"DataQueryTest_C"."Title" = ? OR "DataQueryTest_E"."SortOrder" > ?' => array(
                'First', 2
            )
            )
        );
        $result = $query->getFinalisedQuery(array('Title'));
        $from = $result->getFrom();

        // Check that including "SortOrder" prompted inclusion of DataQueryTest_E table
        $this->assertContains('DataQueryTest_C', array_keys($from));
        $this->assertContains('DataQueryTest_E', array_keys($from));
        $arrayResult = iterator_to_array($result->execute());
        $first = array_shift($arrayResult);
        $this->assertNotNull($first);
        $this->assertEquals('First', $first['Title']);
        $second = array_shift($arrayResult);
        $this->assertNotNull($second);
        $this->assertEquals('Last', $second['Title']);
        $this->assertEmpty(array_shift($arrayResult));
    }

    public function testColumnReturnsAllValues()
    {
        $first = new DataQueryTest\ObjectA();
        $first->Name = 'Bar';

        $first->write();

        $second = new DataQueryTest\ObjectA();
        $second->Name = 'Foo';
        $second->write();

        $third = new DataQueryTest\ObjectA();
        $third->Name = 'Bar';
        $third->write();

        $result = DataQueryTest\ObjectA::get()->column('Name');
        $this->assertEquals(['Bar', 'Foo', 'Bar'], $result);
    }

    public function testColumnUniqueReturnsAllValues()
    {
        $first = new DataQueryTest\ObjectA();
        $first->Name = 'Bar';

        $first->write();

        $second = new DataQueryTest\ObjectA();
        $second->Name = 'Foo';
        $second->write();

        $third = new DataQueryTest\ObjectA();
        $third->Name = 'Bar';
        $third->write();

        $result = DataQueryTest\ObjectA::get()->columnUnique('Name');
        $this->assertCount(2, $result);
        $this->assertContains('Bar', $result);
        $this->assertContains('Foo', $result);
    }
}


1			<?php
2
3			namespace SilverStripe\ORM\Tests;
4
5			use SilverStripe\ORM\Connect\Query;
6			use SilverStripe\ORM\DataQuery;
7			use SilverStripe\ORM\DataObject;
8			use SilverStripe\ORM\DB;
9			use SilverStripe\Dev\SapphireTest;
10			use SilverStripe\Security\Member;
11
12			/**
13			* @skipUpgrade
14			*/
15			class DataQueryTest extends SapphireTest
16			{
17
18			protected static $fixture_file = 'DataQueryTest.yml';
19
20			protected static $extra_dataobjects = array(
21			DataQueryTest\ObjectA::class,
22			DataQueryTest\ObjectB::class,
23			DataQueryTest\ObjectC::class,
24			DataQueryTest\ObjectD::class,
25			DataQueryTest\ObjectE::class,
26			DataQueryTest\ObjectF::class,
27			DataQueryTest\ObjectG::class,
28			SQLSelectTest\TestObject::class,
29			SQLSelectTest\TestBase::class,
30			SQLSelectTest\TestChild::class,
31			);
32
33			public function testSortByJoinedFieldRetainsSourceInformation()
34			{
35			$bar = new DataQueryTest\ObjectC();
36			$bar->Title = "Bar";
37			$bar->write();
38
39			$foo = new DataQueryTest\ObjectB();
40			$foo->Title = "Foo";
41			$foo->TestC = $bar->ID;
			0 ignored issues – show Bug Best Practice introduced 2017-11-28 04:26 UTC by Report Bug Copy Issue Report The property `TestC` does not exist on `SilverStripe\ORM\Tests\DataQueryTest\ObjectB`. Since you implemented `__set`, consider adding a @property annotation. Loading history...
42			$foo->write();
43
44			$query = new DataQuery(DataQueryTest\ObjectB::class);
45			$result = $query->leftJoin(
46			'DataQueryTest_C',
47			"\"DataQueryTest_B\".\"TestCID\" = \"DataQueryTest_B\".\"ID\""
48			)->sort('"DataQueryTest_B"."Title"', 'ASC');
49
50			$result = $result->execute()->record();
51			$this->assertEquals('Foo', $result['Title']);
52			}
53
54			/**
55			* Test the leftJoin() and innerJoin method of the DataQuery object
56			*/
57			public function testJoins()
58			{
59			$dq = new DataQuery(Member::class);
60			$dq->innerJoin("Group_Members", "\"Group_Members\".\"MemberID\" = \"Member\".\"ID\"");
61			$this->assertSQLContains(
62			"INNER JOIN \"Group_Members\" ON \"Group_Members\".\"MemberID\" = \"Member\".\"ID\"",
63			$dq->sql($parameters)
64			);
65
66			$dq = new DataQuery(Member::class);
67			$dq->leftJoin("Group_Members", "\"Group_Members\".\"MemberID\" = \"Member\".\"ID\"");
68			$this->assertSQLContains(
69			"LEFT JOIN \"Group_Members\" ON \"Group_Members\".\"MemberID\" = \"Member\".\"ID\"",
70			$dq->sql($parameters)
71			);
72			}
73
74			public function testApplyRelation()
75			{
76			// Test applyRelation with two has_ones pointing to the same class
77			$dq = new DataQuery(DataQueryTest\ObjectB::class);
78			$dq->applyRelation('TestC');
79			$this->assertTrue($dq->query()->isJoinedTo('testc_DataQueryTest_C'));
80			$this->assertContains('"testc_DataQueryTest_C"."ID" = "DataQueryTest_B"."TestCID"', $dq->sql());
81
82			$dq = new DataQuery(DataQueryTest\ObjectB::class);
83			$dq->applyRelation('TestCTwo');
84			$this->assertTrue($dq->query()->isJoinedTo('testctwo_DataQueryTest_C'));
85			$this->assertContains('"testctwo_DataQueryTest_C"."ID" = "DataQueryTest_B"."TestCTwoID"', $dq->sql());
86			}
87
88			public function testApplyRelationDeepInheritance()
89			{
90			//test has_one relation
91			$newDQ = new DataQuery(DataQueryTest\ObjectE::class);
92			//apply a relation to a relation from an ancestor class
93			$newDQ->applyRelation('TestA');
94			$this->assertTrue($newDQ->query()->isJoinedTo('DataQueryTest_C'));
95			$this->assertContains('"testa_DataQueryTest_A"."ID" = "DataQueryTest_C"."TestAID"', $newDQ->sql($params));
96
97			//test many_many relation
98
99			//test many_many with separate inheritance
100			$newDQ = new DataQuery(DataQueryTest\ObjectC::class);
101			$baseDBTable = DataObject::getSchema()->baseDataTable(DataQueryTest\ObjectC::class);
102			$newDQ->applyRelation('ManyTestAs');
103			//check we are "joined" to the DataObject's table (there is no distinction between FROM or JOIN clauses)
104			$this->assertTrue($newDQ->query()->isJoinedTo($baseDBTable));
105			//check we are explicitly selecting "FROM" the DO's table
106			$this->assertContains("FROM \"$baseDBTable\"", $newDQ->sql());
107
108			//test many_many with shared inheritance
109			$newDQ = new DataQuery(DataQueryTest\ObjectE::class);
110			$baseDBTable = DataObject::getSchema()->baseDataTable(DataQueryTest\ObjectE::class);
111			//check we are "joined" to the DataObject's table (there is no distinction between FROM or JOIN clauses)
112			$this->assertTrue($newDQ->query()->isJoinedTo($baseDBTable));
113			//check we are explicitly selecting "FROM" the DO's table
114			$this->assertContains("FROM \"$baseDBTable\"", $newDQ->sql(), 'The FROM clause is missing from the query');
115			$newDQ->applyRelation('ManyTestGs');
116			//confirm we are still joined to the base table
117			$this->assertTrue($newDQ->query()->isJoinedTo($baseDBTable));
118			//double check it is the "FROM" clause
119			$this->assertContains(
120			"FROM \"$baseDBTable\"",
121			$newDQ->sql(),
122			'The FROM clause has been removed from the query'
123			);
124			//another (potentially less crude check) for checking "FROM" clause
125			$fromTables = $newDQ->query()->getFrom();
126			$this->assertEquals('"' . $baseDBTable . '"', $fromTables[$baseDBTable]);
127			}
128
129			public function testRelationReturn()
130			{
131			$dq = new DataQuery(DataQueryTest\ObjectC::class);
132			$this->assertEquals(
133			DataQueryTest\ObjectA::class,
134			$dq->applyRelation('TestA'),
135			'DataQuery::applyRelation should return the name of the related object.'
136			);
137			$this->assertEquals(
138			DataQueryTest\ObjectA::class,
139			$dq->applyRelation('TestAs'),
140			'DataQuery::applyRelation should return the name of the related object.'
141			);
142			$this->assertEquals(
143			DataQueryTest\ObjectA::class,
144			$dq->applyRelation('ManyTestAs'),
145			'DataQuery::applyRelation should return the name of the related object.'
146			);
147
148			$this->assertEquals(
149			DataQueryTest\ObjectB::class,
150			$dq->applyRelation('TestB'),
151			'DataQuery::applyRelation should return the name of the related object.'
152			);
153			$this->assertEquals(
154			DataQueryTest\ObjectB::class,
155			$dq->applyRelation('TestBs'),
156			'DataQuery::applyRelation should return the name of the related object.'
157			);
158			$this->assertEquals(
159			DataQueryTest\ObjectB::class,
160			$dq->applyRelation('ManyTestBs'),
161			'DataQuery::applyRelation should return the name of the related object.'
162			);
163			$newDQ = new DataQuery(DataQueryTest\ObjectE::class);
164			$this->assertEquals(
165			DataQueryTest\ObjectA::class,
166			$newDQ->applyRelation('TestA'),
167			'DataQuery::applyRelation should return the name of the related object.'
168			);
169			}
170
171			public function testRelationOrderWithCustomJoin()
172			{
173			$dataQuery = new DataQuery(DataQueryTest\ObjectB::class);
174			$dataQuery->innerJoin('DataQueryTest_D', '"DataQueryTest_D"."RelationID" = "DataQueryTest_B"."ID"');
175			$this->assertInstanceOf(Query::class, $dataQuery->execute());
176			}
177
178			public function testDisjunctiveGroup()
179			{
180			$dq = new DataQuery(DataQueryTest\ObjectA::class);
181
182			$dq->where('DataQueryTest_A.ID = 2');
183			$subDq = $dq->disjunctiveGroup();
184			$subDq->where('DataQueryTest_A.Name = \'John\'');
185			$subDq->where('DataQueryTest_A.Name = \'Bob\'');
186
187			$this->assertSQLContains(
188			"WHERE (DataQueryTest_A.ID = 2) AND ((DataQueryTest_A.Name = 'John') OR (DataQueryTest_A.Name = 'Bob'))",
189			$dq->sql($parameters)
190			);
191			}
192
193			public function testConjunctiveGroup()
194			{
195			$dq = new DataQuery(DataQueryTest\ObjectA::class);
196
197			$dq->where('DataQueryTest_A.ID = 2');
198			$subDq = $dq->conjunctiveGroup();
199			$subDq->where('DataQueryTest_A.Name = \'John\'');
200			$subDq->where('DataQueryTest_A.Name = \'Bob\'');
201
202			$this->assertSQLContains(
203			"WHERE (DataQueryTest_A.ID = 2) AND ((DataQueryTest_A.Name = 'John') AND (DataQueryTest_A.Name = 'Bob'))",
204			$dq->sql($parameters)
205			);
206			}
207
208			/**
209			* @todo Test paramaterised
210			*/
211			public function testNestedGroups()
212			{
213			$dq = new DataQuery(DataQueryTest\ObjectA::class);
214
215			$dq->where('DataQueryTest_A.ID = 2');
216			$subDq = $dq->disjunctiveGroup();
217			$subDq->where('DataQueryTest_A.Name = \'John\'');
218			$subSubDq = $subDq->conjunctiveGroup();
219			$subSubDq->where('DataQueryTest_A.Age = 18');
220			$subSubDq->where('DataQueryTest_A.Age = 50');
221			$subDq->where('DataQueryTest_A.Name = \'Bob\'');
222
223			$this->assertSQLContains(
224			"WHERE (DataQueryTest_A.ID = 2) AND ((DataQueryTest_A.Name = 'John') OR ((DataQueryTest_A.Age = 18) "
225			. "AND (DataQueryTest_A.Age = 50)) OR (DataQueryTest_A.Name = 'Bob'))",
226			$dq->sql($parameters)
227			);
228			}
229
230			public function testEmptySubgroup()
231			{
232			$dq = new DataQuery(DataQueryTest\ObjectA::class);
233			$dq->conjunctiveGroup();
234
235			// Empty groups should have no where condition at all
236			$this->assertSQLNotContains('WHERE', $dq->sql($parameters));
237			}
238
239			public function testSubgroupHandoff()
240			{
241			$dq = new DataQuery(DataQueryTest\ObjectA::class);
242			$subDq = $dq->disjunctiveGroup();
243
244			$orgDq = clone $dq;
245
246			$subDq->sort('"DataQueryTest_A"."Name"');
247			$orgDq->sort('"DataQueryTest_A"."Name"');
248
249			$this->assertSQLEquals($dq->sql($parameters), $orgDq->sql($parameters));
250
251			$subDq->limit(5, 7);
252			$orgDq->limit(5, 7);
253
254			$this->assertSQLEquals($dq->sql($parameters), $orgDq->sql($parameters));
255			}
256
257			public function testOrderByMultiple()
258			{
259			$dq = new DataQuery(SQLSelectTest\TestObject::class);
260			$dq = $dq->sort('"Name" ASC, MID("Name", 8, 1) DESC');
261			$this->assertContains(
262			'ORDER BY "SQLSelectTest_DO"."Name" ASC, "_SortColumn0" DESC',
263			$dq->sql($parameters)
264			);
265			}
266
267			public function testDefaultSort()
268			{
269			$query = new DataQuery(DataQueryTest\ObjectE::class);
270			$result = $query->column('Title');
271			$this->assertEquals(array('First', 'Second', 'Last'), $result);
272			}
273
274			public function testDistinct()
275			{
276			$query = new DataQuery(DataQueryTest\ObjectE::class);
277			$this->assertContains('SELECT DISTINCT', $query->sql($params), 'Query is set as distinct by default');
278
279			$query = $query->distinct(false);
280			$this->assertNotContains('SELECT DISTINCT', $query->sql($params), 'Query does not contain distinct');
281
282			$query = $query->distinct(true);
283			$this->assertContains('SELECT DISTINCT', $query->sql($params), 'Query contains distinct');
284			}
285
286			public function testComparisonClauseInt()
287			{
288			DB::query("INSERT INTO \"DataQueryTest_F\" (\"SortOrder\") VALUES (2)");
289			$query = new DataQuery(DataQueryTest\ObjectF::class);
290			$query->where(DB::get_conn()->comparisonClause('"SortOrder"', '2'));
291			$this->assertGreaterThan(0, $query->count(), "Couldn't find SortOrder");
292			static::resetDBSchema(true);
293			}
294
295			public function testComparisonClauseDateFull()
296			{
297			DB::query("INSERT INTO \"DataQueryTest_F\" (\"MyDate\") VALUES ('1988-03-04 06:30')");
298			$query = new DataQuery(DataQueryTest\ObjectF::class);
299			$query->where(DB::get_conn()->comparisonClause('"MyDate"', '1988-03-04%'));
300			$this->assertGreaterThan(0, $query->count(), "Couldn't find MyDate");
301			static::resetDBSchema(true);
302			}
303
304			public function testSurrogateFieldSort()
305			{
306			$query = new DataQuery(DataQueryTest\ObjectE::class);
307			$query->sort(
308			sprintf(
309			'(case when "Title" = %s then 1 else 0 end)',
310			DB::get_conn()->quoteString('Second')
311			),
312			'DESC',
313			true
314			);
315			$query->sort('SortOrder', 'ASC', false);
316			$query->sort(
317			sprintf(
318			'(case when "Title" = %s then 0 else 1 end)',
319			DB::get_conn()->quoteString('Fourth')
320			),
321			'DESC',
322			false
323			);
324			$this->assertEquals(
325			$query->execute()->column('Title'),
326			$query->column('Title')
327			);
328			}
329
330			public function testComparisonClauseDateStartsWith()
331			{
332			DB::query("INSERT INTO \"DataQueryTest_F\" (\"MyDate\") VALUES ('1988-03-04 06:30')");
333			$query = new DataQuery(DataQueryTest\ObjectF::class);
334			$query->where(DB::get_conn()->comparisonClause('"MyDate"', '1988%'));
335			$this->assertGreaterThan(0, $query->count(), "Couldn't find MyDate");
336			static::resetDBSchema(true);
337			}
338
339			public function testComparisonClauseDateStartsPartial()
340			{
341			DB::query("INSERT INTO \"DataQueryTest_F\" (\"MyDate\") VALUES ('1988-03-04 06:30')");
342			$query = new DataQuery(DataQueryTest\ObjectF::class);
343			$query->where(DB::get_conn()->comparisonClause('"MyDate"', '%03-04%'));
344			$this->assertGreaterThan(0, $query->count(), "Couldn't find MyDate");
345			static::resetDBSchema(true);
346			}
347
348			public function testComparisonClauseTextCaseInsensitive()
349			{
350			DB::query("INSERT INTO \"DataQueryTest_F\" (\"MyString\") VALUES ('HelloWorld')");
351			$query = new DataQuery(DataQueryTest\ObjectF::class);
352			$query->where(DB::get_conn()->comparisonClause('"MyString"', 'helloworld'));
353			$this->assertGreaterThan(0, $query->count(), "Couldn't find MyString");
354			static::resetDBSchema(true);
355			}
356
357			public function testComparisonClauseTextCaseSensitive()
358			{
359			DB::query("INSERT INTO \"DataQueryTest_F\" (\"MyString\") VALUES ('HelloWorld')");
360			$query = new DataQuery(DataQueryTest\ObjectF::class);
361			$query->where(DB::get_conn()->comparisonClause('"MyString"', 'HelloWorld', false, false, true));
362			$this->assertGreaterThan(0, $query->count(), "Couldn't find MyString");
363
364			$query2 = new DataQuery(DataQueryTest\ObjectF::class);
365			$query2->where(DB::get_conn()->comparisonClause('"MyString"', 'helloworld', false, false, true));
366			$this->assertEquals(0, $query2->count(), "Found mystring. Shouldn't be able too.");
367			static::resetDBSchema(true);
368			}
369
370			/**
371			* Tests that getFinalisedQuery can include all tables
372			*/
373			public function testConditionsIncludeTables()
374			{
375			// Including filter on parent table only doesn't pull in second
376			$query = new DataQuery(DataQueryTest\ObjectC::class);
377			$query->sort('"SortOrder"');
378			$query->where(
379			array(
380			'"DataQueryTest_C"."Title" = ?' => array('First')
381			)
382			);
383			$result = $query->getFinalisedQuery(array('Title'));
384			$from = $result->getFrom();
385			$this->assertContains('DataQueryTest_C', array_keys($from));
386			$this->assertNotContains('DataQueryTest_E', array_keys($from));
387
388			// Including filter on sub-table requires it
389			$query = new DataQuery(DataQueryTest\ObjectC::class);
390			$query->sort('"SortOrder"');
391			$query->where(
392			array(
393			'"DataQueryTest_C"."Title" = ? OR "DataQueryTest_E"."SortOrder" > ?' => array(
394			'First', 2
395			)
396			)
397			);
398			$result = $query->getFinalisedQuery(array('Title'));
399			$from = $result->getFrom();
400
401			// Check that including "SortOrder" prompted inclusion of DataQueryTest_E table
402			$this->assertContains('DataQueryTest_C', array_keys($from));
403			$this->assertContains('DataQueryTest_E', array_keys($from));
404			$arrayResult = iterator_to_array($result->execute());
405			$first = array_shift($arrayResult);
406			$this->assertNotNull($first);
407			$this->assertEquals('First', $first['Title']);
408			$second = array_shift($arrayResult);
409			$this->assertNotNull($second);
410			$this->assertEquals('Last', $second['Title']);
411			$this->assertEmpty(array_shift($arrayResult));
412			}
413
414			public function testColumnReturnsAllValues()
415			{
416			$first = new DataQueryTest\ObjectA();
417			$first->Name = 'Bar';
			0 ignored issues – show Bug Best Practice introduced 2018-07-16 10:53 UTC by Report Bug Copy Issue Report The property `Name` does not exist on `SilverStripe\ORM\Tests\DataQueryTest\ObjectA`. Since you implemented `__set`, consider adding a @property annotation. Loading history...
418			$first->write();
419
420			$second = new DataQueryTest\ObjectA();
421			$second->Name = 'Foo';
422			$second->write();
423
424			$third = new DataQueryTest\ObjectA();
425			$third->Name = 'Bar';
426			$third->write();
427
428			$result = DataQueryTest\ObjectA::get()->column('Name');
429			$this->assertEquals(['Bar', 'Foo', 'Bar'], $result);
430			}
431
432			public function testColumnUniqueReturnsAllValues()
433			{
434			$first = new DataQueryTest\ObjectA();
435			$first->Name = 'Bar';
			0 ignored issues – show Bug Best Practice introduced 2018-07-05 09:41 UTC by Report Bug Copy Issue Report The property `Name` does not exist on `SilverStripe\ORM\Tests\DataQueryTest\ObjectA`. Since you implemented `__set`, consider adding a @property annotation. Loading history...
436			$first->write();
437
438			$second = new DataQueryTest\ObjectA();
439			$second->Name = 'Foo';
440			$second->write();
441
442			$third = new DataQueryTest\ObjectA();
443			$third->Name = 'Bar';
444			$third->write();
445
446			$result = DataQueryTest\ObjectA::get()->columnUnique('Name');
447			$this->assertCount(2, $result);
448			$this->assertContains('Bar', $result);
449			$this->assertContains('Foo', $result);
450			}
451			}
452

silverstripe / silverstripe-framework

Push — master ( 72e173...aab521 )

DataQueryTest::testSurrogateFieldSort() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like