R – How to prevent tree items from being dragged into themselves

apache-flexdrag and droptree

I have a flex tree with dragMoveEnabled = true. I want to find out, when an item is dragged into itself or it's children. I'd like to use DragManager.showFeedback(DragManager.NONE) in the tree's onDragOver handler, but can't find out how get this to work. I'm using an ArrayCollection of nested objects as dataSource for the tree.

Best Answer

    private function onDragOver(event:DragEvent):void {
        event.preventDefault();
        event.currentTarget.hideDropFeedback(event);                

        var index:int = tree.calculateDropIndex(event);
        tree.selectedIndex = index;
        var subCategory:CategoryVO = CategoryVO(tree.selectedItem);

        var currentCategory:CategoryVO = subCategory;

        while(currentCategory.parent != 0) {
        if (currentCategory.parent == _draggedItem.id) {
            DragManager.showFeedback(DragManager.NONE);
            return;
        }
        currentCategory = tree.getParentItem(currentCategory);

        if (currentCategory == null) {
            break;
        }

        DragManager.showFeedback(DragManager.MOVE);
        tree.showDropFeedback(event);
   }

That's how I solved it.

_draggedItem holds the currently dragged item, set in the tree's onDragEnter handler. CategoryVO is the value object I use.

Related Solutions

Sql – the most efficient/elegant way to parse a flat table into a tree

Now that MySQL 8.0 supports recursive queries, we can say that all popular SQL databases support recursive queries in standard syntax.

WITH RECURSIVE MyTree AS (
    SELECT * FROM MyTable WHERE ParentId IS NULL
    UNION ALL
    SELECT m.* FROM MyTABLE AS m JOIN MyTree AS t ON m.ParentId = t.Id
)
SELECT * FROM MyTree;

I tested recursive queries in MySQL 8.0 in my presentation Recursive Query Throwdown in 2017.

Below is my original answer from 2008:

There are several ways to store tree-structured data in a relational database. What you show in your example uses two methods:

Adjacency List (the "parent" column) and
Path Enumeration (the dotted-numbers in your name column).

Another solution is called Nested Sets, and it can be stored in the same table too. Read "Trees and Hierarchies in SQL for Smarties" by Joe Celko for a lot more information on these designs.

I usually prefer a design called Closure Table (aka "Adjacency Relation") for storing tree-structured data. It requires another table, but then querying trees is pretty easy.

I cover Closure Table in my presentation Models for Hierarchical Data with SQL and PHP and in my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

CREATE TABLE ClosureTable (
  ancestor_id   INT NOT NULL REFERENCES FlatTable(id),
  descendant_id INT NOT NULL REFERENCES FlatTable(id),
  PRIMARY KEY (ancestor_id, descendant_id)
);

Store all paths in the Closure Table, where there is a direct ancestry from one node to another. Include a row for each node to reference itself. For example, using the data set you showed in your question:

INSERT INTO ClosureTable (ancestor_id, descendant_id) VALUES
  (1,1), (1,2), (1,4), (1,6),
  (2,2), (2,4),
  (3,3), (3,5),
  (4,4),
  (5,5),
  (6,6);

Now you can get a tree starting at node 1 like this:

SELECT f.* 
FROM FlatTable f 
  JOIN ClosureTable a ON (f.id = a.descendant_id)
WHERE a.ancestor_id = 1;

The output (in MySQL client) looks like the following:

+----+
| id |
+----+
|  1 | 
|  2 | 
|  4 | 
|  6 | 
+----+

In other words, nodes 3 and 5 are excluded, because they're part of a separate hierarchy, not descending from node 1.

Re: comment from e-satis about immediate children (or immediate parent). You can add a "path_length" column to the ClosureTable to make it easier to query specifically for an immediate child or parent (or any other distance).

INSERT INTO ClosureTable (ancestor_id, descendant_id, path_length) VALUES
  (1,1,0), (1,2,1), (1,4,2), (1,6,1),
  (2,2,0), (2,4,1),
  (3,3,0), (3,5,1),
  (4,4,0),
  (5,5,0),
  (6,6,0);

Then you can add a term in your search for querying the immediate children of a given node. These are descendants whose path_length is 1.

SELECT f.* 
FROM FlatTable f 
  JOIN ClosureTable a ON (f.id = a.descendant_id)
WHERE a.ancestor_id = 1
  AND path_length = 1;

+----+
| id |
+----+
|  2 | 
|  6 | 
+----+

Re comment from @ashraf: "How about sorting the whole tree [by name]?"

Here's an example query to return all nodes that are descendants of node 1, join them to the FlatTable that contains other node attributes such as name, and sort by the name.

SELECT f.name
FROM FlatTable f 
JOIN ClosureTable a ON (f.id = a.descendant_id)
WHERE a.ancestor_id = 1
ORDER BY f.name;

Re comment from @Nate:

SELECT f.name, GROUP_CONCAT(b.ancestor_id order by b.path_length desc) AS breadcrumbs
FROM FlatTable f 
JOIN ClosureTable a ON (f.id = a.descendant_id) 
JOIN ClosureTable b ON (b.descendant_id = a.descendant_id) 
WHERE a.ancestor_id = 1 
GROUP BY a.descendant_id 
ORDER BY f.name

+------------+-------------+
| name       | breadcrumbs |
+------------+-------------+
| Node 1     | 1           |
| Node 1.1   | 1,2         |
| Node 1.1.1 | 1,2,4       |
| Node 1.2   | 1,6         |
+------------+-------------+

A user suggested an edit today. SO moderators approved the edit, but I am reversing it.

The edit suggested that the ORDER BY in the last query above should be ORDER BY b.path_length, f.name, presumably to make sure the ordering matches the hierarchy. But this doesn't work, because it would order "Node 1.1.1" after "Node 1.2".

If you want the ordering to match the hierarchy in a sensible way, that is possible, but not simply by ordering by the path length. For example, see my answer to MySQL Closure Table hierarchical database - How to pull information out in the correct order.

R – How to limit tree nodes from being dragged from their current node in flex 3

Okay, here's a pretty simple way to do this, based on the last example in this Flex Quick Starts article. This should probably be improved by using the "proper" drop indicators (the lines between items) instead of just selecting the item under the mouse when dragging.

The most relevant part is the onDragOver() method, where we restrict where items can be dropped.

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml">
    <mx:Script>
        <![CDATA[

            import mx.events.DragEvent;
            import mx.managers.DragManager;
            import mx.core.DragSource;
            import mx.core.UIComponent;
            import mx.controls.Tree;

            private var _draggedItem:XML = null;


            private function onDragEnter( event:DragEvent ) : void
            {
                event.preventDefault();
                event.currentTarget.hideDropFeedback(event);

                var ds:DragSource = event.dragSource;
                var items:Array = ds.dataForFormat("treeItems") as Array;
                if (items != null && items.length > 0 && (items[0] is XML))
                    _draggedItem = items[0];

                DragManager.acceptDragDrop(UIComponent(event.currentTarget));
            }

            private function onDragOver( event:DragEvent ) : void
            {
                event.preventDefault();
                event.currentTarget.hideDropFeedback(event);

                tree.selectedIndex = tree.calculateDropIndex(event);
                var node:XML = tree.selectedItem as XML;

                // restrict drag & drop to nodes within same parent
                if (_draggedItem.parent() != node.parent())
                {
                    DragManager.showFeedback(DragManager.NONE);
                    return;
                }

                DragManager.showFeedback(DragManager.MOVE);
            }

            private function onDragDrop( event:DragEvent ) : void
            {
                event.preventDefault();
                event.currentTarget.hideDropFeedback(event);

                tree.selectedIndex = tree.calculateDropIndex(event);
                var node:XML = tree.selectedItem as XML;

                var addToIndex:int = node.childIndex();
                if ((_draggedItem.parent() == node.parent()) && (addToIndex != _draggedItem.childIndex()))
                {
                    tree.dataDescriptor.removeChildAt(node.parent(), _draggedItem, _draggedItem.childIndex());
                    tree.dataDescriptor.addChildAt(node.parent(), _draggedItem, addToIndex);
                }
            }

            private function onDragComplete( event:DragEvent ) : void
            {
                tree.selectedIndex = -1;
            }    
        ]]>
    </mx:Script>    
    <mx:XML id="treeData" xmlns="">
        <root>
            <node label="Massachusetts" type="state" data="MA">
                <node label="Boston" type="city" >
                    <node label="Smoke House Grill" type="restaurant" />
                    <node label="Equator" type="restaurant" />
                    <node label="Aquataine" type="restaurant" />
                    <node label="Grill 23" type="restaurant" />
                </node>
                <node label="Provincetown" type="city" >
                    <node label="Lobster Pot" type="restaurant" />
                    <node label="The Mews" type="restaurant" />
                </node>
            </node>
            <node label="California" type="state" data="CA">
                <node label="San Francisco" type="city" >
                    <node label="Frog Lane" type="restaurant" />
                </node>
            </node>
        </root>
    </mx:XML>
    <mx:Tree width="100%" height="100%" id="tree"
        labelField="@label"
        dataProvider="{treeData.node}"
        allowMultipleSelection="false"
        dragEnabled="true"
        dropEnabled="true"
        dragMoveEnabled="false"
        dragEnter="onDragEnter(event)"
        dragOver="onDragOver(event)"
        dragDrop="onDragDrop(event)"
        dragComplete="onDragComplete(event)">
    </mx:Tree>        
</mx:Application>

Best Answer

Related Solutions

Sql – the most efficient/elegant way to parse a flat table into a tree

R – How to limit tree nodes from being dragged from their current node in flex 3

Related Topic