XF 2.2 Best way to use the finder to find a thread based on a tag?

[HJW]

Hi,

If I'm trying to find the most recent open visible thread with a tag (ID or Url whatever is better?)

Is the best way like below to just hit the thread table searching the tag blob using the tag_url/tag_id?

Or should I be joining with the tag table and finding based on the tag_ID?

Just hitting the thread table

$this->app->finder('XF:Thread')
->where('tag', 'LIKE', $finder->escapeLike($tag_url, '%?%'))
->where('discussion_state', '=', 'visible')
->where('discussion_open', '=', 1)
->fetchOne();

Or should I be joining with the tag_content table

$finder->sqlJoin('(
    select content_id as thread_id
    from xf_tag_content
    where content_type = \'thread\' and tag_id in (' . $db->quote($tagIds) . ')
    group by content_id
    having count(*) >= '. $db->quote(count($tagIds)) .'
)', 'taggedThread', ['thread_id'], true, true);
$finder->sqlJoinConditions('taggedThread', ['thread_id']);

 

[Xon]

The second approach (which I believe uses my Standard lib add-on) will work best as it uses indexed lookups and will support fetching multiple unique threads. The first first query can only fetch 1 at a time, and requires a table scan of a text field and can have false positives based on the tag.

 

[Kirby]

I think the first version wouldn't work at all as there is no field xf_thread.tag

The 2nd option would work if you have Xon Standard Lib installed; it does not work with plain XenForo.

What about a 3rd option?

$tagId = 1234;

$taggedContentFinder = \XF::finder('XF:TagContent');
$threadIdSubQuery = $taggedContentFinder
    ->where('content_type', 'thread')
    ->where('content_id', '>', 0)
    ->where('tag_id', $tagId)
    ->getQuery(['fetchOnly' => ['content_id']]);

$threadFinder = \XF::finder('XF:Thread');
$thread = $threadFinder
    ->where('discussion_state', 'visible')
    ->where('discussion_open', 1)
    ->whereSql($threadFinder->columnSqlName('thread_id') . ' IN (' .  $threadIdSubQuery . ')')
    ->order('post_date', 'desc')
    ->getQuery();

This should work with standard XenForo and use indexes.

 

[HJW]

I think the first version wouldn't work at all as there is no field xf_thread.tag

The 2nd option would work if you have Xon Standard Lib installed; it does not work with plain XenForo.

What about a 3rd option?

$tagId = 1234;

$taggedContentFinder = \XF::finder('XF:TagContent');
$threadIdSubQuery = $taggedContentFinder
    ->where('content_type', 'thread')
    ->where('content_id', '>', 0)
    ->where('tag_id', $tagId)
    ->getQuery(['fetchOnly' => ['content_id']]);

$threadFinder = \XF::finder('XF:Thread');
$thread = $threadFinder
    ->where('discussion_state', 'visible')
    ->where('discussion_open', 1)
    ->whereSql($threadFinder->columnSqlName('thread_id') . ' IN (' .  $threadIdSubQuery . ')')
    ->order('post_date', 'desc')
    ->getQuery();

This should work with standard XenForo and use indexes.

thanks so much, works perfectly