1<?php
2
3namespace dokuwiki\plugin\struct\meta;
4
5/**
6 * Creates the SQL query using the QueryBuilder
7 *
8 * @internal This class is used by the Search class and should probably never be used directly
9 */
10class SearchSQLBuilder
11{
12    /** @var QueryBuilder */
13    protected $qb;
14
15    /** @var bool Include latest = 1 in select query */
16    protected $selectLatest = true;
17
18    /**
19     * SearchSQLBuilder constructor.
20     */
21    public function __construct()
22    {
23        $this->qb = new QueryBuilder();
24    }
25
26    /**
27     * Add the schemas to the query
28     *
29     * @param Schema[] $schemas Schema names to query
30     */
31    public function addSchemas($schemas)
32    {
33        // basic tables
34        $first_table = '';
35        foreach ($schemas as $schema) {
36            $datatable = 'data_' . $schema->getTable();
37            if ($first_table) {
38                // follow up tables
39                $this->qb->addLeftJoin($first_table, $datatable, $datatable, "$first_table.pid = $datatable.pid");
40            } else {
41                // first table
42                $this->qb->addTable($datatable);
43
44                // add conditional page clauses if pid has a value
45                $subAnd = $this->qb->filters()->whereSubAnd();
46                $subAnd->whereAnd("$datatable.pid = ''");
47                $subOr = $subAnd->whereSubOr();
48                $subOr->whereAnd("GETACCESSLEVEL($datatable.pid) > 0");
49                $subOr->whereAnd("PAGEEXISTS($datatable.pid) = 1");
50                // make sure to check assignment for page data only
51                $subOr->whereAnd("($datatable.rid != 0 OR (ASSIGNED = 1 OR ASSIGNED IS NULL))");
52
53                // add conditional schema assignment check
54                $this->qb->addLeftJoin(
55                    $datatable,
56                    'schema_assignments',
57                    '',
58                    "$datatable.pid != ''
59                    AND $datatable.pid = schema_assignments.pid
60                    AND schema_assignments.tbl = '{$schema->getTable()}'"
61                );
62
63                $this->qb->addSelectColumn($datatable, 'rid');
64                $this->qb->addSelectColumn($datatable, 'pid', 'PID');
65                $this->qb->addSelectColumn($datatable, 'rev');
66                $this->qb->addSelectColumn('schema_assignments', 'assigned', 'ASSIGNED');
67                $this->qb->addGroupByColumn($datatable, 'pid');
68                $this->qb->addGroupByColumn($datatable, 'rid');
69
70                $first_table = $datatable;
71            }
72            $this->qb->filters()->whereAnd($this->addPublishClauses($datatable));
73        }
74    }
75
76    /**
77     * Add the columns to select, handling multis
78     *
79     * @param Column[] $columns
80     */
81    public function addColumns($columns)
82    {
83        $sep = Search::CONCAT_SEPARATOR;
84        $n = 0;
85        foreach ($columns as $col) {
86            $CN = 'C' . $n++;
87
88            if ($col->isMulti()) {
89                $datatable = "data_{$col->getTable()}";
90                $multitable = "multi_{$col->getTable()}";
91                $MN = $this->qb->generateTableAlias('M');
92
93                $this->qb->addLeftJoin(
94                    $datatable,
95                    $multitable,
96                    $MN,
97                    "$datatable.pid = $MN.pid AND $datatable.rid = $MN.rid AND
98                     $datatable.rev = $MN.rev AND
99                     $MN.colref = {$col->getColref()}"
100                );
101
102                $col->getType()->select($this->qb, $MN, 'value', $CN);
103                $sel = $this->qb->getSelectStatement($CN);
104                $this->qb->addSelectStatement("GROUP_CONCAT_DISTINCT($sel, '$sep')", $CN);
105            } else {
106                $col->getType()->select($this->qb, 'data_' . $col->getTable(), $col->getColName(), $CN);
107                $this->qb->addGroupByStatement($CN);
108            }
109        }
110    }
111
112    /**
113     * Add the filters to the query
114     *
115     * All given filters are added to their own subclause.
116     *
117     * @param array[] $filters
118     */
119    public function addFilters($filters)
120    {
121        if (!$filters) return; // ignore empty filters
122
123        $subClause = $this->qb->filters()->where('AND');
124
125        foreach ($filters as $filter) {
126            /** @var Column $col */
127            [$col, $value, $comp, $op] = $filter;
128
129            $datatable = "data_{$col->getTable()}";
130            $multitable = "multi_{$col->getTable()}";
131
132            /** @var $col Column */
133            if ($col->isMulti()) {
134                $MN = $this->qb->generateTableAlias('MN');
135
136                $this->qb->addLeftJoin(
137                    $datatable,
138                    $multitable,
139                    $MN,
140                    "$datatable.pid = $MN.pid AND $datatable.rid = $MN.rid AND
141                     $datatable.rev = $MN.rev AND
142                     $MN.colref = {$col->getColref()}"
143                );
144                $coltbl = $MN;
145                $colnam = 'value';
146            } else {
147                $coltbl = $datatable;
148                $colnam = $col->getColName();
149            }
150
151            $col->getType()->filter($subClause, $coltbl, $colnam, $comp, $value, $op); // type based filter
152        }
153    }
154
155    /**
156     * Add the sort by clauses to the query
157     *
158     * We always sort by the single val column which contains a copy of the first value of the multi column
159     *
160     * @param array[] $sorts
161     */
162    public function addSorts($sorts)
163    {
164        foreach ($sorts as $sort) {
165            [$col, $asc, $nc] = $sort;
166            /** @var $col Column */
167            $colname = $col->getColName(false);
168            if ($nc) $colname .= ' COLLATE NOCASE';
169            $col->getType()->sort($this->qb, 'data_' . $col->getTable(), $colname, $asc ? 'ASC' : 'DESC');
170        }
171    }
172
173    /**
174     * @param string $datatable
175     * @return string
176     */
177    public function addPublishClauses($datatable)
178    {
179        $latestClause = "IS_PUBLISHER($datatable.pid)";
180        if ($this->selectLatest) {
181            $latestClause .= " AND $datatable.latest = 1";
182        }
183        $publishedClause = "IS_PUBLISHER($datatable.pid) !=1 AND $datatable.published = 1";
184
185        return "( ($latestClause) OR ($publishedClause) )";
186    }
187
188    /**
189     * Access to the underlying QueryBuilder
190     *
191     * @return QueryBuilder
192     */
193    public function getQueryBuilder()
194    {
195        return $this->qb;
196    }
197
198    /**
199     * Get the SQL query and parameters
200     *
201     * Shortcut for $this->getQueryBuilder()->getSQL()
202     *
203     * @return array ($sql, $params)
204     */
205    public function getSQL()
206    {
207        return $this->qb->getSQL();
208    }
209
210    /**
211     * Allows disabling default 'latest = 1' clause in select statement
212     *
213     * @param bool $selectLatest
214     */
215    public function setSelectLatest(bool $selectLatest)
216    {
217        $this->selectLatest = $selectLatest;
218    }
219}
220