Three levels deep


  • Trolleybus Mechanic

    "Make sure to restrict them to only making folders three levels deep"

    Me: "Why?"

    "Because other wise you'll have to write too many LEFT OUTER JOINS"

    {weep}



  • You can tell that person he is an idiot.

    Because the OUTER keyword is optional when specifying LEFT or RIGHT.



  • Well, in all honesty, manipulating n-level deep hierarchies can be a royal pain, especially if you're working with MySQL. Still, it's always fun to hear about bosses/clients who pick up a term somewhere and decide they understand it.


  • Garbage Person

    @veggen said:

    Well, in all honesty, manipulating n-level deep hierarchies *can* be a royal pain, especially if you're working with MySQL.
    Hell, AFAIK it's a royal pain in SQL Server. I did it once. It was a NIGHTMARE.



  • Working with Oracle, I ♥ CONNECT BY PRIOR and friends.


  • Trolleybus Mechanic

    @veggen said:

    Well, in all honesty, manipulating n-level deep hierarchies *can* be a royal pain, especially if you're working with MySQL. Still, it's always fun to hear about bosses/clients who pick up a term somewhere and decide they understand it.
     

    I can be... but it also can very easily not be. There's a very nice "nested dolls" design that only requires two extra int fields, a single easy to grok join, and *BAM* entire heirachy, in order, with depth. My tutorial link seems to be dead, but there's a good [url="http://en.wikipedia.org/wiki/Nested_set_model"]Source of All Knowledge (citation needed)[/url] article.

    I fully acknowledge it's not as simple as id, parent_id, sort on the surface, but it's way fucking easier than:

    @This is wrong, but you get the fucking point for shitfucks's sake! said:


    SELECT *
    FROM
    (
     SELECT *, sort_order as parent_sort_order, null as child_sort_order, null as grandchild_sort_order FROM nodes where depth = 1
     UNION
     SELECT d2.*, d1.sort_order as parent_sort_order, d2.sort_order as child_sort_order, null as grandchild_sort_order FROM nodes d1 INNER JOIN nodes d2 on    d1.id = d2.parent_id
     UNION
     SELECT d3.*, d1.sort_order as parent_sort_order, d2.sort_order as child_sort_order d3.sort_order as grandchild_sort_order FROM nodes d1 INNER JOIN   nodes d2 on d1.id = d2.parent_id AND d2.id = d3.parent_id
    ) d
    ORDER BY parent_sort_order, child_sort_order, grandchild_sort_order


     And if any query is sufficently complex, you can always put it in DataLayer.GetTheFuckingMenu() : DataTable

    Oh, wait-- we don't "believe" in data layers. They're too academic.

    Edit: I guess the thing that pisses me off the most is that it's presented as "the only way" to do it, tried and true without any problems whatsoever-- except for all the problems we've had with it-- and that all other methods are too risky and can't even be concidered. There's nothing* more frustrating than having every design/solution dismissed out of hand because "I don't get it". There's a big difference between "too complex to understand" and "too complex for YOU to understand in 30 seconds".

    *except for hyperboles. Those are very frustrating.



  • @Weng said:

    Hell, AFAIK it's a royal pain in SQL Server. I did it once. It was a NIGHTMARE.

    Did this not long ago.

    CREATE VIEW [dbo].[V_FooHierarchyAscending]
    AS
    	-- The query optimiser should be able to do a good job with this if you join against DescendantId
    	WITH FooHierarchy AS
    	(
    		SELECT
    			Foo.Id AS AncestorId,
    			Foo.Id AS DescendantId,
    			0 AS Separation
    		FROM Foo
    
    		UNION ALL
    
    		SELECT
    			Foo.ParentId AS AncestorId,
    			H.DescendantId,
    			H.Separation + 1
    		FROM FooHierarchy H
    			INNER JOIN Foo ON Foo.Id = H.AncestorId
    	)
    	SELECT * FROM FooHierarchy
    GO

    Plus a similar view V_FooHierarchyDescending for joins against AncestorId.

    Fairly straightforward, but not efficient because SQL Server doesn't like materialising views with UNION ALL. OTOH if the hierarchy is only four or five levels deep it shouldn't be a major performance problem.

    @Lorne Kates said:

    It can be... but it also can very easily not be. There's a very nice "nested dolls" design that only requires two extra int fields, a single easy to grok join, and BAM entire hierarchy, in order, with depth.

    And some more complicated logic for inserts and removals. Then there's SQL Server's special datatype for hierarchies which basically looks like a path from the root (and no doubt other DBMSs have similar, but I don't know them), and a couple of other interesting techniques. Plenty of scope for performance / complexity of query / complexity of modification tradeoffs.


  • Garbage Person

    @pjt33 said:

    Plenty of scope for performance / complexity of query / complexity of modification tradeoffs.
    These tradeoffs are great fun in the world of "This query will run 90000000 times a day and the heirachy is hundreds of levels deep and will be maintained by untrained lowest-bidder idiots from Bangalore who have never seen a select statement before andwe CERTAINLY aren't paying you to document it."



  • @Lorne Kates said:

    I can be... but it also can very easily not be. There's a very nice "nested dolls" design that only requires two extra int fields, a single easy to grok join, and BAM entire heirachy, in order, with depth. My tutorial link seems to be dead, but there's a good Source of All Knowledge (citation needed) article.

    That's what I use (in MySQL), which I got out of a book, which I then simplified and actually made work (book examples kept the unneeded "parent_id" field, and it grouped on the name and not the id, and there were other things wrong that I don't remember any more). One such tree had 20,000 nodes in it, which was handled without any problems (except the DBA initially not putting ANY indexes (other than PK) on the table which caused queries to take too long - adding indexes to the left_side and right_side columns sped it up nicely) (IMSMR).



  • @pjt33 said:

    @Weng said:
    Hell, AFAIK it's a royal pain in SQL Server. I did it once. It was a NIGHTMARE.
    Did this not long ago.
    CREATE VIEW [dbo].[V_FooHierarchyAscending]
    AS
    	-- The query optimiser should be able to do a good job with this if you join against DescendantId
    	WITH FooHierarchy AS
    	(
    		SELECT
    			Foo.Id AS AncestorId,
    			Foo.Id AS DescendantId,
    			0 AS Separation
    		FROM Foo
    
    	UNION ALL
    
    	SELECT
    		Foo.ParentId AS AncestorId,
    		H.DescendantId,
    		H.Separation + 1
    	FROM FooHierarchy H
    		INNER JOIN Foo ON Foo.Id = H.AncestorId
    )
    SELECT * FROM FooHierarchy
    

    GO

    This is great. But can you do the same thing for Folders instead of Database Tables?!?



  • @Weng said:

    @pjt33 said:

    Plenty of scope for performance / complexity of query / complexity of modification tradeoffs.
    These tradeoffs are great fun in the world of "This query will run 90000000 times a day and the heirachy is hundreds of levels deep and will be maintained by untrained lowest-bidder idiots from Bangalore who have never seen a select statement before andwe CERTAINLY aren't paying you to document it."


     

    They will mess with anything that you write. Documented or not, complex or not. And it will be slow, it will deadlock (don't mind that it doesn't depend on anything), and will raise a huge number of exceptions.

     



  • @Lorne Kates said:

    My tutorial link seems to be dead, but there's a good Source of All Knowledge (citation needed) article.

    If you re-find the original tutorial, would you repost so as to share with us all? kthxbai



  •  @zelmak said:

    @Lorne Kates said:

    My tutorial link seems to be dead, but there's a good Source of All Knowledge (citation needed) article.

    If you re-find the original tutorial, would you repost so as to share with us all? kthxbai

    http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ is pretty good.


  • Trolleybus Mechanic

    @Khazwossname said:

     @zelmak said:

    @Lorne Kates said:

    My tutorial link seems to be dead, but there's a good Source of All Knowledge (citation needed) article.

    If you re-find the original tutorial, would you repost so as to share with us all? kthxbai

    http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ is pretty good.

     

    That's the one. Bookmark updated. Thanks!



  • @pjt33 said:

    CREATE VIEW [dbo].[V_FooHierarchyAscending]

    I like this. It indeed looks like a plain and simple way to build the transitive closure of a parent/child relation(ship).

    On second thought, joining FooHierarchy with itself instead of Foo might be worthwhile:

    ...
    WITH FooHierarchy AS
    (
    	SELECT
    		Foo.ParentId AS AncestorId,
    		Foo.Id AS DescendantId,
    		1 AS Separation
    	FROM Foo
    	UNION ALL
    	SELECT
    		H1.AncestorId,
    		H2.DescendantId,
    		H1.Separation + H2.Separation
    	FROM FooHierarchy H1 INNER JOIN FooHierarchy H2
    		ON H1.DescendantId = H2.AncestorId
    )
    ...

    (Disclaimer: No, this is not reflexive. And no, I do not have SQL Server, so I did not test the query.)

    @pjt33 said:

    Plus a similar view V_FooHierarchyDescending for joins against AncestorId.

    Why? Both views are the same, aren't they? ;-)



  • @fatbull said:

    On second thought, joining FooHierarchy with itself instead of Foo might be worthwhile:

    I don't have SQL Server at home, but IIRC that would cause some duplicate rows. I did experiment with a few variations before settling on this one.

    @fatbull said:

    @pjt33 said:
    Plus a similar view V_FooHierarchyDescending for joins against AncestorId.
    Why? Both views are the same, aren't they? ;-)

    Absolutely. Only wimps worry about performance ;)



  • So no recursive function call to bring back the entire tree then?


  • Trolleybus Mechanic

    @CrisW said:

    So no recursive function call to bring back the entire tree then?
     

    Recursion is too academic, and isn't used in the real world.


  • ♿ (Parody)

    @Lorne Kates said:

    @CrisW said:

    So no recursive function call to bring back the entire tree then?
     

    Recursion is too academic, and isn't used in the real world.

    Definately. If your concerned about security, you should be using frames.



  • @Lorne Kates said:

    @CrisW said:

    So no recursive function call to bring back the entire tree then?
     

    Recursion is too academic, and isn't used in the real world.

    Really?  Are you sure?  Because this tree isn't going to traverse itself.

  • Trolleybus Mechanic

    @frits said:

    @Lorne Kates said:

    @CrisW said:

    So no recursive function call to bring back the entire tree then?
     

    Recursion is too academic, and isn't used in the real world.

    Really?  Are you sure?  Because this tree isn't going to traverse itself.
     

    It will if you use recursion.



  • @frits said:

    @Lorne Kates said:

    Recursion is too academic, and isn't used in the real world.

    Really?  Are you sure?  Because this tree isn't going to traverse itself.
    You have been trolled. You have lost.

     



  • @Zylon said:

    @frits said:

    @Lorne Kates said:

    Recursion is too academic, and isn't used in the real world.

    Really?  Are you sure?  Because this tree isn't going to traverse itself.
    You have been trolled. You have lost.

     

    I think you may have missed something. 

    What's with you and pointing out trolling anyway?  It's very odd behavior.  Every post you make is either "<blank> is a troll" or "ZOMG you mispelled something!" 

    Trolling exists and I don't even care if I respond to it.



  • @frits said:


    Trolling exists and I don't even care if I respond to it.

     

    That's because you're an idiot. But we already knew that.

     



  • @Zylon said:

    @frits said:


    Trolling exists and I don't even care if I respond to it.

     

    That's because you're an idiot. But we already knew that.

    Not everyone can be as smart as you are--not that you've ever actually demonstrated intelligence here. 

    Why don't you try actually contributing something other than petty snipes you hateful bastard?



  • @frits said:

    @Zylon said:

    @frits said:


    Trolling exists and I don't even care if I respond to it.

     

    That's because you're an idiot. But we already knew that.

    Not everyone can be as smart as you are--not that you've ever actually demonstrated intelligence here. 

    Why don't you try actually contributing something other than the usual posts here you common thedailywft forum user?

    FTFY



  • @Zylon said:

    @frits said:
    Trolling exists and I don't even care if I respond to it.
    That's because you're an idiot. But we already knew that.

    Fascinating. But have you accounted for the inherent metaphysical existentialism effected by the technologies of abstract communication?

    And how would you respond to criticism of your argument that it lacks falsifiability?



  • @serguey123 said:

    @frits said:


    Why don't you try actually contributing something other than the usual posts here you common thedailywft forum user?

    FTFY

    Wha??



  • @Zylon said:

    @frits said:
    Trolling exists and I don't even care if I respond to it.
     

    That's because you're an idiot. But we already knew that.

    I know you!  You're that angry little fat kid that everybody thought was a reclusive nerd, only to discover that you're really dumb as shit.

    A rare breed, you are...  Retarded, anti-social (even amongst fellow nerds).  Your life must be a beacon of happiness...



  • @frits said:

    @serguey123 said:

    @frits said:


    Why don't you try actually contributing something other than the usual posts here you common thedailywft forum user?

    FTFY

    Wha??

    Image is broken.


  • Weird.  It came back.  I tried to game twitter.  I wanted to use them to host the pic but I didn't neccessarily want you guys to easily find my account so I deleted the tweet.  Twitter probably saw I deleted the tweet and deleted the file.  Maybe it's back because they saw the links here.  Or something.


Log in to reply