xref: /plugin/tagging/helper/querybuilder.php (revision 1b4b4fa991ead6a21eec9148846f6b5e23d3fecb)
1<?php
2/**
3 * Tagging Plugin (helper component)
4 */
5class helper_plugin_tagging_querybuilder extends DokuWiki_Plugin {
6
7    const QUERY_ORDER = ['pid', 'tagger', 'tags', 'ns', 'notns'];
8
9    /** @var string */
10    protected $field;
11    /** @var bool */
12    protected $logicalAnd = false;
13    /** @var array */
14    protected $tags = [];
15    /** @var array  */
16    protected $ns = [];
17    /** @var array */
18    protected $notns = [];
19    /** @var string */
20    protected $pid;
21    /** @var string */
22    protected $tagger = '';
23    /** @var string */
24    protected $limit = '';
25    /** @var string */
26    protected $where;
27    /** @var string */
28    protected $orderby;
29    /** @var string */
30    protected $groupby;
31    /** @var string */
32    protected $having = '';
33
34    /**
35     * Shorthand method: deduces the appropriate getter from $this->field
36     *
37     * @return string
38     */
39    public function getQuery()
40    {
41        if (!$this->field) {
42            throw new \RuntimeException('Failed to build a query, no field specified');
43        }
44        return ($this->field === 'pid') ? $this->getPages() : $this->getTags();
45    }
46
47    /**
48     * Returns SQL query for fetching tagged pages
49     *
50     * @return string
51     */
52    public function getPages()
53    {
54        $this->where = $this->getWhere();
55        $this->groupby = 'pid';
56        $this->orderby = "cnt DESC, pid";
57        if ($this->tags && $this->logicalAnd) $this->having = ' HAVING cnt = ' . count($this->tags);
58
59        return $this->getSql();
60    }
61
62    /**
63     * Returns SQL query for fetching tags
64     *
65     * @return string
66     */
67    public function getTags()
68    {
69        $this->where = $this->getWhere();
70        $this->groupby = 'CLEANTAG(tag)';
71        $this->orderby = 'CLEANTAG(tag)';
72
73        return $this->getSql();
74    }
75
76    /**
77     * @param array $tags
78     */
79    public function setTags($tags)
80    {
81        $this->tags = $tags;
82    }
83
84    /**
85     * Namespaces to limit search to
86     *
87     * @param array $ns
88     */
89    public function includeNS($ns)
90    {
91        $this->ns = $ns;
92    }
93
94    /**
95     * Namespaces to exclude from search
96     *
97     * @param array $ns
98     */
99    public function excludeNS($ns)
100    {
101        $this->notns = $ns;
102    }
103
104    /**
105     * @param bool $and
106     */
107    public function setLogicalAnd($and)
108    {
109        $this->logicalAnd = $and;
110    }
111
112    /**
113     * @param string $limit
114     */
115    public function setLimit($limit)
116    {
117        $this->limit = $limit ? " LIMIT $limit" : '';
118    }
119
120    /**
121     * @param string $field
122     */
123    public function setField($field)
124    {
125        $this->field = $field;
126    }
127
128    /**
129     * @param string $pid
130     */
131    public function setPid($pid)
132    {
133        $this->pid = $pid;
134    }
135
136    /**
137     * @param string $tagger
138     */
139    public function setTagger($tagger)
140    {
141        $this->tagger = $tagger;
142    }
143
144    /**
145     * Returns query SQL
146     * @return string
147     */
148    protected function getSql()
149    {
150        $sql = "SELECT $this->field AS item, COUNT(*) AS cnt
151                  FROM taggings
152                 WHERE $this->where
153              GROUP BY $this->groupby
154              $this->having
155              ORDER BY $this->orderby
156                $this->limit
157              ";
158
159        return $sql;
160    }
161
162    /**
163     * Builds query string. The order is important
164     * @return string
165     */
166    protected function getWhere()
167    {
168        $where = '1=1';
169
170        if ($this->pid) {
171            $where .= ' AND pid = ?';
172        }
173
174        if ($this->tagger) {
175            $where .= ' AND tagger = ?';
176        }
177
178        if ($this->ns) {
179            $where .= ' AND ';
180
181            $nsCnt = count($this->ns);
182            $i = 0;
183            foreach ($this->ns as $ns) {
184                $where .= ' pid';
185                $where .= ' GLOB';
186                $where .= ' ?';
187                if (++$i < $nsCnt) $where .= ' OR';
188            }
189        }
190
191        if ($this->notns) {
192            $where .= ' AND ';
193
194            $nsCnt = count($this->notns);
195            $i = 0;
196            foreach ($this->notns as $notns) {
197                $where .= ' pid';
198                $where .= ' NOT GLOB';
199                $where .= ' ?';
200                if (++$i < $nsCnt) $where .= ' AND';
201            }
202        }
203
204        if ($this->tags) {
205            $where .= ' AND ';
206
207            $tagCnt = count($this->tags);
208            $i = 0;
209            foreach ($this->tags as $tag) {
210                $where .= ' CLEANTAG(tag)';
211                $where .= $this->useLike($tag) ? ' GLOB' : ' =';
212                $where .= ' CLEANTAG(?)';
213                if (++$i < $tagCnt) $where .= ' OR';
214            }
215        }
216
217        $where .= ' AND GETACCESSLEVEL(pid) >= ' . AUTH_READ;
218
219
220        return $where;
221    }
222
223    /**
224     * Check if the given string is a LIKE statement
225     *
226     * @param string $value
227     * @return bool
228     */
229    protected function useLike($value) {
230        return strpos($value, '*') === 0 || strrpos($value, '*') === strlen($value) - 1;
231    }
232}
233