XF 2.1 Writing query to get the most reacted posts per tag

[Pol57]

Hello,

Could someone help as to how to do this with the xenforo finder?

I'm writing a query to get the most reacted posts per tag, it's easy enough with sql:

SELECT t.tag_url, th.title FROM `xf_tag_content` as tc, xf_tag as t, xf_thread as th
where tc.content_type = "thread"
and t.tag_id = tc.tag_id
and th.thread_id = tc.content_id

But I'm a bit stuck as to how to write this for XF2. This is my code at the moment just to check the tag exists and to get all posts, now I need to filter it down to just get the posts that are in a thread with the tag:

        $tag = null;
        $tagthreads = null;

        if ($tagUrl = $params->get('tag_url'))
        {
            $tag = $this->finder('XF:Tag')
                ->where('tag_url', $tagUrl)
                ->fetchOne();
        }

        if (!$tag)
        {
            return $this->error(\XF::phrase('requested_tag_not_found'), 404);
        }

        $visitor = \XF::visitor();

        $router = $this->app->router('public');

        $postFinder = $this->finder('XF:Post');
        $postFinder
            ->with(['Thread.Forum.Node.Permissions|' . $visitor->permission_combination_id, 'User'])
            ->where('Thread.discussion_state', 'visible')
            ->where('message_state','visible')
            ->where('reaction_score', '>', 0)
            ->setDefaultOrder('reaction_score', 'DESC')