How To Add Parent Forum Name To This Code?

[ADRBossman]

I have acquired the parent ID but now I need to create another select query (I think) so I can acquire the parent forum name. It should be an easy fix.  I am unfamiliar with this forum code though.

Code Select Expand

$request = $smcFunc['db_query']('', '
SELECT
ms.poster_time as firstTime, m.poster_time as lastTime, ms.icon as first_icon, ms.subject, m.id_topic, t.num_replies, t.num_views,
ms.id_member as id_first_poster, m.id_member as id_last_poster, m.id_msg, b.id_board, b.id_parent, b.name AS bName,
IFNULL(mem2.real_name, ms.poster_name) AS firstPoster,
IFNULL(mem.real_name, m.poster_name) AS lastPoster
FROM {db_prefix}topics AS t
INNER JOIN {db_prefix}messages AS m ON (m.id_msg = t.id_last_msg)
INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
INNER JOIN {db_prefix}messages AS ms ON (ms.id_msg = t.id_first_msg)
LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)
LEFT JOIN {db_prefix}members AS mem2 ON (mem2.id_member = t.id_member_started)
WHERE t.id_last_msg >= {int:min_msg_id}
AND ' . $query_this_board . ($modSettings['postmod_active'] ? '
AND t.approved = {int:is_approved}
AND m.approved = {int:is_approved}' : '') . '
' . $latest_post . '
ORDER BY t.id_last_msg DESC
LIMIT {int:limit}',
array_merge($query_parameters, [
'min_msg_id' => $min_msg_id,
'is_approved' => 1,
'limit' => $modSettings['recent_topics_number_topics'],
])
);

while ($row = $smcFunc['db_fetch_assoc']($request))
{
if (!empty($modSettings['messageIconChecks_enable']))
{
if (!isset($context['icon_sources'][$row['first_icon']]))
$context['icon_sources'][$row['first_icon']] = file_exists($settings['theme_dir'] . '/images/post/' . $row['first_icon'] . '.png') ? 'images_url' : 'default_images_url';
}
else
{
if (!isset($context['icon_sources'][$row['first_icon']]))
$context['icon_sources'][$row['first_icon']] = 'images_url';
}

$context['topics'][] = [
'id' => $row['id_topic'],
'first_icon' => $row['first_icon'],
'icon_url' => $settings[$context['icon_sources'][$row['first_icon']]] . '/post/' . $row['first_icon'] . '.png',
'subject' => $row['subject'],
'href' => $scripturl . '?topic=' . $row['id_topic'] . '.0',
'link' => '<a href="' . $scripturl . '?topic=' . $row['id_topic'] . '.0">' . $row['subject'] . '</a>',
'replies' => comma_format($row['num_replies']),
'views' => comma_format($row['num_views']),
'board' => [
'id' => $row['id_board'],
'name' => $row['bName'],
'href' => $scripturl . '?board=' . $row['id_board'] . '.0',
'link' => '<a href="' . $scripturl . '?board=' . $row['id_board'] . '.0">' . $row['id_parent'] . ' ' . $row['bName'] . '</a>',
],

The above 'link' id_parent needs to be altered to the parent forum name instead.

[mickjav]

You should be able to get the parent from the boards table and use a join to get it in query query.

At work at moment so can't help more

[Arantor]

I sort of question the premise why you'd get a board parent rather than the board the topic is actually in, but... eh, whatever.

Code Select Expand

$request = $smcFunc['db_query']('', '
SELECT
ms.poster_time as firstTime, m.poster_time as lastTime, ms.icon as first_icon, ms.subject, m.id_topic, t.num_replies, t.num_views,
ms.id_member as id_first_poster, m.id_member as id_last_poster, m.id_msg, b.id_board, b.id_parent, b.name AS bName,
                        COALESCE(bp.name, b.name) AS parent_name,
IFNULL(mem2.real_name, ms.poster_name) AS firstPoster,
IFNULL(mem.real_name, m.poster_name) AS lastPoster
FROM {db_prefix}topics AS t
INNER JOIN {db_prefix}messages AS m ON (m.id_msg = t.id_last_msg)
INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
                        LEFT JOIN {db_prefix}boards AS bp ON (bp.id_board = b.id_parent)
INNER JOIN {db_prefix}messages AS ms ON (ms.id_msg = t.id_first_msg)
LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)
LEFT JOIN {db_prefix}members AS mem2 ON (mem2.id_member = t.id_member_started)
WHERE t.id_last_msg >= {int:min_msg_id}
AND ' . $query_this_board . ($modSettings['postmod_active'] ? '
AND t.approved = {int:is_approved}
AND m.approved = {int:is_approved}' : '') . '
' . $latest_post . '
ORDER BY t.id_last_msg DESC
LIMIT {int:limit}',
array_merge($query_parameters, [
'min_msg_id' => $min_msg_id,
'is_approved' => 1,
'limit' => $modSettings['recent_topics_number_topics'],
])
);

while ($row = $smcFunc['db_fetch_assoc']($request))
{
if (!empty($modSettings['messageIconChecks_enable']))
{
if (!isset($context['icon_sources'][$row['first_icon']]))
$context['icon_sources'][$row['first_icon']] = file_exists($settings['theme_dir'] . '/images/post/' . $row['first_icon'] . '.png') ? 'images_url' : 'default_images_url';
}
else
{
if (!isset($context['icon_sources'][$row['first_icon']]))
$context['icon_sources'][$row['first_icon']] = 'images_url';
}

$context['topics'][] = [
'id' => $row['id_topic'],
'first_icon' => $row['first_icon'],
'icon_url' => $settings[$context['icon_sources'][$row['first_icon']]] . '/post/' . $row['first_icon'] . '.png',
'subject' => $row['subject'],
'href' => $scripturl . '?topic=' . $row['id_topic'] . '.0',
'link' => '<a href="' . $scripturl . '?topic=' . $row['id_topic'] . '.0">' . $row['subject'] . '</a>',
'replies' => comma_format($row['num_replies']),
'views' => comma_format($row['num_views']),
'board' => [
'id' => $row['id_parent'] ?: $row['id_board'],
'name' => $row['parent_name'],
'href' => $scripturl . '?board=' . ($row['id_parent'] ?: $row['id_board']) . '.0',
'link' => '<a href="' . $scripturl . '?board=' . ($row['id_parent'] ?: $row['id_board']) . '.0">' . $row['parent_name'] . '</a>',
],

I've made the assumption that you also have topics in actual top level boards where there won't be a parent board and dealt with all the edge cases that produces, so if there isn't a parent board you just get the regular board name *instead*.

[ADRBossman]

I sort of question the premise why you'd get a board parent rather than the board the topic is actually in, but... eh, whatever.

Code Select Expand

$request = $smcFunc['db_query']('', '
        SELECT
            ms.poster_time as firstTime, m.poster_time as lastTime, ms.icon as first_icon, ms.subject, m.id_topic, t.num_replies, t.num_views,
            ms.id_member as id_first_poster, m.id_member as id_last_poster, m.id_msg, b.id_board, b.id_parent, b.name AS bName,
                        COALESCE(bp.name, b.name) AS parent_name,
            IFNULL(mem2.real_name, ms.poster_name) AS firstPoster,
            IFNULL(mem.real_name, m.poster_name) AS lastPoster
        FROM {db_prefix}topics AS t
            INNER JOIN {db_prefix}messages AS m ON (m.id_msg = t.id_last_msg)
            INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
                        LEFT JOIN {db_prefix}boards AS bp ON (bp.id_board = b.id_parent)
            INNER JOIN {db_prefix}messages AS ms ON (ms.id_msg = t.id_first_msg)
            LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)
            LEFT JOIN {db_prefix}members AS mem2 ON (mem2.id_member = t.id_member_started)
        WHERE t.id_last_msg >= {int:min_msg_id}
            AND ' . $query_this_board . ($modSettings['postmod_active'] ? '
            AND t.approved = {int:is_approved}
            AND m.approved = {int:is_approved}' : '') . '
            ' . $latest_post . '
        ORDER BY t.id_last_msg DESC
        LIMIT {int:limit}',
        array_merge($query_parameters, [
            'min_msg_id' => $min_msg_id,
            'is_approved' => 1,
            'limit' => $modSettings['recent_topics_number_topics'],
        ])
    );

    while ($row = $smcFunc['db_fetch_assoc']($request))
    {
        if (!empty($modSettings['messageIconChecks_enable']))
        {
            if (!isset($context['icon_sources'][$row['first_icon']]))
                $context['icon_sources'][$row['first_icon']] = file_exists($settings['theme_dir'] . '/images/post/' . $row['first_icon'] . '.png') ? 'images_url' : 'default_images_url';
        }
        else
        {
            if (!isset($context['icon_sources'][$row['first_icon']]))
                $context['icon_sources'][$row['first_icon']] = 'images_url';
        }

        $context['topics'][] = [
            'id' => $row['id_topic'],
            'first_icon' => $row['first_icon'],
            'icon_url' => $settings[$context['icon_sources'][$row['first_icon']]] . '/post/' . $row['first_icon'] . '.png',
            'subject' => $row['subject'],
            'href' => $scripturl . '?topic=' . $row['id_topic'] . '.0',
            'link' => '<a href="' . $scripturl . '?topic=' . $row['id_topic'] . '.0">' . $row['subject'] . '</a>',
            'replies' => comma_format($row['num_replies']),
            'views' => comma_format($row['num_views']),
            'board' => [
                'id' => $row['id_parent'] ?: $row['id_board'],
                'name' => $row['parent_name'],
                'href' => $scripturl . '?board=' . ($row['id_parent'] ?: $row['id_board']) . '.0',
                'link' => '<a href="' . $scripturl . '?board=' . ($row['id_parent'] ?: $row['id_board']) . '.0">' . $row['parent_name'] . '</a>',
            ],

I've made the assumption that you also have topics in actual top level boards where there won't be a parent board and dealt with all the edge cases that produces, so if there isn't a parent board you just get the regular board name *instead*.

Yes.

The above code is part of the AJAX Recent Topics mod.

The way I have my forum is that sub-forums are the only forums where topics are allowed.  This makes the Ajax Recent Topics display a little vague.  To display the parent forum name alongside the sub-forum name would simplify matters. 

I just need to run an additional query using the parent ID I now have (I think?).

[Arantor]

No, you don't need an extra query.

If you look at the code I wrote, I did an extra join on the board table to fetch the parent and pushed it out of the query as parent_name, then wired everything up to check if the parent id was actually a board id, and use it (or use the board itself if it didn't have a parent.

[ADRBossman]

No, you don't need an extra query.

If you look at the code I wrote, I did an extra join on the board table to fetch the parent and pushed it out of the query as parent_name, then wired everything up to check if the parent id was actually a board id, and use it (or use the board itself if it didn't have a parent.

Cheers.

Works a treat