XF 2.2 Query needed to update 250 nodes with new thread type options

Stuart Wright

Well-known member
I've suggested here the ability to batch update nodes, which isn't going to happen before I upgrade AVForums.
I have over 240 nodes to edit, so I need a query to update all children of a specific category node. We have 8 categories that I need to apply this to.

I've got two bits of SQL prepared. This one updates the nodes with the options I want:
UPDATE xf_forum set type_config = {"allowed_thread_types":["poll","article","question"],"allow_answer_voting":true,"allow_answer_downvote":false} where node_id IN...
and I'm trying to find a query to return all the node_ids given each individual category ID (there are only 8 categories):

select node_id,
title,
parent_node_id
from (select * from xf_node
order by parent_node_id, node_id) xf_node,
(select @pv := '295') initialisation
where find_in_set(parent_node_id, @pv) > 0
and @pv := concat(@pv, ',', node_id)
But this second query is returning one less node that it should be.

Is there an SQL guru out there who can help with this second query to get all the nodes it should do, please?
Thanks
 

Mouth

Well-known member
help with this second query to get all the nodes it should do
SQL:
select GROUP_CONCAT(concat (node_id )SEPARATOR ',') as Child
from    (select * from xf_node
         order by parent_node_id, node_id) s,
        (select @pv := '295') initialisation
where   find_in_set(parent_node_id, @pv) > 0
and     @pv := concat(@pv, ',', node_id);
 

Stuart Wright

Well-known member
Thank you but it's still returning one less result than it should do some times.
In this example, I am using the Xbox Forums as the parent ID and it's returning 8 nodes instead of 9. Xbox One Rocksmith is missing.
2.png1.png
 

Mouth

Well-known member
Thank you but it's still returning one less result than it should do some times.
Hmmm, I have 8 top level categories too and working as expected for all of mine. Eg ...

Screen Shot 2020-10-12 at 13.15.45.png

Code:
mysql> select node_id, title  from    (select * from xf_node          order by parent_node_id, node_id) s,         (select @pv := '49') initialisation where   find_in_set(parent_node_id, @pv) > 0 and     @pv := concat(@pv, ',', node_id);
+---------+-----------------------------------------+
| node_id | title                                   |
+---------+-----------------------------------------+
|       3 | Reviews                                 |
|      58 | Bike Reviews, Questions and Suggestions |
|      59 | Riding Gear and Bike Accessories/Parts  |
|      66 | Businesses and Service Providers        |
|      88 | Product Reviews                         |
|     108 | Specials                                |
|      96 | Tyres                                   |
|      97 | Helmets                                 |
|      98 | Gloves                                  |
|      99 | Jackets                                 |
|     100 | Pants                                   |
|     101 | Boots                                   |
|     102 | Motorcycles                             |
|     103 | Everything Else                         |
|     175 | Electronics                             |
|     185 | Luggage                                 |
|     168 | Archived                                |
+---------+-----------------------------------------+
17 rows in set (0.00 sec)

mysql> select node_id, title  from    (select * from xf_node          order by parent_node_id, node_id) s,         (select @pv := '88') initialisation where   find_in_set(parent_node_id, @pv) > 0 and     @pv := concat(@pv, ',', node_id);
+---------+-----------------+
| node_id | title           |
+---------+-----------------+
|      96 | Tyres           |
|      97 | Helmets         |
|      98 | Gloves          |
|      99 | Jackets         |
|     100 | Pants           |
|     101 | Boots           |
|     102 | Motorcycles     |
|     103 | Everything Else |
|     175 | Electronics     |
|     185 | Luggage         |
+---------+-----------------+
10 rows in set (0.00 sec)

Sorry.
 

Stuart Wright

Well-known member
Hmmm, I have 8 top level categories too and working as expected for all of mine. Eg ...

View attachment 237302

Code:
mysql> select node_id, title  from    (select * from xf_node          order by parent_node_id, node_id) s,         (select @pv := '49') initialisation where   find_in_set(parent_node_id, @pv) > 0 and     @pv := concat(@pv, ',', node_id);
+---------+-----------------------------------------+
| node_id | title                                   |
+---------+-----------------------------------------+
|       3 | Reviews                                 |
|      58 | Bike Reviews, Questions and Suggestions |
|      59 | Riding Gear and Bike Accessories/Parts  |
|      66 | Businesses and Service Providers        |
|      88 | Product Reviews                         |
|     108 | Specials                                |
|      96 | Tyres                                   |
|      97 | Helmets                                 |
|      98 | Gloves                                  |
|      99 | Jackets                                 |
|     100 | Pants                                   |
|     101 | Boots                                   |
|     102 | Motorcycles                             |
|     103 | Everything Else                         |
|     175 | Electronics                             |
|     185 | Luggage                                 |
|     168 | Archived                                |
+---------+-----------------------------------------+
17 rows in set (0.00 sec)

mysql> select node_id, title  from    (select * from xf_node          order by parent_node_id, node_id) s,         (select @pv := '88') initialisation where   find_in_set(parent_node_id, @pv) > 0 and     @pv := concat(@pv, ',', node_id);
+---------+-----------------+
| node_id | title           |
+---------+-----------------+
|      96 | Tyres           |
|      97 | Helmets         |
|      98 | Gloves          |
|      99 | Jackets         |
|     100 | Pants           |
|     101 | Boots           |
|     102 | Motorcycles     |
|     103 | Everything Else |
|     175 | Electronics     |
|     185 | Luggage         |
+---------+-----------------+
10 rows in set (0.00 sec)

Sorry.
Could it be because mine go down an extra level?
 

Mouth

Well-known member
Could it be because mine go down an extra level?
I guess, but I cannot see any reason within the SQL why it would stop after x nested levels.
You mentioned in results one less sometimes, so I'd suggest looking at what might be different between the ones that work and the ones that don't
 
Top