XF 2.0 Use operator "IN" in the Finder system


Well-known member
I selected few thread_id based on certain criteria. Now I want to get the thread, forum and user entities for all those IDs. But in the XF2 doc, I don't see "IN" in the supported operator list for the Finder system. Is there any way to do that?
$values = $this->db()->fetchAllKeyed("
            count(xf_post.post_id) totalNewPost
            FROM xf_post
            xf_post.post_date > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 7 DAY))
            GROUP BY xf_post.thread_id
            HAVING totalNewPost > 0
            ORDER BY totalNewPost DESC
            LIMIT 5", "thread_id");

        $threads = $this->finder('XF:Thread')
            ->with(['Forum', 'User'])
            ->where('thread_id', 'IN', array_keys($values));
Last edited:


Well-known member
Thanks, that worked nicely.

However, as expected, the sort of $values was not honored by the Finder system while fetching $threads.

Last night I spend hours on a workaround but failed.

Just now I realized there is a nice sorting option in SQL other than usual ASC/DESC, where I can actually tell the exact sorting order:
SELECT * from xf_thread where thread_id IN (1, 2, 3, 4, 5) ORDER BY FIELD (thread_id, 4,5,1,3,2)
I'll try that tonight when I go home, but can't really concentrate on my study until I know if this would work:
$threads = $this->finder('XF:Thread')
            ->with(['Forum', 'User'])
            ->where('thread_id', 'IN', array_keys($values))
            ->order('thread_id', array_keys($values));
If anyone can kindly confirm, that would help me doing what I am supposed to do now :p.
Last edited:


Well-known member
Sorry, corrected some typo in previous post .. last line of code should be ->order('thread_id', array_keys($values))

Chris D

XenForo developer
Staff member
You might be able to use a finder expression for this, something along these lines:
$finder = $this->finder('XF:Thread');
$threadIdsQuoted = \XF::db()->quote(array_keys($values));
$orderExpression = $finder->expression("FIELD(%s, $threadIdsQuoted", 'thread_id');

$threads = $finder
   ->with(['Forum', 'User'])
   ->where('thread_id', array_keys($values))
Note, as Mike suggested before, you don't need to do the 'IN' operator.

Incidentally, you might be able to use finder expressions for some of your more complicated query stuff which might enable you to use the finder for some things (though, really, that's optional as clearly the syntax can end up making it look a little more convoluted).