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

[Stuart Wright]

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

 

[Stuart Wright]

Anyone?

 

[Mouth]

help with this second query to get all the nodes it should do

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]

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.

 

[Mouth]

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

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]

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

View attachment 237302

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]

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

 

[Stuart Wright]

Ok, I found a solution. The problem occurs when the child node ID is less than the parent node ID.
The new code which works uses the with clause, so you need mySLQ 8+:

with recursive cte (node_id, title, parent_node_id) as (
  select     node_id,
             title,
             parent_node_id
  from       xf_node
  where      parent_node_id = 495 and node_type_id = 'Forum' and display_in_list = 1
  union all
  select     p.node_id,
             p.title,
             p.parent_node_id
  from       xf_node p
  inner join cte on p.parent_node_id = cte.node_id
             where p.node_type_id = 'Forum' and display_in_list = 1
)
select * from cte;

The desired parent node is 495 in this case.
And I am only including Forum type nodes and those publicly visible.

 

[djbaxter]

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:

@Stuart Wright

In the OP you used quote instead of code for the UPDATE SQL and it's cut off.

Can you repost using the code BBCode?

 

[Stuart Wright]

I got the list of node IDs to update from the first query.
This successfully ran on our sandbox.

UPDATE xf_forum set type_config = '{"allowed_thread_types":["poll","article","question"],"allow_answer_voting":true,"allow_answer_downvote":false}' where node_id IN(104,367,155,232,82,87,91,107,114,145,113,246,47,43,44,130,45,307,183,390,73,164,284,168,406,37,55,298,563,447,78,420,407,54,80,61,280,270,192,106,392,446,291,410,100,247,206,271,96,575,50,576,162,463,279,583,414,38,59,123,129,137,152,203,148,239,259,263,332,373,127,147,245,224,333,272,460,175,240,430,371,105,357,491,423,553,35,273,274,436,482,487,188,211,212,484,126,547,32,189,194,577,578,205,493,544,545,494,540,327,424,542,486,234,481,543,33,258,554,558,262,550,551,552)