MySQL Forums
Forum List  »  PHP

Multi Level Sorting - Queries Question
Posted by: Paul Mansell
Date: January 28, 2011 04:35PM

Hi,

I have a multi level set of data that actually outputs as a tree :

-Page1
-Page2
---Page3
---Page4
-----Page5
-Page6

I have the tree working fine - but it requires a SELECT statement for each entry to check for parents - to create the tree I create and Array in PHP then json_encode this and construct the tree from JSON. That bit kind of works OK - but it would be nice to perhaps reduce the number of queries. Is this possible with a single query ?



Here is my code for doing the tree :

public static function getSiteTree(){
return self::getPages(0);
}

public static function getPages($where = ''){
$q = Doctrine_Query::create()
->from('siteContent p')
->where('p.parentid = ?', $where)
->orderBy('sort');

$results = $q->execute();

$categories = array();
foreach ($results as $category) {
$my_id = $category['id'];
$newcategory = array();
$newcategory['attr'] = array('id' => 'node_' . $my_id, 'rel' => 'folder');
$newcategory['data'] = array('title' => $category['title'], 'href' => '/admin/getID/' . $my_id);
$newcategory['children'] = self::getPages($my_id);
$categories[] = $newcategory;
}
return $categories;
}

Secondly the tree can be updated using javascript - all working again no problem - when the tree is updated a PHP function is called - passing the ID of the record updated - its new parent and its new sort position. I have a method that updates the record in the database as well as any records inbetween - also updating the parent ID if its moved levels. However I dont know how to update the old levels sort if its moved levels. Here is my function to resort the tree :

UPDATE `website`.`siteContent` SET sort = CASE WHEN sort = @oldIndex THEN @newIndex ELSE IF(@newIndex > @oldIndex, sort - 1, sort + 1) END WHERE sort BETWEEN LEAST(@newIndex, @oldIndex) AND GREATEST(@newIndex, @oldIndex) AND parentID = @newParentID

To give an example of what happens ->

---------------------
|id | sort |parent|
---------------------
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
---------------------

If I update id 2 to be a child of id 1 - i end up with the following table :

---------------------
|id | sort |parent|
---------------------
| 1 | 1 | 0 |
| 2 | 2 | 1 |
| 3 | 3 | 0 |
---------------------

You can see that the sort order for level 0 (parent 0) reads 1..3... and that causes me problems - whats the easiest way to ensure that there is a consistency and sequential ordering in each level ?

Can anyone help me ?

Options: ReplyQuote


Subject
Written By
Posted
Multi Level Sorting - Queries Question
January 28, 2011 04:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.