XF 2.1 [SQL] Batch Update Custom Thread Fields based on Nodes?

[sbj]

Hey,

I want to insert value_id X of a custom thread field to ALL threads of a node.

Basically I have hundeds, or thousands of threads and I don't want to manually update all of them. Too tiresome to do this for hundreds of threads using the overlays on front-end. The batch-update of XF doesn't have that function sadly.

Someone friendly helped me, but it doesn't work right now. Don't want to steal his time anymore so I thought I ask it publicly.

What I have so far is this:

DELETE xf FROM xf_thread_field_value xf
LEFT JOIN xf_thread xt ON xt.thread_id = xf.thread_id
WHERE xt.node_id = 2
AND xf.field_id = 'CTF1'

INSERT INTO xf_thread_field_value (thread_id, field_id, field_value)
SELECT thread_id, 'CTF1', 'value_id_x'
FROM xf_thread
WHERE node_id = 2

These queries ran successfully in phpmyadmin, but on XF I was getting this error:

Spoiler

Server error log

    ErrorException: Template error: Invalid argument supplied for foreach() src\XF\CustomField\Definition.php:31

    Generated by: xxx 25 April 2019 at 13:20

Stack trace

#0 src\XF\CustomField\Definition.php(31): XF\Template\Templater->handleTemplateError(2, 'Invalid argumen...', 'C:\\xampp\\htdocs...', 31, Array)
#1 [internal function]: XF\CustomField\Definition->getFormattedValue(false)
#2 src\XF\Template\Templater.php(985): call_user_func_array(Array, Array)
#3 internal_data\code_cache\templates\l1\s2\public\custom_fields_macros.php(115): XF\Template\Templater->method(Object(XF\CustomField\Definition), 'getFormattedVal...', Array)
#4 src\XF\Template\Templater.php(701): XF\Template\Templater->{closure}(Object(XF\Template\Templater), Array, Array)
#5 internal_data\code_cache\templates\l1\s2\public\custom_fields_macros.php(69): XF\Template\Templater->callMacro('custom_fields_m...', 'custom_field_va...', Array, Array)
#6 src\XF\Template\Templater.php(701): XF\Template\Templater->{closure}(Object(XF\Template\Templater), Array, Array)
#7 internal_data\code_cache\templates\l1\s2\public\custom_fields_macros.php(28): XF\Template\Templater->callMacro('custom_fields_m...', 'custom_fields_v...', Array, Array)
#8 src\XF\Template\Templater.php(701): XF\Template\Templater->{closure}(Object(XF\Template\Templater), Array, Array)
#9 internal_data\code_cache\templates\l1\s2\public\thread_view.php(476): XF\Template\Templater->callMacro('custom_fields_m...', 'custom_fields_v...', Array, Array)
#10 src\XF\Template\Templater.php(1301): XF\Template\Templater->{closure}(Object(XF\Template\Templater), Array)
#11 src\XF\Template\Template.php(24): XF\Template\Templater->renderTemplate('thread_view', Array)
#12 src\XF\Mvc\Renderer\Html.php(48): XF\Template\Template->render()
#13 src\XF\Mvc\Dispatcher.php(418): XF\Mvc\Renderer\Html->renderView('XF:Thread\\View', 'public:thread_v...', Array)
#14 src\XF\Mvc\Dispatcher.php(400): XF\Mvc\Dispatcher->renderView(Object(XF\Mvc\Renderer\Html), Object(XF\Mvc\Reply\View))
#15 src\XF\Mvc\Dispatcher.php(360): XF\Mvc\Dispatcher->renderReply(Object(XF\Mvc\Renderer\Html), Object(XF\Mvc\Reply\View))
#16 src\XF\Mvc\Dispatcher.php(53): XF\Mvc\Dispatcher->render(Object(XF\Mvc\Reply\View), 'html')
#17 src\XF\App.php(2177): XF\Mvc\Dispatcher->run()
#18 src\XF.php(390): XF\App->run()
#19 index.php(20): XF::runApp('XF\\Pub\\App')
#20 {main}

Request state

array(4) {
  ["url"] => string(144) "/forum/xxx.51168/"
  ["referrer"] => string(60) "http://localhost/_forum_/xxx.36/"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(0) {
  }
}

Now after I got that error, I got told to do this to all OTHER existing CTFs in that node. For each of them:

INSERT INTO xf_thread_field_value (thread_id, field_id, field_value)
SELECT thread_id, 'CTF2', ''
FROM xf_thread
WHERE node_id = 2
ON DUPLICATE KEY update field_id = field_id

Still the error is happening and I don't see the Custom Thread Field with those values appearing on frontend. Of course I rebuilt the threads but no luck.

Anybody any idea? Or do I need a PHP part to update the threads successfully? Thanks.

 

[Veer]

@sbj did you found a working solution for this? I have posted a similar suggestion you may like to support: https://xenforo.com/community/threads/batch-update-custom-thread-fields.180674/

 

[sbj]

@Veer
I paid 2 people to write me a script. It works for single-line text box, multi-line text box, drop down selection, multiple-choice drop down.

I can either update by field id, by node id, by user id or by thread id.

"prefix" is not for prefix id. I can enter text like "This is my prefix text" + "this is my field value text". So I can enter 2 phrases.

Counter adds a number to the field value (which is a text). So if I enter "1", it starts by one and counts up. This way I can enter field values + counter, like "this is my id + counter". So the first thread for that field has the text "this is my id 1", the second one "this is my id 2" and so on.

To validate it you enter your admin username, so that you don't mess up things easily.

"Does not lose data" works for drop downs. If checked, it adds choice values to the existing values. So, let's say my field is "clothes" and in the thread I saved it as "red". Then with script I can add "blue, green" to that thread, and it will be "red, blue, green". If not checked, then it overwrites red with "blue, green".

Then you can press the "send" button and the script runs.