Is there a quick way to mark all categories with 0 products as inactive?

U03T08LE3CP

Is there a quick way to mark all categories with 0 products as inactive?

U03T08LE3CP

(other than querying and setting so from SQL)

U04FXCTCWPP

Not to my knowledge. The SQL way you've mentioned does seem to be the simplest, quickest solution to me too.

U03T08LE3CP

it gets kinda tricky to get only leaf nodes but doable, lets see

U03T08LE3CP

update spy_category set is_active = 0 where spy_category.id_category not in (
with recursive tree (id_category, category_key, fk_parent_category_node, is_active) as (
    select id_category, category_key, fk_parent_category_node, is_active from spy_category sp
    left join spy_product_category spc on sp.id_category = spc.fk_category
    left join spy_category_node ndc on sp.id_category = ndc.fk_category
    where spc.fk_category is not NULL
    and ndc.fk_parent_category_node is not null
    UNION ALL
    select p.id_category, p.category_key, ndc.fk_parent_category_node, p.is_active from spy_category p
    left join spy_product_category spc on p.id_category = spc.fk_category
    left join spy_category_node ndc on p.id_category = ndc.fk_category
    inner join tree on p.id_category = tree.fk_parent_category_node
)
select distinct (id_category) from tree order by category_key);

U03T08LE3CP

Maybe.

U04FXCTCWPP

Looks good 👍

U03T08LE3CP

docker/sdk console publish:trigger-events -r category_node

U03T08LE3CP

is this the command to update search? Seems to take forever

U04FXCTCWPP

Based on the description, looks like that to me.
(Although previously, we used console event:trigger -r [resource name] for such updates, and that was pretty fast (if it only had small amounts of data to work with.)

U04FXCTCWPP

(You should also be able to use -i [comma separated id list] to specify ids you'd like to update and then it can be even faster as then it won't need to bother with the rest, just with the ones you specified).