|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
/** |
|
4
|
|
|
* DB and general functions for working with the message index |
|
5
|
|
|
* |
|
6
|
|
|
* @name ElkArte Forum |
|
7
|
|
|
* @copyright ElkArte Forum contributors |
|
8
|
|
|
* @license BSD http://opensource.org/licenses/BSD-3-Clause |
|
9
|
|
|
* |
|
10
|
|
|
* @version 2.0 dev |
|
11
|
|
|
* |
|
12
|
|
|
*/ |
|
13
|
|
|
|
|
14
|
|
|
/** |
|
15
|
|
|
* Builds the message index with the supplied parameters |
|
16
|
|
|
* creates all you ever wanted on message index, returns the data in array |
|
17
|
|
|
* |
|
18
|
|
|
* @param int $id_board board to build the topic listing for |
|
19
|
|
|
* @param int $id_member who we are building it for so we don't show unapproved topics |
|
20
|
|
|
* @param int $start where to start from |
|
21
|
|
|
* @param int $items_per_page The number of items to show per page |
|
22
|
|
|
* @param string $sort_by how to sort the results asc/desc |
|
23
|
|
|
* @param string $sort_column which value we sort by |
|
24
|
|
|
* @param mixed[] $indexOptions |
|
25
|
|
|
* 'include_sticky' => if on, loads sticky topics as additional |
|
26
|
|
|
* 'only_approved' => if on, only load approved topics |
|
27
|
|
|
* 'previews' => if on, loads in a substring of the first/last message text for use in previews |
|
28
|
|
|
* 'include_avatars' => if on loads the last message posters avatar |
|
29
|
|
|
* 'ascending' => ASC or DESC for the sort |
|
30
|
|
|
* 'fake_ascending' => |
|
31
|
|
|
* 'custom_selects' => loads additional values from the tables used in the query, for addon use |
|
32
|
|
|
*/ |
|
33
|
|
|
function messageIndexTopics($id_board, $id_member, $start, $items_per_page, $sort_by, $sort_column, $indexOptions) |
|
34
|
|
|
{ |
|
35
|
1 |
|
$db = database(); |
|
36
|
|
|
|
|
37
|
1 |
|
$topics = array(); |
|
38
|
1 |
|
$indexOptions = array_merge(array( |
|
39
|
1 |
|
'include_sticky' => true, |
|
40
|
|
|
'fake_ascending' => false, |
|
41
|
|
|
'ascending' => true, |
|
42
|
|
|
'only_approved' => true, |
|
43
|
|
|
'previews' => -1, |
|
44
|
|
|
'include_avatars' => false, |
|
45
|
|
|
'custom_selects' => array(), |
|
46
|
|
|
'custom_joins' => array(), |
|
47
|
1 |
|
), $indexOptions); |
|
48
|
|
|
|
|
49
|
1 |
|
$request = $db->query('', ' |
|
50
|
|
|
SELECT t.id_topic |
|
51
|
1 |
|
FROM {db_prefix}topics AS t' . ($sort_by === 'last_poster' ? ' |
|
52
|
1 |
|
INNER JOIN {db_prefix}messages AS ml ON (ml.id_msg = t.id_last_msg)' : (in_array($sort_by, array('starter', 'subject')) ? ' |
|
53
|
1 |
|
INNER JOIN {db_prefix}messages AS mf ON (mf.id_msg = t.id_first_msg)' : '')) . ($sort_by === 'starter' ? ' |
|
54
|
1 |
|
LEFT JOIN {db_prefix}members AS memf ON (memf.id_member = mf.id_member)' : '') . ($sort_by === 'last_poster' ? ' |
|
55
|
1 |
|
LEFT JOIN {db_prefix}members AS meml ON (meml.id_member = ml.id_member)' : '') . ' |
|
56
|
1 |
|
WHERE t.id_board = {int:current_board}' . (!$indexOptions['only_approved'] ? '' : ' |
|
57
|
1 |
|
AND (t.approved = {int:is_approved}' . ($id_member == 0 ? '' : ' OR t.id_member_started = {int:current_member}') . ')') . ' |
|
58
|
1 |
|
ORDER BY ' . ($indexOptions['include_sticky'] ? 'is_sticky' . ($indexOptions['fake_ascending'] ? '' : ' DESC') . ', ' : '') . $sort_column . ($indexOptions['ascending'] ? '' : ' DESC') . ' |
|
59
|
|
|
LIMIT {int:start}, {int:maxindex}', |
|
60
|
|
|
array( |
|
61
|
1 |
|
'current_board' => $id_board, |
|
62
|
1 |
|
'current_member' => $id_member, |
|
63
|
1 |
|
'is_approved' => 1, |
|
64
|
1 |
|
'id_member_guest' => 0, |
|
65
|
1 |
|
'start' => $start, |
|
66
|
1 |
|
'maxindex' => $items_per_page, |
|
67
|
|
|
) |
|
68
|
|
|
); |
|
69
|
1 |
|
while ($row = $db->fetch_assoc($request)) |
|
70
|
1 |
|
$topics[$row['id_topic']] = []; |
|
71
|
1 |
|
$db->free_result($request); |
|
72
|
|
|
|
|
73
|
|
|
// -1 means preview the whole body |
|
74
|
1 |
|
if ($indexOptions['previews'] === -1) |
|
75
|
|
|
$indexOptions['custom_selects'] = array_merge($indexOptions['custom_selects'], array('ml.body AS last_body', 'mf.body AS first_body')); |
|
76
|
|
|
// Default: a SUBSTRING |
|
77
|
1 |
|
elseif (!empty($indexOptions['previews'])) |
|
78
|
|
|
$indexOptions['custom_selects'] = array_merge($indexOptions['custom_selects'], array('SUBSTRING(ml.body, 1, ' . ($indexOptions['previews'] + 256) . ') AS last_body', 'SUBSTRING(mf.body, 1, ' . ($indexOptions['previews'] + 256) . ') AS first_body')); |
|
79
|
|
|
|
|
80
|
1 |
|
if (!empty($indexOptions['include_avatars'])) |
|
81
|
|
|
{ |
|
82
|
1 |
View Code Duplication |
if ($indexOptions['include_avatars'] === 1 || $indexOptions['include_avatars'] === 3) |
|
83
|
|
|
{ |
|
84
|
1 |
|
$indexOptions['custom_selects'] = array_merge($indexOptions['custom_selects'], array('meml.avatar', 'COALESCE(a.id_attach, 0) AS id_attach', 'a.filename', 'a.attachment_type', 'meml.email_address')); |
|
85
|
1 |
|
$indexOptions['custom_joins'] = array_merge($indexOptions['custom_joins'], array('LEFT JOIN {db_prefix}attachments AS a ON (a.id_member = ml.id_member AND a.id_member != 0)')); |
|
86
|
|
|
} |
|
87
|
|
|
|
|
88
|
1 |
View Code Duplication |
if ($indexOptions['include_avatars'] === 2 || $indexOptions['include_avatars'] === 3) |
|
89
|
|
|
{ |
|
90
|
|
|
$indexOptions['custom_selects'] = array_merge($indexOptions['custom_selects'], array('memf.avatar AS avatar_first', 'COALESCE(af.id_attach, 0) AS id_attach_first', 'af.filename AS filename_first', 'af.attachment_type AS attachment_type_first', 'memf.email_address AS email_address_first')); |
|
91
|
|
|
$indexOptions['custom_joins'] = array_merge($indexOptions['custom_joins'], array('LEFT JOIN {db_prefix}attachments AS af ON (af.id_member = mf.id_member AND af.id_member != 0)')); |
|
92
|
|
|
} |
|
93
|
|
|
|
|
94
|
1 |
|
$request = $db->query('substring', ' |
|
95
|
|
|
SELECT |
|
96
|
|
|
t.id_topic, t.num_replies, t.locked, t.num_views, t.num_likes, t.is_sticky, t.id_poll, t.id_previous_board, |
|
97
|
1 |
|
' . ($id_member == 0 ? '0' : 'COALESCE(lt.id_msg, lmr.id_msg, -1) + 1') . ' AS new_from, |
|
98
|
|
|
t.id_last_msg, t.approved, t.unapproved_posts, t.id_redirect_topic, t.id_first_msg, |
|
99
|
|
|
ml.poster_time AS last_poster_time, ml.id_msg_modified, ml.subject AS last_subject, ml.icon AS last_icon, |
|
100
|
|
|
ml.poster_name AS last_member_name, ml.id_member AS last_id_member, ml.smileys_enabled AS last_smileys, |
|
101
|
|
|
COALESCE(meml.real_name, ml.poster_name) AS last_display_name, |
|
102
|
|
|
mf.poster_time AS first_poster_time, mf.subject AS first_subject, mf.icon AS first_icon, |
|
103
|
|
|
mf.poster_name AS first_member_name, mf.id_member AS first_id_member, mf.smileys_enabled AS first_smileys, |
|
104
|
|
|
COALESCE(memf.real_name, mf.poster_name) AS first_display_name |
|
105
|
1 |
|
' . (!empty($indexOptions['custom_selects']) ? ' ,' . implode(',', $indexOptions['custom_selects']) : '') . ' |
|
106
|
|
|
FROM {db_prefix}topics AS t |
|
107
|
|
|
INNER JOIN {db_prefix}messages AS ml ON (ml.id_msg = t.id_last_msg) |
|
108
|
|
|
INNER JOIN {db_prefix}messages AS mf ON (mf.id_msg = t.id_first_msg) |
|
109
|
|
|
LEFT JOIN {db_prefix}members AS meml ON (meml.id_member = ml.id_member) |
|
110
|
1 |
|
LEFT JOIN {db_prefix}members AS memf ON (memf.id_member = mf.id_member)' . ($id_member == 0 ? '' : ' |
|
111
|
|
|
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:current_member}) |
|
112
|
|
|
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = {int:current_board} AND lmr.id_member = {int:current_member})') . |
|
113
|
1 |
|
(!empty($indexOptions['custom_joins']) ? implode("\n\t\t\t\t", $indexOptions['custom_joins']) : '') . ' |
|
114
|
|
|
WHERE t.id_topic IN ({array_int:topic_list})', |
|
115
|
|
|
array( |
|
116
|
1 |
|
'current_board' => $id_board, |
|
117
|
1 |
|
'current_member' => $id_member, |
|
118
|
1 |
|
'topic_list' => array_keys($topics), |
|
119
|
|
|
) |
|
120
|
|
|
); |
|
121
|
|
|
|
|
122
|
|
|
// Lets take the results |
|
123
|
1 |
|
while ($row = $db->fetch_assoc($request)) |
|
124
|
1 |
|
$topics[$row['id_topic']] = $row; |
|
125
|
|
|
|
|
126
|
1 |
|
$db->free_result($request); |
|
127
|
|
|
} |
|
128
|
|
|
|
|
129
|
1 |
|
return $topics; |
|
130
|
|
|
} |
|
131
|
|
|
|
|
132
|
|
|
/** |
|
133
|
|
|
* This simple function returns the sort methods for message index in an array. |
|
134
|
|
|
*/ |
|
135
|
|
|
function messageIndexSort() |
|
136
|
|
|
{ |
|
137
|
|
|
// Default sort methods for message index. |
|
138
|
|
|
$sort_methods = array( |
|
139
|
1 |
|
'subject' => 'mf.subject', |
|
140
|
|
|
'starter' => 'COALESCE(memf.real_name, mf.poster_name)', |
|
141
|
|
|
'last_poster' => 'COALESCE(meml.real_name, ml.poster_name)', |
|
142
|
|
|
'replies' => 't.num_replies', |
|
143
|
|
|
'views' => 't.num_views', |
|
144
|
|
|
'likes' => 't.num_likes', |
|
145
|
|
|
'first_post' => 't.id_topic', |
|
146
|
|
|
'last_post' => 't.id_last_msg' |
|
147
|
|
|
); |
|
148
|
|
|
|
|
149
|
1 |
|
call_integration_hook('integrate_messageindex_sort', array(&$sort_methods)); |
|
150
|
|
|
|
|
151
|
1 |
|
return $sort_methods; |
|
152
|
|
|
} |
|
153
|
|
|
|
|
154
|
|
|
/** |
|
155
|
|
|
* This function determines if a user has posted in the list of topics, |
|
156
|
|
|
* and returns the list of those topics they posted in. |
|
157
|
|
|
* |
|
158
|
|
|
* @param int $id_member member to check |
|
159
|
|
|
* @param int[] $topic_ids array of topics ids to check for participation |
|
160
|
|
|
*/ |
|
161
|
|
|
function topicsParticipation($id_member, $topic_ids) |
|
162
|
|
|
{ |
|
163
|
1 |
|
$db = database(); |
|
164
|
1 |
|
$topics = array(); |
|
165
|
|
|
|
|
166
|
1 |
|
$result = $db->query('', ' |
|
167
|
|
|
SELECT id_topic |
|
168
|
|
|
FROM {db_prefix}messages |
|
169
|
|
|
WHERE id_topic IN ({array_int:topic_list}) |
|
170
|
|
|
AND id_member = {int:current_member} |
|
171
|
1 |
|
LIMIT ' . count($topic_ids), |
|
172
|
|
|
array( |
|
173
|
1 |
|
'current_member' => $id_member, |
|
174
|
1 |
|
'topic_list' => $topic_ids, |
|
175
|
|
|
) |
|
176
|
|
|
); |
|
177
|
1 |
|
while ($row = $db->fetch_assoc($result)) |
|
178
|
|
|
$topics[] = $row; |
|
179
|
|
|
|
|
180
|
1 |
|
$db->free_result($result); |
|
181
|
|
|
|
|
182
|
|
|
return $topics; |
|
183
|
|
|
} |
elkarte /
Elkarte
