Column in select statement as an array

[Anzeo]

okay, not sure if this is even possible, but thought I might as well ask you guys.

 

I store my menu in my database, in one table MENUITEM. I have two levels, so there are parent and child items. Parent items have a ParentID of 0, while child's have an existing MENUITEM.ID as their ParentID.

 

However, the problem lies in fetching all information in one query. By this I mean I'd like to fetch the parent items together with their child items in a single query. Currently I've devised this query:

'SELECT M1.Name AS ParentName,M2.Name AS ChildName
FROM MENUITEM AS M1 LEFT JOIN MENUITEM AS M2 ON M1.ID = M2.ParentID
WHERE M1.ParentID=0 ORDER BY M1.OrderNr'

 

When outputting the result, I get:

-PARENT_1
-PARENT_1_CHILD_1
-PARENT_1
-PARENT_1_CHILD_2

 

However, I would like to get:

-PARENT_1
-PARENT_1_CHILD_1
-PARENT_1_CHILD_2

 

I cannot use a concat on a GROUP BY, as I need the separate records of the childs (with all their information). So I would like to have per parent record an array as column, containing all childs of that parent.

 

Long explanation, short question: is this possible ?

 

 

 

Thanks in advance!

[fenway]

And what does the source data look like?

[Anzeo]

The structure of the table is as follows:

ID int(11)
Name varchar(45)
Link varchar(45)
IsActive 	tinyint(1) 			
OrderNr int(11) 			
ParentID int(11) 				

 

So parent nodes have a ParentID equal to 0, while child nodes will have the ID of a parent node as their ParentID.

[fenway]

Then how can you ever have the parent node come back twice?

[Anzeo]

Then how can you ever have the parent node come back twice?

 

Well, that is my question

[fenway]

That leads me to believe that it's in your table twice -- or that you're not excluding the same ID from both tables.

[ajlisowski]

SELECT `name`, IF(`ParentID`=0,`ID`,`ParentID`) AS `SortBy` ORDER BY `SortBy`, `ParentID`

 

Parent was showing up twice because you joined. We didnt need to join. We simply want to sort by the parent ID, or the ID if the parent itself.

 

This will likely not work as smoothly if you have multiple layers Grandparent->Parent->Child but it should work for a single layer, Parent->Child.

[Anzeo]

SELECT `name`, IF(`ParentID`=0,`ID`,`ParentID`) AS `SortBy` ORDER BY `SortBy`, `ParentID`

 

Parent was showing up twice because you joined. We didnt need to join. We simply want to sort by the parent ID, or the ID if the parent itself.

 

This will likely not work as smoothly if you have multiple layers Grandparent->Parent->Child but it should work for a single layer, Parent->Child.

This allows (with some changes to your example) to order parents and childs correctly. However, my initial question was wether it would be possible to obtain a resultset (from one query!) like this:

array(
0 =>
array(
'Name' => 'parent_name',
'Children' => array(
0 => array('Name' => 'child1_name',...),
1 => array('Name' => 'child2_name',...),
...
),
...
),
...)

 

Thus selecting an array of results as a column of another (parent) result?