1 | <?php |
||
2 | /* |
||
3 | * @copyright 2018 Mautic Contributors. All rights reserved |
||
4 | * @author Mautic, Inc. |
||
5 | * |
||
6 | * @link https://mautic.org |
||
7 | * |
||
8 | * @license GNU/GPLv3 http://www.gnu.org/licenses/gpl-3.0.html |
||
9 | */ |
||
10 | |||
11 | namespace Mautic\LeadBundle\Segment\Query\Filter; |
||
12 | |||
13 | use Mautic\LeadBundle\Segment\ContactSegmentFilter; |
||
14 | use Mautic\LeadBundle\Segment\Query\QueryBuilder; |
||
15 | |||
16 | /** |
||
17 | * Class ForeignValueFilterQueryBuilder. |
||
18 | */ |
||
19 | class ForeignValueFilterQueryBuilder extends BaseFilterQueryBuilder |
||
20 | { |
||
21 | /** {@inheritdoc} */ |
||
22 | public static function getServiceId() |
||
23 | { |
||
24 | return 'mautic.lead.query.builder.foreign.value'; |
||
25 | } |
||
26 | |||
27 | /** {@inheritdoc} */ |
||
28 | public function applyQuery(QueryBuilder $queryBuilder, ContactSegmentFilter $filter) |
||
29 | { |
||
30 | $filterOperator = $filter->getOperator(); |
||
31 | |||
32 | $filterParameters = $filter->getParameterValue(); |
||
33 | |||
34 | if (is_array($filterParameters)) { |
||
35 | $parameters = []; |
||
36 | foreach ($filterParameters as $filterParameter) { |
||
37 | $parameters[] = $this->generateRandomParameterName(); |
||
38 | } |
||
39 | } else { |
||
40 | $parameters = $this->generateRandomParameterName(); |
||
41 | } |
||
42 | |||
43 | $filterParametersHolder = $filter->getParameterHolder($parameters); |
||
44 | |||
45 | $tableAlias = $this->generateRandomParameterName(); |
||
46 | |||
47 | $subQueryBuilder = $queryBuilder->getConnection()->createQueryBuilder(); |
||
48 | $subQueryBuilder |
||
49 | ->select('NULL')->from($filter->getTable(), $tableAlias) |
||
50 | ->andWhere($tableAlias.'.lead_id = l.id'); |
||
51 | |||
52 | if (!is_null($filter->getWhere())) { |
||
53 | $subQueryBuilder->andWhere(str_replace(str_replace(MAUTIC_TABLE_PREFIX, '', $filter->getTable()).'.', $tableAlias.'.', $filter->getWhere())); |
||
54 | } |
||
55 | |||
56 | switch ($filterOperator) { |
||
57 | case 'empty': |
||
58 | $subQueryBuilder->andWhere($subQueryBuilder->expr()->isNull($tableAlias.'.'.$filter->getField())); |
||
59 | $queryBuilder->addLogic($queryBuilder->expr()->exists($subQueryBuilder->getSQL()), $filter->getGlue()); |
||
60 | break; |
||
61 | case 'notEmpty': |
||
62 | $subQueryBuilder->andWhere($subQueryBuilder->expr()->isNotNull($tableAlias.'.'.$filter->getField())); |
||
63 | $queryBuilder->addLogic($queryBuilder->expr()->exists($subQueryBuilder->getSQL()), $filter->getGlue()); |
||
64 | break; |
||
65 | case 'notIn': |
||
66 | // The use of NOT EXISTS here requires the use of IN instead of NOT IN to prevent a "double negative." |
||
67 | // We are not using EXISTS...NOT IN because it results in including everyone who has at least one entry that doesn't |
||
68 | // match the criteria. For example, with tags, if the contact has the tag in the filter but also another tag, they'll |
||
69 | // be included in the results which is not what we want. |
||
70 | $expression = $subQueryBuilder->expr()->in( |
||
71 | $tableAlias.'.'.$filter->getField(), |
||
72 | $filterParametersHolder |
||
73 | ); |
||
74 | |||
75 | $subQueryBuilder->andWhere($expression); |
||
76 | $queryBuilder->addLogic($queryBuilder->expr()->notExists($subQueryBuilder->getSQL()), $filter->getGlue()); |
||
77 | break; |
||
78 | case 'neq': |
||
79 | $expression = $subQueryBuilder->expr()->orX( |
||
80 | $subQueryBuilder->expr()->eq($tableAlias.'.'.$filter->getField(), $filterParametersHolder), |
||
81 | $subQueryBuilder->expr()->isNull($tableAlias.'.'.$filter->getField()) |
||
82 | ); |
||
83 | |||
84 | $subQueryBuilder->andWhere($expression); |
||
85 | |||
86 | $queryBuilder->addLogic($queryBuilder->expr()->notExists($subQueryBuilder->getSQL()), $filter->getGlue()); |
||
87 | break; |
||
88 | case 'notLike': |
||
89 | $expression = $subQueryBuilder->expr()->orX( |
||
90 | $subQueryBuilder->expr()->isNull($tableAlias.'.'.$filter->getField()), |
||
91 | $subQueryBuilder->expr()->like($tableAlias.'.'.$filter->getField(), $filterParametersHolder) |
||
92 | ); |
||
93 | |||
94 | $subQueryBuilder->andWhere($expression); |
||
95 | |||
96 | $queryBuilder->addLogic($queryBuilder->expr()->notExists($subQueryBuilder->getSQL()), $filter->getGlue()); |
||
97 | break; |
||
98 | case 'regexp': |
||
99 | case 'notRegexp': |
||
100 | $not = ('notRegexp' === $filterOperator) ? ' NOT' : ''; |
||
101 | $expression = $tableAlias.'.'.$filter->getField().$not.' REGEXP '.$filterParametersHolder; |
||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
102 | |||
103 | $subQueryBuilder->andWhere($expression); |
||
104 | |||
105 | $queryBuilder->addLogic($queryBuilder->expr()->exists($subQueryBuilder->getSQL()), $filter->getGlue()); |
||
106 | break; |
||
107 | default: |
||
108 | $expression = $subQueryBuilder->expr()->$filterOperator( |
||
109 | $tableAlias.'.'.$filter->getField(), |
||
110 | $filterParametersHolder |
||
111 | ); |
||
112 | $subQueryBuilder->andWhere($expression); |
||
113 | |||
114 | $queryBuilder->addLogic($queryBuilder->expr()->exists($subQueryBuilder->getSQL()), $filter->getGlue()); |
||
115 | } |
||
116 | |||
117 | $queryBuilder->setParametersPairs($parameters, $filterParameters); |
||
118 | |||
119 | return $queryBuilder; |
||
120 | } |
||
121 | } |
||
122 |