SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot dizyn's Avatar
    Join Date
    Apr 2006
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with query

    Hi

    I have products in categories and my categories are of multiple levels. I want to calculate number of products at parent category level. some of the categories have 3 levels and some have 4, its not fixed, but at least 2 levels are required. Means parent and child is required but in some cases there may be child of child and so on to up n level.

    I want to calculate number of products in all the children categories of a parent category, is it possible? if its it is please help me out.

    Tables Structure is like this:
    Product Table: productId,title,catid
    Category Table:catid,catname,parentcat

    Regards,
    -dizyn

  2. #2
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You might be able to come up with a clever SQL query to calculate all children nodes under a specific parent, but I wouldn't do it that way. I'd abstract the SQL away and recursively traverse the tree for this in the application.

    If you know how many levels you have beforehand (i.e. you know the tree will never have more than 4 levels for whatever reason), you can just do a bunch of joins on the parent and count the nodes that way.
    SQL optimization, Data management, Back-end development
    http://brainfreezeanalytics.com
    info@brainfreezeanalytics.com

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Intaglio5 View Post
    I'd abstract the SQL away and recursively traverse the tree for this in the application.
    that's about as inefficient as it is possible to be

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

  4. #4
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why do you say that? And how is it possible to recursively count the nodes in the SQL query itself?
    SQL optimization, Data management, Back-end development
    http://brainfreezeanalytics.com
    info@brainfreezeanalytics.com

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Intaglio5 View Post
    why do you say that?
    because the overhead associated with each call to the database is like a separate query

    for one "recursive" request, you could actually run hundreds of queries

    getting all your data in a single query is much faster
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're right, each call to the database would be a separate query -- and there'd be no reason to query the database multiple times if the number of levels you have is static. But if it's not, which could happen if you have users dynamically creating nodes and levels, then the only way I can think of to track node counts is to either run recursive calls in the application or keep track of the number of levels as the nodes are created and use multiple left joins. In this case, though, you're right, it makes more sense to just get all of the data in one query since you know how many levels there are within the tree.
    SQL optimization, Data management, Back-end development
    http://brainfreezeanalytics.com
    info@brainfreezeanalytics.com

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    there are several options

    1. realize that more than N levels (e.g. 15) is silly, and set a max at that (note that performance is still okay, even for 15 LEFT JOINs)

    2. realize that the user may not visually be able to comprehend (cf. Tufte) a robust tree's complete expansion to N levels, therefore show the user only a couple of levels down (say 2, or 3), from the point he's currently at -- like a drill-down, limiting each request to at most 2 or 3 LEFT JOINs, a single query

    3. use a completely different data model, i.e. nested sets, which does not require recursion
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •