Thread

I am looking for a way to capture the top-level category from the child categories or grandchild categories. What is the best way to do this. I am working on a client site that is using the categories tag to show the top level categories from a weblog and then applying a class of selected to that element. The problem is when the child categories are selected. Then the selected class of the top-level elements is lost. Any ideas?

See if this helps: http://ellislab.com/forums/viewthread/87639/
If not then best bet is query module.

Thanks, I tried that, but it didn’t do what I wanted. I will look into the query module.

What would a query look like to pull a top level category from a subcategory?

Depends on your setup.
Starting point is in following table(s):

SELECT
exp_categories.cat_id,
exp_categories.parent_id,
exp_categories.cat_image,
exp_categories.cat_url_title,
exp_categories.cat_name,
exp_categories.group_id
FROM
exp_categories
WHERE
exp_categories.parent_id >  '0' AND
exp_categories.group_id =  '2'

the problem with doing this via SQL is that SQL can’t do recursion, so you can’t have a single easy query work for an arbitrary level of nested categories.  if you know how many levels you’ve got you can create a query for that using self-joins though.

Thanks ender. That is the problem, there could be an unknown number of levels depending on how many subcategories the client adds.

That is why I said it depends on setup and what is “top level”. Can a child be a top-level?
eg.

|_Cars
..|_Sedans*
....|_Two Door
....|_Four Door
|_RVs
|_Trucks

So here “sedans” is a child category but also a Parent Category. Here it would not work as it has the parent AND is parent itself.

IF this is NOT the case then categories are pretty ‘flat’ and are only marked as parents with exp_categories.parent_id >  ‘0’...and you would know a group ID if very weblog has its own group assigned.
Again, you would have to make it specific to your setup…
For what is worth.

In my case a child category can not be a top level category. The one constant that I do know is that the top level category that I want to pull out will always have a parent id of 0.

😊 there you go then, the query above should get you started.

Thanks lebisol, could you provide me a statement of what that query would look like using the query tags? I am just getting started in this database stuff and I am not sure how to get it started.

In your template:

{exp:query sql="
    RAW SQL HERE ;"}
Print/Show Stuff 
{/exp:query}

In essence:

{exp:query sql="
SELECT
    exp_categories.cat_id,
    exp_categories.parent_id,
    exp_categories.cat_image,
    exp_categories.cat_url_title,
    exp_categories.cat_name,
    exp_categories.group_id
FROM
    exp_categories
WHERE
    exp_categories.parent_id >  '0' AND
    exp_categories.group_id =  '2';"}
Category Name: {cat_name}, Category Image: {cat_image} 
{/exp:query}

Here is a link to some docs/samples.
Give it a try and post back if you get stuck…

Okay, now I am getting somewhere. One final question. Is is possible to run a check and see if the value in a segment belongs to that particular category?

Here is what I am thinking.

{exp:query sql="SELECT exp_categories.cat_name FROM exp_categories WHERE exp_categories.parent_id = '0' AND exp_categories.group_id = '1';"}
                {if segment_3 == "is a child of parent_id = '0'}
                        cat name: {cat_name}
                        {/exp:query}

Now, I know my code is off, but that is what I am thinking.

Yes, just add the “AND” condition for sql something like:

...WHERE ... AND exp_categories.cat_id='{segment_3}'

OR if you are using category names in url

...WHERE ... AND exp_categories.cat_name='{segment_3}'

p.s. wrap your code in [-code-] tag for easier reading on forum posts.