SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2007
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Guru needed for MPTT query

    I'm trying to use the modified preorder tree traversal method as layed out in the excellent sitepoint article to retrieve a category tree with a count of items below each category.

    So far I've come up with this query which is so very nearly there but only returns rows for categories that contain an item.

    Code:
    SELECT root.name, 
               root.category_id, 
               COUNT( items.item_id ) AS item_count 
               FROM categories AS current, categories AS root, items 
               WHERE root.category_id != 1
                       AND ( current.lft BETWEEN root.lft AND root.rgt )
                       AND ( current.category_id = category_id ) 
               GROUP BY root.category_id 
               ORDER BY current.lft ASC
    Can anybody help?

    Many thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    two questions...
    Code:
    SELECT root.name
         , root.category_id
         , COUNT( items.item_id ) AS item_count 
      FROM categories AS current
         , categories AS root
         , items 
     WHERE root.category_id != 1
       AND ( current.lft BETWEEN root.lft AND root.rgt )
       AND ( current.category_id = category_id ) 
    GROUP 
        BY root.category_id 
    ORDER 
        BY current.lft ASC
    first, which table does the column marked in red belong to? (i'm guessing item)

    second, why isn't the ORDER BY column in the SELECT clause? i'd be surprised if you actually got the sorting to work correctly
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2007
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    first, which table does the column marked in red belong to? (i'm guessing item)
    hi r937. Apologies, yes you're right the column in red should be items.category_id

    I edited the query slightly shortening some category names etc for clarity and accidentally erased that.
    second, why isn't the ORDER BY column in the SELECT clause? i'd be surprised if you actually got the sorting to work correctly
    Ah I hadn't realised that and you are right the sorting isn't in fact working it was just a lucky coincidence they were sorted in the right order probably due to the order the data was inserted.

    That query is actually an attempt by somebody else after I presented my initial attempt of this monstrosity to them...

    Code:
    SELECT
        ips_classifieds_categories.category_id
        , ips_classifieds_categories.name
        , COUNT(ips_classifieds_items.item_id)
    FROM
        millnedev.ips_classifieds_categories
        LEFT JOIN millnedev.ips_classifieds_items 
            ON (ips_classifieds_categories.category_id = ips_classifieds_items.category_id)
        LEFT JOIN millnedev.ips_classifieds_categories AS ips_classifieds_categories_1
            ON (ips_classifieds_items.category_id = ips_classifieds_categories_1.category_id)
    WHERE (ips_classifieds_categories_1.lft >= ips_classifieds_categories.lft
        AND ips_classifieds_categories_1.rgt <= ips_classifieds_categories.rgt)
    GROUP BY ips_classifieds_categories.category_id
    ORDER BY ips_classifieds_categories.lft ASC;
    I've added the ORDER BY column into the SELECT clause and the sorting now appears to be working.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    here you go...
    Code:
    SELECT root.name
         , root.category_id
         , root.lft
         , COUNT( items.item_id ) AS item_count 
      FROM millnedev.ips_classifieds_categories AS root
    INNER
      JOIN millnedev.ips_classifieds_categories AS current
        ON current.lft BETWEEN root.lft AND root.rgt
    LEFT OUTER
      JOIN millnedev.ips_classifieds_items AS items 
        ON items.category_id = current.category_id
     WHERE root.category_id <> 1
    GROUP 
        BY root.category_id 
    ORDER 
        BY root.lft ASC
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2007
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem is obviously with...

    Code:
    AND ( current.category_id = items.item_category_id )
    as if there is no item in the category then this can't work but I really can't figure out how to restructure the query.

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2007
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much I'll give that a try now.

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2007
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Brilliant thank you very much! this forum should have a reputation feature.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Andrew Millne View Post
    this forum should have a reputation feature.
    it's been tried, and it only gets abused

    but thanks for the kind words

    and to think i haven't got a clue about the nested set model!!
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Apr 2007
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there any way to limit the results to n levels deep?

    If there's no way to limit it from the query directly maybe I could add a "level" field when inserting the catgegories parent_id.level + 1?

    Would add complication when moving categories around then though.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i hope that question wasn't directed at me, because i don't do the nested set model (lft,rgt)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Apr 2007
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    not necessarily no

    Is there any particular reason I should aware of why you don't do the nested set model? Am I going up a blind alley here?

    In any case I found this which describes using nested set to get a specific level deep so I should be able to work off that.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Andrew Millne View Post
    Is there any particular reason I should aware of why you don't do the nested set model?
    just personal preference: the nested set model is ~way~ too complicated for me

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Apr 2007
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I got the level depth working with the following...

    I've had to enter the roots lft and rgt values manually but will do this programatically.

    Code:
    SELECT root.name, root.category_id, root.lft, COUNT( items.item_id ) AS item_count 
    FROM ips_classifieds_categories root,ips_classifieds_categories current 
    LEFT OUTER JOIN ips_classifieds_items items ON ( items.category_id = current.category_id ) 
    WHERE root.parent_id <> 0 
    AND ( SELECT COUNT(*) FROM ips_classifieds_categories depth WHERE current.lft BETWEEN depth.lft AND depth.rgt AND depth.lft BETWEEN 1 AND 18 ) <= 3
    AND current.lft BETWEEN root.lft AND root.rgt 
    GROUP BY root.category_id 
    ORDER BY root.lft ASC
    But I've now lost the item count I can see why r937 avoids nested sets now.

  14. #14
    SitePoint Zealot
    Join Date
    Apr 2007
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Improved the query slightly so I can now also limit by depth...

    Code:
    SELECT node.name, node.category_id, node.lft, node.rgt, node.parent_id, (COUNT(parent.category_id) - (sub_tree.depth + 1)) AS depth
    FROM millnedev.ips_classifieds_categories AS node,
    	millnedev.ips_classifieds_categories AS parent,
    	millnedev.ips_classifieds_categories AS sub_parent,
    	(
    		SELECT node.category_id, (COUNT(parent.category_id) - 1) AS depth
    		FROM millnedev.ips_classifieds_categories AS node,
    		millnedev.ips_classifieds_categories AS parent
    		WHERE node.lft BETWEEN parent.lft AND parent.rgt
    		AND node.category_id = '1'
    		GROUP BY node.category_id
    		ORDER BY node.lft
    	) AS sub_tree
    
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    	AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    	AND sub_parent.category_id = sub_tree.category_id
    GROUP BY node.category_id
    HAVING depth BETWEEN 1 AND 2
    ORDER BY node.lft;
    But now need to work out how to factor back in the join to get the item count also.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •