Category Viewing thought.

peregrine

I haven't experimented with this. but was wondering about easy fixes, or possible thoughts on implementation of it.

lets say you have

All Discussions   

Category A    
    category 1    
    category 2   

Category B    
      category 3    
      category 4    

All Discussions - shows all discussions in all categories

Does vanilla have the ability through current plugins or functionality to allow something similar with Categories and sub-categories.

to click on Category A and see all discussions related to category 1 and category 2

to click on Category 1 and see all discussions related to category 1

to click on Category 2 and see all discussions related to category 2

to click on Category B and see all discussions related to category 3 and category 4

to click on Category 3 and see all discussions related to category 3

to click on Category 4 and see all discussions related to category 4

peregrine

probably would require some major change to controller or model, I suspect.

seems like it would be handy though.

businessdad

Personally, I like self-contained categories, but what you are describing can be done quite easily on Vanilla, since it stored categories using both ad an adjacency list and a modified modified preorder tree. If you look in Gdn_Category table, you will see that it contains two columns, called TreeLeft and TreeRight. They can be used to retrieve the category hierarchy in one shot.

If you return the same fields expected by the controller, you should be able to leave it alone, and just alter the query on a Model::BeforeGet() event.

Example (with random values)

Target category: Parenting.

• Category ID: 5.

How to retrieve all the categories under Parenting category

SELECT
    CC.CategoryID AS ChildCategoryID
    ,CC.Name As ChildCategoryName
FROM
    -- Parent category
    Gdn_Category PC 
    JOIN
    -- Children categories
    Gdn_Category CC ON 
        (CC.TreeLeft BETWEEN PC.TreeLeft AND PC.TreeRight)      
WHERE
    (PC.CategoryID = 5)
-- ORDER BY <Whatever>

How to retrieve all the discussions under Parenting category and its subcategories

SELECT
    CC.CategoryID AS ChildCategoryID
    ,CC.Name As ChildCategoryName
    ,D.DiscussionID
    ,D.Name AS DiscussionName
    -- ,D.<other discussion fields>
FROM
    -- Parent category
    Gdn_Category PC 
    JOIN
    -- Children categories
    Gdn_Category CC ON 
        (CC.TreeLeft BETWEEN PC.TreeLeft AND PC.TreeRight)
    JOIN
    -- Discussions in each child category
    Gdn_Discussion D ON
        (D.CategoryID = CC.CategoryID)
WHERE
    (PC.CategoryID = 5)
-- ORDER BY <Whatever>

businessdad

Correction: in my example, I wrote "Category ID: 15", while it should have been "Category ID: 5" (just below "Target category"). If any moderator can fix it, it would avoid confusion. Thanks.

whu606

@businessdad

Done (I think...)

peregrine

thanks for the input and insights @businessdad.

Linc

I would strongly recommend doing this in PHP with the data from CategoryModel::Categories() or GetSubtree(). Doing it in MySQL isn't necessary and I suspect those queries will murder your db performance on large sites. The methods I referenced will use the cache if available.

peregrine

your insights are always valuable @Lincoln , thx.

xanthos84

hi everyone!

I am a new vanilla user, and I want to achieve exactly whats asked in the first post -
can anyone give a short tutorial on how to change the php files?

Thank you so much! I am really lost for now...