• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 2.0 Use operator "IN" in the Finder system

abdfahim

Active member
#1
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?
Code:
$values = $this->db()->fetchAllKeyed("
           SELECT
            xf_post.thread_id,
            count(xf_post.post_id) totalNewPost
            FROM xf_post
            WHERE 
            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:

abdfahim

Active member
#4
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:
Code:
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:
Code:
$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:

Chris D

XenForo developer
Staff member
#6
You might be able to use a finder expression for this, something along these lines:
PHP:
$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))
   ->order($orderExpression);
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).