I have a question regarding displaying categories. A category can have a subcategory. The user can indicate a display order to the categories. The table looks like this:
In this example fruits and vegetables are subcategories of non-beverages. Big and small candy are subcategories of candy.
I am using a listview which initially displays all categories which havent got a ParentCategoryID. If the user clicks on a category which has subcategories, I want the listview to show all categories which havent got a ParentCategoryID as well as all the
children for the clicked category. (I can handle this with a simple itemcommand event on the listview).
I do NOT know how I can get the list with items 1 to 5 if I click item 2 (non-beverages).
The list I want is the following one: (Notice, the subcategories of candy are not displayed!)
Beverages
Non-Beverages
Fruits
Vegetables
Candy
Any help is welcome. I do also want the ID because I need those values when clicked.
with CteRec as
(
select
c.ID,
c.CategoryName,
c.DisplayOrder as ParentDisplayOrder,
0 as DisplayOrder,
c.ParentCategoryID
from @tblCategory as c
where c.ParentCategoryID is null
union all
select
c.ID,
c.CategoryName,
r.ParentDisplayOrder as ParentDisplayOrder,
c.DisplayOrder,
c.ParentCategoryID
from @tblCategory as c
join CteRec as r on r.ID = c.ParentCategoryID
where c.ParentCategoryID = 2
)
select * from CteRec
order by ParentDisplayOrder, DisplayOrder
(I let out 'where c.ParentCategoryID=2' because ParentCategoryID is a uniqueIdentifier and wouldnt work like this)
This is great! But it works partially:
it displays all subcategories of all parentcategories. (The categoryID will be sent to the stored procedure to indicate which item has to expand its children)
as long as I am working with 1 subcategory.
Is it possible to only expand only the 'children' of the categorie indicated in a parameter?
Is it possible to have it working with a number of subcategories? I mean that the subcategory has subcategories, that subcategory of a subcategory can have subcategories as well,... (without knowing how many subcategories there are?)
with CteRec as
(
select
c.ID,
c.CategoryName,
Cast(c.DisplayOrder as float) as ParentDisplayOrder,
0 as DisplayOrder,
c.ParentCategoryID,
c.ID as MainID,
1 as LevelNo
from tblCategory as c
where (c.ParentCategoryID is null)
union all
select
c.ID,
c.CategoryName,
r.ParentDisplayOrder + (Cast(c.DisplayOrder as float) / Power(10, r.LevelNo)) as ParentDisplayOrder,
c.DisplayOrder,
c.ParentCategoryID,
r.MainID,
r.LevelNo + 1 as LevelNo
from tblCategory as c
join CteRec as r on r.ID = c.ParentCategoryID
where r.MainID = @ID
)
select * from CteRec
order by ParentDisplayOrder
with CteRec as
(
select
c.ID,
c.CategoryName,
Cast(c.DisplayOrder as float) as ParentDisplayOrder,
0 as DisplayOrder,
c.ParentCategoryID,
c.ID as MainID,
1 as LevelNo
from tblCategory as c
where (c.ParentCategoryID is null)
union all
select
c.ID,
c.CategoryName,
r.ParentDisplayOrder + (Cast(c.DisplayOrder as float) / Power(10, r.LevelNo)) as ParentDisplayOrder,
c.DisplayOrder,
c.ParentCategoryID,
r.MainID,
r.LevelNo + 1 as LevelNo
from tblCategory as c
join CteRec as r on r.ID = c.ParentCategoryID
where r.MainID = @ID and r.LevelNo < @LevelNo
)
select * from CteRec
order by ParentDisplayOrder
JanAntwerpen
Member
42 Points
27 Posts
selecting my categories
Apr 06, 2012 12:46 AM|LINK
Hello fellow programmers!
I have a question regarding displaying categories. A category can have a subcategory. The user can indicate a display order to the categories. The table looks like this:
tblCategory
---------------
ID CategoryName DisplayOrder ParentCategoryID
--------------------------------------------------------------------------------
1 Beverages 1
2 Non-Beverages 2
3 Fruits 1 2
4 Vegetables 2 2
5 Candy 3
6 Big Candy 1 5
7 Small Candy 2 5
In this example fruits and vegetables are subcategories of non-beverages. Big and small candy are subcategories of candy.
I am using a listview which initially displays all categories which havent got a ParentCategoryID. If the user clicks on a category which has subcategories, I want the listview to show all categories which havent got a ParentCategoryID as well as all the children for the clicked category. (I can handle this with a simple itemcommand event on the listview).
I do NOT know how I can get the list with items 1 to 5 if I click item 2 (non-beverages).
The list I want is the following one: (Notice, the subcategories of candy are not displayed!)
Beverages
Non-Beverages
Fruits
Vegetables
Candy
Any help is welcome. I do also want the ID because I need those values when clicked.
silvioyf
Participant
1754 Points
340 Posts
Re: selecting my categories
Apr 06, 2012 01:48 AM|LINK
Hi,
I do not know if I understand correctly, but try:
with CteRec as ( select c.ID, c.CategoryName, c.DisplayOrder as ParentDisplayOrder, 0 as DisplayOrder, c.ParentCategoryID from @tblCategory as c where c.ParentCategoryID is null union all select c.ID, c.CategoryName, r.ParentDisplayOrder as ParentDisplayOrder, c.DisplayOrder, c.ParentCategoryID from @tblCategory as c join CteRec as r on r.ID = c.ParentCategoryID where c.ParentCategoryID = 2 ) select * from CteRec order by ParentDisplayOrder, DisplayOrderI hope it's useful.
JanAntwerpen
Member
42 Points
27 Posts
Re: selecting my categories
Apr 06, 2012 02:09 AM|LINK
Thank you Silvioyf!
(I let out 'where c.ParentCategoryID = 2' because ParentCategoryID is a uniqueIdentifier and wouldnt work like this)
This is great! But it works partially:
Is it possible to only expand only the 'children' of the categorie indicated in a parameter?
Is it possible to have it working with a number of subcategories? I mean that the subcategory has subcategories, that subcategory of a subcategory can have subcategories as well,... (without knowing how many subcategories there are?)
silvioyf
Participant
1754 Points
340 Posts
Re: selecting my categories
Apr 06, 2012 03:02 AM|LINK
Try:
with CteRec as ( select c.ID, c.CategoryName, Cast(c.DisplayOrder as float) as ParentDisplayOrder, 0 as DisplayOrder, c.ParentCategoryID, c.ID as MainID, 1 as LevelNo from tblCategory as c where (c.ParentCategoryID is null) union all select c.ID, c.CategoryName, r.ParentDisplayOrder + (Cast(c.DisplayOrder as float) / Power(10, r.LevelNo)) as ParentDisplayOrder, c.DisplayOrder, c.ParentCategoryID, r.MainID, r.LevelNo + 1 as LevelNo from tblCategory as c join CteRec as r on r.ID = c.ParentCategoryID where r.MainID = @ID ) select * from CteRec order by ParentDisplayOrderI hope this helps.
JanAntwerpen
Member
42 Points
27 Posts
Re: selecting my categories
Apr 06, 2012 12:38 PM|LINK
Thank you Silvioyf,
It is already a lot better. Unfortunately, this still doesn't do all I want:
The line 'where r.MainID = @ID' is not working as it is not declared. (I'm not able to declare the param right.)
This solution works if the MainID is a sublevel (1.1) not a deeper level (1.1.1).
Can you fix both these problems?
silvioyf
Participant
1754 Points
340 Posts
Re: selecting my categories
Apr 06, 2012 06:30 PM|LINK
Try:
with CteRec as ( select c.ID, c.CategoryName, Cast(c.DisplayOrder as float) as ParentDisplayOrder, 0 as DisplayOrder, c.ParentCategoryID, c.ID as MainID, 1 as LevelNo from tblCategory as c where (c.ParentCategoryID is null) union all select c.ID, c.CategoryName, r.ParentDisplayOrder + (Cast(c.DisplayOrder as float) / Power(10, r.LevelNo)) as ParentDisplayOrder, c.DisplayOrder, c.ParentCategoryID, r.MainID, r.LevelNo + 1 as LevelNo from tblCategory as c join CteRec as r on r.ID = c.ParentCategoryID where r.MainID = @ID and r.LevelNo < @LevelNo ) select * from CteRec order by ParentDisplayOrderex: @ID = 2 (Non-Beverages) and @LevelNo = 3
I hope this helps.