DataQueryTest::testSurrogateFieldSort() - Code Metrics - Inspection of "Merge pull request #9004 from blueskies79/patch-2" - silverstripe/silverstripe-framework - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — 4 ( 6678f9...487beb )

by Loz

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

silverstripe / silverstripe-framework

Push — 4 ( 6678f9...487beb )

DataQueryTest::testSurrogateFieldSort() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like