Sorting results with child under parent results

[prw]

I've searched around and cannot find a solution that works perfectly for this.

 

My MySQL structure:

 

idchildchild_idpage_name

100Parent 1

215Child 1

311Child 2

415Child 3

500Parent 2

 

Desired order of results:

 

Parent 1

- Child 2

Parent 2

- Child 1

- Child 3

 

My MySQL query at the moment, which of course doesn't order the results as I desire:

 

SELECT * FROM pages GROUP BY id ORDER BY FIELD(child, '1', '0') DESC, id DESC

 

How would I go about ordering them correctly?

[requinix]

I'll assume that there's only two levels in your hierarchy: parent and child. (Because if there were more then you query wouldn't be valid.)

 

Use a JOIN to get columns with the parent and child info.

SELECT
    parent.id AS parentID, parent.page_name AS parentName,
    child.id AS childID, child.page_name AS childName
FROM pages AS child
JOIN pages AS parent ON child.child_id = parent.id
ORDER BY parent.page_name ASC, child.page_name ASC

parentID | parentName | childID | childName
---------+------------+---------+----------
       1 | Parent 1   |       3 | Child 2
       5 | Parent 2   |       2 | Child 1
       5 | Parent 2   |       4 | Child 3

[prw]

You are correct to assume there's only two levels. So a child cannot be within another child.

 

Your query doesn't seem to work how I want the rows to show though. I only see Parent 1, and Parent 2 twice whereas I want to see the Childs below the parents with no duplicate records.

[requinix]

Adjacency list doesn't do that task very well. It'll be easier to modify your code to look at the parent stuff before the child stuff, like

$parent = "';
for each $row in the resultset {
if $row[parent] != $parent then {
	display the parent
	$parent = $row[parent]
}
display the child
}

 

[prw]

Ah no, I don't wish the results to be a adjacent. I guess my dash put you off a bit there, sorry. Just put that they to make them seem separate from the parent.

 

Just want it listed as a normal vertical row, like so:

 

http://cl.ly/6R5g

[requinix]

"Adjacency list" is a database model where (for example) every child in a hierarchy knows who its parent is. There are other models; "nested sets" in particular can easily give you the exact resultset you want, but while you gain in some areas you lose in others - it's a trade-off.

What I was saying is that with that child.child_id = parent.id structure it's not easy to get one list of the parents and children sorted in the right order.

 

What is easier (than constructing a complex query) is to slightly restructure your code so that it prints just the children, and then modify it a bit more to print the parents just before their first children. Just like the pseudocode I posted.

If you're still not sure, post your actual PHP and I'll adjust it for you.

[prw]

Managed to find a solution that works perfectly:

http://stackoverflow.com/questions/4259225/selecting-records-in-order-of-parent-id

 

SELECT Concat(If(isnull(p2.page_name),"",Concat("/",p2.page_name)),"/",p1.page_name) AS `generated path`, p2.page_name AS parent, p1.*
FROM pages p1
LEFT JOIN pages p2 ON p1.child_id = p2.id
ORDER BY `generated path`

 

Thanks for the help.