db

1
<?php
2

3
namespace Upside\Db\Test\SQL;
4

5
use Upside\Db\SQL\Expression;
6
use Upside\Db\SQL\Join;
7

8
class JoinTest extends BaseClass
9
{
10
    public function testDefaultJoin()
11
    {
12
        $expected = 'SELECT * FROM "users" INNER JOIN "profiles" ON "users"."id" = "profiles"."id"';
13
        $actual = $this->db->from('users')
14
            ->join('profiles', function (Join $join) {
15
                $join->on('users.id', 'profiles.id');
16
            })
17
            ->select();
18
        $this->assertEquals($expected, $actual);
19
    }
20

21
    public function testDefaultJoinGTE()
22
    {
23
        $expected = 'SELECT * FROM "users" INNER JOIN "profiles" ON "users"."id" >= "profiles"."id"';
24
        $actual = $this->db->from('users')
25
            ->join('profiles', function (Join $join) {
26
                $join->on('users.id', 'profiles.id', '>=');
27
            })
28
            ->select();
29
        $this->assertEquals($expected, $actual);
30
    }
31

32
    public function testDefaultJoinAnd()
33
    {
34
        $expected = 'SELECT * FROM "users" INNER JOIN "profiles" ON "users"."id" = "profiles"."id" AND "users"."email" = "profile"."primary_email"';
35
        $actual = $this->db->from('users')
36
            ->join('profiles', function (Join $join) {
37
                $join->on('users.id', 'profiles.id')
38
                    ->and_on('users.email', 'profile.primary_email');
39
            })
40
            ->select();
41
        $this->assertEquals($expected, $actual);
42
    }
43

44
    public function testDefaultJoinOr()
45
    {
46
        $expected = 'SELECT * FROM "users" INNER JOIN "profiles" ON "users"."id" = "profiles"."id" OR "users"."email" = "profile"."primary_email"';
47
        $actual = $this->db->from('users')
48
            ->join('profiles', function (Join $join) {
49
                $join->on('users.id', 'profiles.id')
50
                    ->or_on('users.email', 'profile.primary_email');
51
            })
52
            ->select();
53
        $this->assertEquals($expected, $actual);
54
    }
55

56
    public function testDefaultJoinGroup()
57
    {
58
        $expected = 'SELECT * FROM "users" INNER JOIN "profiles" ON "users"."id" = "profiles"."id" AND ("users"."email" = "profiles"."primary_email" OR "users"."email" = "profiles"."secondary_email")';
59
        $actual = $this->db->from('users')
60
            ->join('profiles', function (Join $join) {
61
                $join->on('users.id', 'profiles.id')
62
                    ->and_on(function (Join $join) {
63
                        $join->on('users.email', 'profiles.primary_email')
64
                            ->or_on('users.email', 'profiles.secondary_email');
65
                    });
66
            })
67
            ->select();
68
        $this->assertEquals($expected, $actual);
69
    }
70

71
    public function testDefaultJoinAlias()
72
    {
73
        $expected = 'SELECT * FROM "users" INNER JOIN "profiles" AS "p" ON "users"."id" = "p"."id"';
74
        $actual = $this->db->from('users')
75
            ->join(['profiles' => 'p'], function (Join $join) {
76
                $join->on('users.id', 'p.id');
77
            })
78
            ->select();
79
        $this->assertEquals($expected, $actual);
80
    }
81

82
    public function testCrossJoin()
83
    {
84
        $expected = 'SELECT * FROM "users" CROSS JOIN "profiles"';
85
        $actual = $this->db->from('users')
86
            ->cross_join('profiles')
87
            ->select();
88
        $this->assertEquals($expected, $actual);
89
    }
90

91
    public function testJoinExpression()
92
    {
93
        $expected = 'SELECT * FROM "users" INNER JOIN "profiles" ON "users"."id" = LEN("profiles"."name")';
94
        $actual = $this->db->from('users')
95
            ->join('profiles', function (Join $join) {
96
                $join->on(function (Expression $expr) {
97
                    $expr->column('users.id')->{'='}->len('profiles.name');
98
                }, true);
99
            })
100
            ->select();
101
        $this->assertEquals($expected, $actual);
102
    }
103
}