Automatically assign parent category to sub-category entries?

Question

I have a parent category called, 'Reviews', which has sub-categories, 'Albums', 'Tracks' and 'Gigs'.

The entries in the sub-categories had already been assigned those sub-categories before the parent category was created.

Is there any way I can automatically add the 'Reviews' (parent) category to all of its sub-category entries?

Answer 1

A couple of things. First, going forward, make sure that the Auto-Assign Category Parents option (under Admin → Channel Administration → Global Preferences) is set to yes.

Second, to fix all of the pre-existing entries, backup your database, create a new template, enable PHP on it, put this in it, then load the template in your browser. (You'll need to find the Category ID of each of the relevant categories and replace them below.)

<?php

$this->EE =& get_instance();

$child_categories = "1,2,3";
$parent_category = "4";

$entries = $this->EE->db->query("SELECT DISTINCT entry_id FROM exp_category_posts WHERE cat_id IN($child_categories)");
if($entries->num_rows() > 0)
{
    foreach($entries->result_array() as $entry)
    {
        $this->EE->db->query(
            $this>EE->db->insert_string(
                'exp_category_posts',
                array('cat_id' => $parent_category, 'entry_id' => $entry['entry_id'])
            )
        );
    }
}

?>

Answer 2

Fix for 2.6.1

<?php
$child_categories = "1,2,3";
$parent_category = "4";
$entries = ee()->db->query("SELECT DISTINCT entry_id FROM exp_category_posts WHERE cat_id IN($child_categories)");

if($entries->num_rows() > 0)
{
    foreach($entries->result_array() as $entry)   
    {
            ee()->db->query(
                    ee()->db->insert_string(
                         'exp_category_posts',
                    array('cat_id' => $parent_category, 'entry_id' => $entry['entry_id'])
                    )
                    );
     }
}

?>

Answer 3

You could do this with a custom extension although my advice would be to reconsider your data architecture.

Can you post some more information on why you want to do this including channel setup and custom fields?

Answer 4

To avoid having problems with duplicate MySQL Errors, add this after the parent categoty ID variable

$parent_category = "21";

$delete = ee()->db->query("delete FROM exp_category_posts WHERE cat_id IN($parent_category)");

that will delete all the old relations to the parent category -_^