XF 2.0 Use operator "IN" in the Finder system

[abdfahim]

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("
           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));

 

[Jake B.]

You can use whereSql instead of the where method

 

[Mike]

We use IN automatically if you pass an array of values in when using a standard equals condition.

 

[abdfahim]

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 .

 

[abdfahim]

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

 

[Chris D]

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))
   ->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).

 

[abdfahim]

Thanks a lot!