blob: a1e496168045f9c9ec8055c21c62eb752cc6366b (
plain)
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
|
<?php
/**
* @license GPL2 or any later version
*
* @property string $id alias column for tag_hash
* @property string $tag_hash database column
* @property string $name database column
*/
class OERTag extends SimpleORMap
{
protected static function configure($config = [])
{
$config['db_table'] = 'oer_tags';
parent::configure($config);
}
public static function findBest($number = 9, $raw = false)
{
$statement = DBManager::get()->prepare("
SELECT oer_tags.*
FROM (
SELECT tags.tag_hash, COUNT(*) AS position
FROM oer_tags_material AS tags
INNER JOIN oer_material ON (tags.material_id = oer_material.material_id)
LEFT JOIN oer_hosts ON (oer_hosts.host_id = oer_material.host_id)
WHERE oer_material.draft = '0'
AND (oer_material.host_id IS NULL OR oer_hosts.`active` = '1')
GROUP BY tags.tag_hash
) AS best_tags
INNER JOIN oer_tags ON (best_tags.tag_hash = oer_tags.tag_hash)
WHERE position > 0
ORDER BY position DESC
LIMIT ".(int) $number."
");
$statement->execute();
$best = $statement->fetchAll(PDO::FETCH_ASSOC);
if ($raw) {
return $best;
} else {
$tags = [];
foreach ($best as $tag_data) {
$tags[] = self::buildExisting($tag_data);
}
return $tags;
}
}
public static function findRelated($tag_hash, $but_not = [], $limit = 6, $raw = false)
{
$statement = DBManager::get()->prepare("
SELECT oer_tags.*
FROM (
SELECT tags1.tag_hash, COUNT(*) AS position
FROM oer_tags_material AS tags1
INNER JOIN oer_tags_material AS tags2 ON (tags1.material_id = tags2.material_id AND tags1.tag_hash != tags2.tag_hash)
INNER JOIN oer_material ON (tags1.material_id = oer_material.material_id)
LEFT JOIN oer_hosts ON (oer_hosts.host_id = oer_material.host_id)
WHERE tags2.tag_hash NOT IN (:excluded_tags)
AND tags2.tag_hash = :tag_hash
AND tags1.tag_hash NOT IN (:excluded_tags)
AND oer_material.draft = '0'
AND (oer_material.host_id IS NULL OR oer_hosts.`active` = '1')
GROUP BY tags1.tag_hash
) AS best_tags
INNER JOIN oer_tags ON (best_tags.tag_hash = oer_tags.tag_hash)
WHERE position > 0
ORDER BY position DESC
LIMIT ".(int) $limit."
");
$statement->execute([
'tag_hash' => $tag_hash,
'excluded_tags' => $but_not
]);
$best = $statement->fetchAll(PDO::FETCH_ASSOC);
if ($raw) {
return $best;
} else {
$tags = [];
foreach ($best as $tag_data) {
$tags[] = self::buildExisting($tag_data);
}
return $tags;
}
}
}
|
