Skip to main content
Topic: MessageIndex / Attachments sql Query (Read 672 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

MessageIndex / Attachments sql Query

MessageIndex.sub.php function messageIndexTopics
Code: [Select]
		$request = $db->query('substring', '
SELECT
t.id_topic, t.num_replies, t.locked, t.num_views, t.num_likes, t.is_sticky, t.id_poll, t.id_previous_board,
' . ($id_member == 0 ? '0' : 'IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1') . ' AS new_from,
t.id_last_msg, t.approved, t.unapproved_posts, t.id_redirect_topic, t.id_first_msg,
ml.poster_time AS last_poster_time, ml.id_msg_modified, ml.subject AS last_subject, ml.icon AS last_icon,
ml.poster_name AS last_member_name, ml.id_member AS last_id_member, ml.smileys_enabled AS last_smileys,
IFNULL(meml.real_name, ml.poster_name) AS last_display_name,
mf.poster_time AS first_poster_time, mf.subject AS first_subject, mf.icon AS first_icon,
mf.poster_name AS first_member_name, mf.id_member AS first_id_member, mf.smileys_enabled AS first_smileys,
IFNULL(memf.real_name, mf.poster_name) AS first_display_name
' . $preview_bodies . '
' . (!empty($indexOptions['include_avatars']) ? ' ,meml.avatar ,IFNULL(a.id_attach, 0) AS id_attach, a.filename, a.attachment_type, meml.email_address' : '') .
(!empty($indexOptions['custom_selects']) ? ' ,' . implode(',', $indexOptions['custom_selects']) : '') . '
FROM {db_prefix}topics AS t
INNER JOIN {db_prefix}messages AS ml ON (ml.id_msg = t.id_last_msg)
INNER JOIN {db_prefix}messages AS mf ON (mf.id_msg = t.id_first_msg)
LEFT JOIN {db_prefix}members AS meml ON (meml.id_member = ml.id_member)
LEFT JOIN {db_prefix}members AS memf ON (memf.id_member = mf.id_member)' . ($id_member == 0 ? '' : '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:current_member})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = {int:current_board} AND lmr.id_member = {int:current_member})') . (!empty($indexOptions['include_avatars']) ? '
LEFT JOIN {db_prefix}attachments AS a ON (a.id_member = ml.id_member AND a.id_member != 0)' : '') . '
WHERE ' . ($ids_query ? 't.id_topic IN ({array_int:topic_list})' : 't.id_board = {int:current_board}') . (!$indexOptions['only_approved'] ? '' : '
AND (t.approved = {int:is_approved}' . ($id_member == 0 ? '' : ' OR t.id_member_started = {int:current_member}') . ')') . '
ORDER BY ' . ($ids_query ? 'FIND_IN_SET(t.id_topic, {string:find_set_topics})' : ($indexOptions['include_sticky'] ? 'is_sticky' . ($indexOptions['fake_ascending'] ? '' : ' DESC') . ', ' : '') . $sort_column . ($indexOptions['ascending'] ? '' : ' DESC')) . '
LIMIT ' . ($ids_query ? '' : '{int:start}, ') . '{int:maxindex}',
array(
'current_board' => $id_board,
'current_member' => $id_member,
'topic_list' => $topic_ids,
'is_approved' => 1,
'find_set_topics' => implode(',', $topic_ids),
'start' => $start,
'maxindex' => $per_page,
)
);

The Line
         LEFT JOIN {db_prefix}attachments AS a ON (a.id_member = ml.id_member AND a.id_member != 0)' : '') . '
is as far as i understand it not ok - it will get all attachments of the last poster ? There should be someting like (and a.id_msg=ml.id_msg) ?


Re: MessageIndex / Attachments sql Query

Reply #1
IIRC that JOIN is for fetching the member's avatar (not the attachments) ..
Thorsten "TE" Eurich
------------------------

Re: MessageIndex / Attachments sql Query

Reply #2
Yep, TE is right, it's for the avatars that can be attachments (if configured to be), so it's not a bug. ;)
Bugs creator.
Features destroyer.
Template killer.

Re: MessageIndex / Attachments sql Query

Reply #3
Correct me please if i am wrong, but the only difference of a avatar or attachment is that avatars have id_msg=0 ? This would mean that this query will retrieve all records having id_memeber set to ml.id_member. This will include all attachments of the Member as well ?

Re: MessageIndex / Attachments sql Query

Reply #4
ok, i had a problem of understanding. id_member is only set if it is a avatar.

Thanks