Symfony3 Doctrine2 自定义 DQL 函数

发表于 | 分类于

在 Symfony 中使用 mysql 函数时,有些函数需要注册自定义 DQL 函数来实现,否则无法使用
自定义 DQL 函数时需要查看的两个文件：
verdor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php
verdor/doctrine/orm/lib/Doctrine/ORM/Query/Lexer.php

需要实现的三个函数, DATE_FORMAT, GLength, If

1
2
3
4
5
6
7
8
9
10
11
# app/config/config.yml
# Doctrine Configuration
doctrine:
    orm:
        dql:
            datetime_functions:
                DATE_FORMAT: Bundles\CommonBundle\DQL\DateFormatFunction
            numeric_functions:
                GLength: Bundles\CommonBundle\DQL\GLengthFunction
            string_functions:
                If: Bundles\CommonBundle\DQL\IfFunction

datetime_functions、numeric_functions、string_functions 是固定的,必须按此命名
DateFormat、GLength、If 命名可以自定义,但是SQL调用的时候,必须与这里定义的一致

DATE_FORMAT 用于格式化字段类型 datetime

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
// Bundles/CommonBundle/DQL/DateFormatFunction.php
<?php
namespace Bundles\CommonBundle\DQL;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

/**
 * DATE_FORMAT("StringPrimary", "StringPrimary")
 * 日期格式化
 */
class DateFormatFunction extends FunctionNode
{
    protected $dateExpression;
    protected $formatString;

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->dateExpression = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->formatString = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker)
    {
        return 'DATE_FORMAT(' . $sqlWalker->walkStringPrimary($this->dateExpression)
            . ',' . $sqlWalker->walkStringPrimary($this->formatString) . ')';
    }

}

IF 函数,此函数有2种用法

赋值 u.mobile = IF(u.age = 1, ‘12345678901’, ‘12345678902’)
IF(条件, true执行条件, false执行条件)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
// Bundles/CommonBundle/DQL/IfFunction.php
<?php
namespace Bundles\CommonBundle\DQL;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

/**
 * IF("ConditionalExpression", "ArithmeticExpression", "ArithmeticExpression")
 */
class IfFunction extends FunctionNode
{
    private $expr = [];

    /**
     * WHERE u.mobile = IF(u.age = 1, '12345678901', '12345678902')
     */
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->expr[] = $parser->ConditionalExpression();
        for ($i = 0; $i < 2; $i++) {
            $parser->match(Lexer::T_COMMA);
            $this->expr[] = $parser->ArithmeticPrimary();
        }
        
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }


    /**
     * WHERE 1 = IF(u.age = 1, u.mobile = '12345678901', u.status = '1')
     */
    /*public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->expr[] = $parser->ConditionalExpression();
        for ($i = 0; $i < 2; $i++) {
            $parser->match(Lexer::T_COMMA);
            $this->expr[] = $parser->ConditionalExpression();
        }
        
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }*/

    public function getSql(SqlWalker $sqlWalker)
    {
        return sprintf('If(%s, %s, %s)',
            $sqlWalker->walkConditionalExpression($this->expr[0]),
            $sqlWalker->walkArithmeticPrimary($this->expr[1]),
            $sqlWalker->walkArithmeticPrimary($this->expr[2]));
    }

    /**
     * WHERE 1 = IF(u.age = 1, 1, DATE_FORMAT(u.createdAt, '%m-%d') < '09-29')
     */
    /*public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);          // 左括号
 
        $this->expr[] = $parser->ConditionalExpression();   // u.age = 1
        $parser->match(Lexer::T_COMMA);                     // 符号 ','
 
        $this->expr[] = $parser->ArithmeticPrimary();       // 1
        $parser->match(Lexer::T_COMMA);                     // 符号 ','
 
        $this->expr[] = $parser->StringPrimary();           // u.createdAt, '%m-%d'
        $parser->match(Lexer::T_LOWER_THAN);                // 符号 '<'
        $this->expr[] = $parser->StringPrimary();           // 09-29
 
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);         // 右括号
    }
 
    public function getSql(SqlWalker $sqlWalker)
    {
        return sprintf('If(%s, %s, %s)',
            $sqlWalker->walkConditionalExpression($this->expr[0]),
            $sqlWalker->walkArithmeticPrimary($this->expr[1]),
            $sqlWalker->walkStringPrimary($this->expr[2]) . '<' . $sqlWalker->walkStringPrimary($this->expr[3]));
    }*/
 
    /**
     * 基于Doctrine2, 此处写法为 1 = IF(), 不能直接 IF()
     * WHERE("1 = IF(u.age = 1, u.id IN(1, 2), 1)")
     */
    /*public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
 
        $this->expr[] = $parser->ConditionalExpression();
        $parser->match(Lexer::T_COMMA);
 
        $this->expr[] = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_IN);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->concatExpressions[] = $parser->ArithmeticPrimary();
        // 判断下一个字符是不是逗号
        while ($parser->getLexer()->isNextToken(Lexer::T_COMMA)) {
            $parser->match(Lexer::T_COMMA);
            $this->concatExpressions[] = $parser->ArithmeticPrimary();
        }
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
        $parser->match(Lexer::T_COMMA);
 
        $this->expr[] = $parser->ArithmeticPrimary();
 
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
 
    public function getSql(SqlWalker $sqlWalker)
    {
        $params = [];
        foreach ($this->concatExpressions as $expression) {
            $params[] = $sqlWalker->walkArithmeticPrimary($expression);
        }
 
        return sprintf('If(%s, %s, %s)',
            $sqlWalker->walkConditionalExpression($this->expr[0]),
            $sqlWalker->walkArithmeticPrimary($this->expr[1]) . ' IN(' . implode(',', $params) . ')',
            $sqlWalker->walkStringPrimary($this->expr[2]));
    }*/
}

GLength 函数,配合相关函数计算两点间的距离

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
// Bundles/CommonBundle/DQL/GLengthFunction.php
<?php
namespace Bundles\CommonBundle\DQL;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

/**
 * GLength
 * MySql 调用 GLength(GeomFromText(CONCAT('LineString(', from_lat, ' ', from_lng, ',', to_lat, ' ', to_lng, ')') / 0.0000092592666666667
 * EntityRepository 调用 GLength(from_lat, from_lng, to_lat, to_lng) / 0.0000092592666666667
 */
class GLengthFunction extends FunctionNode
{
    public $onePriamry;
    public $twoPriamry;
    public $threePriamry;
    public $fourPriamry;

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->onePriamry = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->twoPriamry = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->threePriamry = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->fourPriamry = $parser->ArithmeticPrimary();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker)
    {
        return "GLength(GeomFromText(CONCAT('LineString({$sqlWalker->walkArithmeticPrimary($this->onePriamry)} {$sqlWalker->walkArithmeticPrimary($this->twoPriamry)},',{$sqlWalker->walkStringPrimary($this->threePriamry)},' ',{$sqlWalker->walkStringPrimary($this->fourPriamry)},')')))";
    }
}
0%