XF 2.1 Generating a list of threads from thread IDs

[Sysnative]

Hey all,

I'm trying to create a page that generates a list of threads that have not received any reply from a staff user, specific to a node. I've set up a query to do this as an initial approach, but struggling on generating a list once I have the IDs:

    protected function getThreadsNoStaffReply($nodeid)
    {
        $db = \XF::db();
        $readers = $db->fetchAll('SELECT xf_post.thread_id, xf_thread.node_id, xf_thread.post_date FROM `xf_post`
                                        INNER JOIN xf_thread ON xf_post.thread_id = xf_thread.thread_id
                                        WHERE xf_thread.node_id = ? AND xf_thread.discussion_state = \'visible\'
                                        AND xf_post.thread_id NOT IN (SELECT xf_post.thread_id FROM `xf_post`
                                        INNER JOIN `xf_user` ON  xf_post.user_id = xf_user.user_id
                                        INNER JOIN xf_thread ON xf_post.thread_id = xf_thread.thread_id
                                        WHERE xf_thread.node_id = ? AND xf_user.is_staff = 1)
                                        GROUP BY xf_post.thread_id 
                                        ORDER BY `xf_thread`.`post_date`  DESC',
                                        [$nodeid, $nodeid]);
        return $readers;
    }

(If there is a better way of doing this, happy to learn - I couldn't see an obvious way to do this with an existing entity.)

The question is that once this supplies an array of thread IDs, with a few extra details, what's the best way of now converting this into a thread object, which can be sent to a thread_list style template?

 

[Bespoke]

Why can't you just SELECT xf_thread.* instead of just thread_id, node_id
That should give you a list of threads to use in a template such as widget_new_threads

 

[nocte]

Look at XF\Mvc\Entity::instantiateEntity().

Example Code:

        $em = \XF::em();
        $threads = $em->instantiateEntity(
            'XF:Thread',
            $results
        );

 

[Sysnative]

Hmm... struggling to get that code to work:

LogicException: Cannot instantiate XF:Thread (XF\Entity\Thread) without primary key values in src/XF/Mvc/Entity/Manager.php at line 723

Why can't you just SELECT xf_thread.* instead of just thread_id, node_id

So I could potentially, but this doesn't actually return a thread object - just the raw data. I'm not sure if I'm approaching this in the best way, but I'd ideally like to pull a thread object back for each list of IDs.

I could potentially add an extra column in the thread table, mark it as "staff replied", and then use a Finder to grab the thread entities. The downside with this (as far as I can see) is that I would need to insert code somewhere else to regularly update the extra column to indicate whether or not there was a staff reply.

 

[Bespoke]

Are you looking to fetch a ArrayCollection of Thread Entities using the thread_ids you collected from the SQL statement?
$threads = \XF::finder('XF:Thread')->where('thread_id', [1,2,3,4])->fetch();

 

[Sysnative]

Are you looking to fetch a ArrayCollection of Thread Entities using the thread_ids you collected from the SQL statement?
$threads = \XF::finder('XF:Thread')->where('thread_id', [1,2,3,4])->fetch();

That works perfectly - thank you!

 

[nocte]

Be aware, that your query is very complex and contains a subquery. On a large forum, this could be a performance killer.

I could potentially add an extra column in the thread table, mark it as "staff replied", and then use a Finder to grab the thread entities. The downside with this (as far as I can see) is that I would need to insert code somewhere else to regularly update the extra column to indicate whether or not there was a staff reply.

That would be one way. Another would be to add a table xf_your_addon_thread_staff_answer with columns like thread_id, post_id, user_id. Then you could do a simple JOIN and fetch just threads, that are not in that new table. Of course you would have to fill that table with entries every time a staff member answers to a thread (and maybe delete the entry when the staff member post is deleted).

 

[Sysnative]

The query is realistically only going to run once every few hours at max, as it's a staff only function to check for unanswered threads (we're a tech support forum), so I'm hoping performance will be okay.

I'll monitor this though, and if it's detrimental to performance your alternative approach seems doable.

I'm trying to avoid having to update a separate staff table or thread column on every post save / Cron job, as that seems like it would hurt performance more.

 

[Lukas W.]

I'm trying to avoid having to update a separate staff table or thread column on every post save / Cron job, as that seems like it would hurt performance more.

Independently of the given problem, updating an additional table when already performing a write operation is a neglectible cost. There'll be no feelable impact.
Running a query of that size will render your site slow for a few moments as your number of posts, threads and users grows.

Fwiw, I think this could be done with the finder system completely, but you can at least use it to get the threads from their IDs with it. As it's an indexed query, cost will be neglectible, especially next to that first query.

 

[Sysnative]

Ok - that makes sense. The original query is a proof of concept, so maybe it makes sense to change it to a different approach.

I didn't explore the Finder too much, as I couldn't see an obvious way to return the correct results without an extra column/table, but if there is a way any pointers would be appreciated.

The main problem I found even with the normal query is that I'm having to query the post table, then combine different rows to get an idea if a member of staff has posted.

 

[Snog]

@Lukas W. is correct, you would be better off either adding a "staff_replied" field to the existing xf_thread table and updating it when staff replies to the thread, or creating another table of your own and creating a relationship with the xf_thread table.

But while not pretty (takes 3 queries), and it will become a memory hog as the number of threads on the site grows, this would also work...

$users = $this->finder('XF::User')->where('is_staff', 1)->fetch();
$userIds = $users->pluckNamed('user_id');
$staffIds = implode(',', $userIds);

$postsIn = $this->finder('XF:ThreadUserPost')
	->whereSql("xf_thread_user_post.user_id IN ($staffIds)")
	->fetch();

$allStaffPosts = $postsIn->pluckNamed('thread_id');
$allStaffPosts = array_unique($allStaffPosts);
$queryCondition = implode(',', $allStaffPosts);

$threadsNotReplied = $this->finder('XF:Thread')
	->where('node_id', $nodeid)
	->whereSql("xf_thread.thread_id NOT IN ($queryCondition)")
	->fetch();

 

[Bespoke]

Or use reverse logic...
Create a table of all current threads not replied to by staff.
Then:

• When a new thread is created (not by staff) add it to the table
• When a thread is replied to by staff, remove it from the table