Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
93.89%
169 / 180
66.67%
8 / 12
 CRAP
0.00%
0 / 1
Relation
93.89%
169 / 180
66.67%
8 / 12
 47.50
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 setGroups
100.00%
2 / 2
100.00%
1 / 1
 1
 getCategoryFaqsMatrix
100.00%
15 / 15
100.00%
1 / 1
 4
 getCategoryWithFaqs
100.00%
45 / 45
100.00%
1 / 1
 8
 getNumberOfFaqsPerCategory
100.00%
34 / 34
100.00%
1 / 1
 9
 getAggregatedFaqNumbers
81.25%
13 / 16
0.00%
0 / 1
 6.24
 aggregateRecursively
66.67%
6 / 9
0.00%
0 / 1
 4.59
 getCategories
73.33%
11 / 15
0.00%
0 / 1
 4.30
 add
95.83%
23 / 24
0.00%
0 / 1
 3
 delete
100.00%
9 / 9
100.00%
1 / 1
 3
 normalizeIdList
100.00%
2 / 2
100.00%
1 / 1
 2
 deleteByFAQ
100.00%
8 / 8
100.00%
1 / 1
 2
1<?php
2
3/**
4 * Category relations class.
5 *
6 * This Source Code Form is subject to the terms of the Mozilla Public License,
7 * v. 2.0. If a copy of the MPL was not distributed with this file, You can
8 * obtain one at https://mozilla.org/MPL/2.0/.
9 *
10 * @package   phpMyFAQ
11 * @author    Thorsten Rinne <thorsten@phpmyfaq.de>
12 * @copyright 2019-2026 phpMyFAQ Team
13 * @license   https://www.mozilla.org/MPL/2.0/ Mozilla Public License Version 2.0
14 * @link      https://www.phpmyfaq.de
15 * @since     2019-11-22
16 */
17
18declare(strict_types=1);
19
20namespace phpMyFAQ\Category;
21
22use phpMyFAQ\Category;
23use phpMyFAQ\Configuration;
24use phpMyFAQ\Database;
25
26/**
27 * Class CategoryRelation
28 *
29 * @package phpMyFAQ\Category
30 */
31class Relation
32{
33    /** @var int[] */
34    private array $groups;
35
36    /**
37     * CategoryRelation constructor.
38     */
39    public function __construct(
40        private readonly Configuration $configuration,
41        private readonly Category $category,
42    ) {
43    }
44
45    /**
46     * @param int[] $groups
47     */
48    public function setGroups(array $groups): Relation
49    {
50        $this->groups = $groups;
51        return $this;
52    }
53
54    /**
55     * Create a matrix for representing categories and FAQs.
56     */
57    public function getCategoryFaqsMatrix(): array
58    {
59        $matrix = [];
60
61        $query = sprintf(
62            '
63            SELECT
64                fcr.category_id AS id_cat,
65                fd.id AS id
66            FROM
67                %sfaqdata fd
68            INNER JOIN
69                %sfaqcategoryrelations fcr
70            ON
71                fd.id = fcr.record_id
72            AND
73                fd.lang = fcr.category_lang
74            ORDER BY
75                fcr.category_id, fd.id',
76            Database::getTablePrefix(),
77            Database::getTablePrefix(),
78        );
79        $result = $this->configuration->getDb()->query($query);
80
81        if ($this->configuration->getDb()->numRows($result) > 0) {
82            while (true) {
83                $row = $this->configuration->getDb()->fetchObject($result);
84                if (!$row instanceof \stdClass) {
85                    break;
86                }
87
88                $matrix[(int) $row->id_cat][(int) $row->id] = true;
89            }
90        }
91
92        return $matrix;
93    }
94
95    /**
96     * @return array<int, array<string, mixed>>
97     */
98    public function getCategoryWithFaqs(): array
99    {
100        $categoryTree = [];
101        $groupList = $this->normalizeIdList($this->category->getGroups());
102
103        $query = sprintf(
104            '
105            SELECT
106                fcr.category_id AS id,
107                fc.parent_id AS parent_id,
108                fc.name AS category_name,
109                fc.description AS description,
110                count(DISTINCT fcr.record_id) AS number
111            FROM
112                %sfaqcategoryrelations fcr
113                JOIN %sfaqdata fd ON fcr.record_id = fd.id AND fcr.record_lang = fd.lang
114                LEFT JOIN %sfaqdata_group AS fdg ON fd.id = fdg.record_id
115                LEFT JOIN %sfaqdata_user AS fdu ON fd.id = fdu.record_id
116                LEFT JOIN %sfaqcategory_group AS fcg ON fcr.category_id = fcg.category_id
117                LEFT JOIN %sfaqcategory_user AS fcu ON fcr.category_id = fcu.category_id
118                LEFT JOIN %sfaqcategories AS fc ON fcr.category_id = fc.id AND fcr.category_lang = fc.lang
119            WHERE 1=1 
120            ',
121            Database::getTablePrefix(),
122            Database::getTablePrefix(),
123            Database::getTablePrefix(),
124            Database::getTablePrefix(),
125            Database::getTablePrefix(),
126            Database::getTablePrefix(),
127            Database::getTablePrefix(),
128        );
129
130        if ($this->configuration->get(item: 'security.permLevel') !== 'basic') {
131            if (-1 === $this->category->getUser()) {
132                $query .= sprintf('AND fdg.group_id IN (%s) AND fcg.group_id IN (%s)', $groupList, $groupList);
133            }
134            if (-1 !== $this->category->getUser()) {
135                $query .= sprintf(
136                    'AND ( fdu.user_id = %d OR fdg.group_id IN (%s) )
137                    AND ( fcu.user_id = %d OR fcg.group_id IN (%s) )',
138                    $this->category->getUser(),
139                    $groupList,
140                    $this->category->getUser(),
141                    $groupList,
142                );
143            }
144        }
145
146        if ($this->configuration->getLanguage()->getLanguage() !== '') {
147            $query .= sprintf(
148                " AND fd.lang = '%s'",
149                $this->configuration->getDb()->escape($this->configuration->getLanguage()->getLanguage()),
150            );
151        }
152
153        $query .= " AND fd.active = 'yes' GROUP BY fcr.category_id, fc.parent_id, fc.name, fc.description";
154
155        $result = $this->configuration->getDb()->query($query);
156        if ($this->configuration->getDb()->numRows($result) > 0) {
157            while (true) {
158                $category = $this->configuration->getDb()->fetchObject($result);
159                if (!$category instanceof \stdClass) {
160                    break;
161                }
162
163                $categoryTree[(int) $category->id] = [
164                    'category_id' => (int) $category->id,
165                    'parent_id' => (int) $category->parent_id,
166                    'name' => (string) $category->category_name,
167                    'description' => (string) $category->description,
168                    'faqs' => (int) $category->number,
169                ];
170            }
171        }
172
173        return $categoryTree;
174    }
175
176    /**
177     * Returns the number of records in each category.
178     * @return int[]
179     */
180    public function getNumberOfFaqsPerCategory(bool $categoryRestriction = false, bool $onlyActive = false): array
181    {
182        $numRecordsByCat = [];
183        $language = $this->configuration->getDb()->escape($this->configuration->getLanguage()->getLanguage());
184        $query = '';
185        if ($categoryRestriction) {
186            $query = sprintf(
187                '
188                SELECT
189                    fcr.category_id AS category_id,
190                    fc.parent_id as parent_id,
191                    COUNT(fcr.record_id) AS number
192                FROM
193                    %sfaqcategoryrelations fcr
194                LEFT JOIN
195                    %sfaqdata fd on fcr.record_id = fd.id
196                LEFT JOIN
197                    %sfaqdata_group fdg on fdg.record_id = fcr.record_id
198                LEFT JOIN 
199                    %sfaqcategories fc ON fc.id = fcr.category_id AND fcr.category_lang = fc.lang
200                WHERE
201                    fdg.group_id = %s
202                AND
203                    fcr.record_lang = fd.lang
204                %s',
205                Database::getTablePrefix(),
206                Database::getTablePrefix(),
207                Database::getTablePrefix(),
208                Database::getTablePrefix(),
209                (int) ($this->groups[0] ?? -1),
210                $onlyActive ? " AND fd.active = 'yes'" : '',
211            );
212        }
213
214        if (!$categoryRestriction) {
215            $query = sprintf(
216                '
217                SELECT
218                    fcr.category_id AS category_id,
219                    fc.parent_id as parent_id,
220                    COUNT(fcr.record_id) AS number
221                FROM
222                    %sfaqcategoryrelations fcr
223                LEFT JOIN
224                    %sfaqdata fd on fcr.record_id = fd.id
225                LEFT JOIN 
226                    %sfaqcategories fc ON fc.id = fcr.category_id
227                WHERE
228                    fcr.record_id = fd.id
229                AND
230                    fcr.record_lang = fd.lang
231                %s',
232                Database::getTablePrefix(),
233                Database::getTablePrefix(),
234                Database::getTablePrefix(),
235                $onlyActive ? " AND fd.active = 'yes'" : '',
236            );
237        }
238
239        if ($this->configuration->getLanguage()->getLanguage() !== '') {
240            $query .= sprintf(" AND fd.lang = '%s' AND fc.lang = '%s'", $language, $language);
241        }
242
243        $query .= ' GROUP BY fcr.category_id, fc.parent_id';
244
245        $result = $this->configuration->getDb()->query($query);
246        if ($this->configuration->getDb()->numRows($result) > 0) {
247            while (true) {
248                $row = $this->configuration->getDb()->fetchObject($result);
249                if (!$row instanceof \stdClass) {
250                    break;
251                }
252
253                $numRecordsByCat[(int) $row->category_id] = (int) $row->number;
254            }
255        }
256
257        return $numRecordsByCat;
258    }
259
260    /**
261     * Calculates the aggregated numbers of FAQs
262     *
263     * @param array<int, array<string, mixed>> $categories
264     * @return array<int, int>
265     */
266    public function getAggregatedFaqNumbers(array $categories): array
267    {
268        /** @var array<int, int> $aggregatedFaqs */
269        $aggregatedFaqs = [];
270        /** @var array<int, list<int>> $childrenMap */
271        $childrenMap = [];
272
273        // Build children map and initialize aggregated counts
274        foreach ($categories as $category) {
275            $categoryId = (int) ($category['category_id'] ?? 0);
276            $parentId = (int) ($category['parent_id'] ?? 0);
277
278            $aggregatedFaqs[$categoryId] = (int) ($category['faqs'] ?? 0);
279
280            if ($parentId !== 0) {
281                if (!array_key_exists($parentId, $childrenMap)) {
282                    $childrenMap[$parentId] = [];
283                }
284
285                $childrenMap[$parentId][] = $categoryId;
286            }
287        }
288
289        // Recursively aggregate FAQs from children to parents
290        /** @var array<int, true> $processedCategories */
291        $processedCategories = [];
292
293        foreach ($categories as $category) {
294            $categoryId = (int) ($category['category_id'] ?? 0);
295
296            if (!array_key_exists($categoryId, $processedCategories)) {
297                $this->aggregateRecursively($categoryId, $childrenMap, $aggregatedFaqs, $processedCategories);
298            }
299        }
300
301        return $aggregatedFaqs;
302    }
303
304    /**
305     * Helper method for recursive aggregation
306     *
307     * @param array<int, list<int>> $childrenMap
308     * @param array<int, int> $aggregatedFaqs
309     * @param array<int, true> $processedCategories
310     */
311    private function aggregateRecursively(
312        int $categoryId,
313        array $childrenMap,
314        array &$aggregatedFaqs,
315        array &$processedCategories,
316    ): int {
317        if (array_key_exists($categoryId, $processedCategories)) {
318            return $aggregatedFaqs[$categoryId];
319        }
320
321        $total = $aggregatedFaqs[$categoryId] ?? 0;
322
323        if (array_key_exists($categoryId, $childrenMap)) {
324            foreach ($childrenMap[$categoryId] as $childId) {
325                $total += $this->aggregateRecursively($childId, $childrenMap, $aggregatedFaqs, $processedCategories);
326            }
327        }
328
329        $aggregatedFaqs[$categoryId] = $total;
330        $processedCategories[$categoryId] = true;
331
332        return $total;
333    }
334
335    /**
336     * Returns the categories from a FAQ id and language.
337     *
338     * @param int    $faqId FAQ id
339     * @param string $faqLang FAQ language
340     */
341    public function getCategories(int $faqId, string $faqLang): array
342    {
343        $categories = [];
344        $faqLang = $this->configuration->getDb()->escape($faqLang);
345
346        $query = sprintf("
347            SELECT
348                category_id, category_lang
349            FROM
350                %sfaqcategoryrelations
351            WHERE
352                record_id = %d
353            AND
354                record_lang = '%s'", Database::getTablePrefix(), $faqId, $faqLang);
355
356        $result = $this->configuration->getDb()->query($query);
357        if ($result) {
358            while (true) {
359                $row = $this->configuration->getDb()->fetchObject($result);
360                if (!$row instanceof \stdClass) {
361                    break;
362                }
363
364                $categories[(int) $row->category_id] = [
365                    'category_id' => (int) $row->category_id,
366                    'category_lang' => (string) $row->category_lang,
367                ];
368            }
369        }
370
371        return $categories;
372    }
373
374    /**
375     * Adds new category relations to a FAQ
376     *
377     * @param array  $categories Array of categories
378     * @param int    $faqId FAQ id
379     * @param string $language Language
380     */
381    public function add(array $categories, int $faqId, string $language): bool
382    {
383        $databaseDriver = $this->configuration->getDb();
384        $escapedLang = $databaseDriver->escape($language);
385        $prefix = Database::getTablePrefix();
386
387        foreach ($categories as $category) {
388            // Skip insert if the relation already exists to avoid UNIQUE constraint violation (SQLite warning)
389            $existsQuery = sprintf(
390                "SELECT 1 FROM %sfaqcategoryrelations WHERE category_id = %d AND category_lang = '%s' AND record_id = %d AND record_lang = '%s' LIMIT 1",
391                $prefix,
392                (int) $category,
393                $escapedLang,
394                $faqId,
395                $escapedLang,
396            );
397            $existsResult = $databaseDriver->query($existsQuery);
398            if ($databaseDriver->numRows($existsResult) > 0) {
399                continue; // already present
400            }
401
402            $databaseDriver->query(sprintf(
403                "INSERT INTO %sfaqcategoryrelations VALUES (%d, '%s', %d, '%s')",
404                $prefix,
405                (int) $category,
406                $escapedLang,
407                $faqId,
408                $escapedLang,
409            ));
410        }
411
412        return true;
413    }
414
415    /**
416     * Deletes a category relation for a given category
417     *
418     * @param int    $categoryId Category id
419     * @param string $categoryLang Category language
420     * @param bool   $deleteForAllLanguages Delete all languages?
421     */
422    public function delete(int $categoryId, string $categoryLang, bool $deleteForAllLanguages = false): bool
423    {
424        $query = sprintf(
425            'DELETE FROM %sfaqcategoryrelations WHERE category_id = %d',
426            Database::getTablePrefix(),
427            $categoryId,
428        );
429
430        if (!$deleteForAllLanguages) {
431            $query .= sprintf(" AND category_lang = '%s'", $this->configuration->getDb()->escape($categoryLang));
432        }
433
434        $result = $this->configuration->getDb()->query($query);
435
436        return $result !== false && $result !== null;
437    }
438
439    /**
440     * @param array<array-key, mixed> $ids
441     */
442    private function normalizeIdList(array $ids): string
443    {
444        $normalizedIds = array_map(static fn(mixed $id): int => (int) $id, $ids);
445
446        return $normalizedIds === [] ? '-1' : implode(', ', $normalizedIds);
447    }
448
449    /**
450     * Deletes category relations to a record.
451     *
452     * @param int    $faqId   Record id
453     * @param string $faqLanguage Language
454     */
455    public function deleteByFAQ(int $faqId, string $faqLanguage): bool
456    {
457        $query = sprintf(
458            "DELETE FROM %sfaqcategoryrelations WHERE record_id = %d AND record_lang = '%s'",
459            Database::getTablePrefix(),
460            $faqId,
461            $this->configuration->getDb()->escape($faqLanguage),
462        );
463
464        $result = $this->configuration->getDb()->query($query);
465
466        return $result !== false && $result !== null;
467    }
468}