db

1
<?php
2

3
namespace Upside\Db\Test\SQL;
4

5
use Upside\Db\SQL\Expression;
6

7
class OrderTest extends BaseClass
8
{
9
    public function testOrderAsc()
10
    {
11
        $expected = 'SELECT * FROM "users" ORDER BY "name" ASC';
12
        $actual = $this->db->from('users')->order_by('name')->select();
13
        $this->assertEquals($expected, $actual);
14
    }
15

16
    public function testOrderDesc()
17
    {
18
        $expected = 'SELECT * FROM "users" ORDER BY "name" DESC';
19
        $actual = $this->db->from('users')->order_by('name', 'desc')->select();
20
        $this->assertEquals($expected, $actual);
21
    }
22

23
    public function testOrderMultipleColumnsAsc()
24
    {
25
        $expected = 'SELECT * FROM "users" ORDER BY "name", "age" ASC';
26
        $actual = $this->db->from('users')->order_by(['name', 'age'])->select();
27
        $this->assertEquals($expected, $actual);
28
    }
29

30
    public function testOrderMultipleColumnsDesc()
31
    {
32
        $expected = 'SELECT * FROM "users" ORDER BY "name", "age" DESC';
33
        $actual = $this->db->from('users')->order_by(['name', 'age'], 'desc')->select();
34
        $this->assertEquals($expected, $actual);
35
    }
36

37
    public function testOrderAscDesc()
38
    {
39
        $expected = 'SELECT * FROM "users" ORDER BY "name" ASC, "age" DESC';
40
        $actual = $this->db->from('users')->order_by('name')->order_by('age', 'desc')->select();
41
        $this->assertEquals($expected, $actual);
42
    }
43

44
    public function testOrderNullsFirst()
45
    {
46
        $expected = 'SELECT * FROM "users" ORDER BY "name" ASC, (CASE WHEN "age" IS NULL THEN 0 ELSE 1 END), "age" DESC';
47
        $actual = $this->db->from('users')->order_by('name')->order_by('age', 'desc', 'nulls first')->select();
48
        $this->assertEquals($expected, $actual);
49
    }
50

51
    public function testOrderNullsLast()
52
    {
53
        $expected = 'SELECT * FROM "users" ORDER BY "name" ASC, (CASE WHEN "age" IS NULL THEN 1 ELSE 0 END), "age" DESC';
54
        $actual = $this->db->from('users')->order_by('name')->order_by('age', 'desc', 'nulls last')->select();
55
        $this->assertEquals($expected, $actual);
56
    }
57

58
    public function testOrderExpression()
59
    {
60
        $expected = 'SELECT * FROM "users" ORDER BY LEN("name") ASC';
61
        $actual = $this->db->from('users')->order_by(function (Expression $expr) {
62
            $expr->len('name');
63
        })->select();
64
        $this->assertEquals($expected, $actual);
65
    }
66
}