Question

I have a tree like structure of Categories, the leaf Nodes in the tree have Products and the products have Cods I need to select all the top level Categories (parent=null) that have leafs (Cods) that match some critaria...

SELECT  
    Category.Id AS Id0_, 
    Category.Name AS Name0_, 
    Category.COrder AS COrder0_, 
    Category.Description AS Descript4_0_, 
    Category.ParentId AS ParentId0_, 
    Category.Description_En AS Descript6_0_, 
    Category.Name_En AS Name_En0_, 
    Category.ImagePath AS ImagePath0_ 
FROM
    Category 
    LEFT JOIN Category AS c1 ON Category.Id=c1.ParentId
    LEFT JOIN Category AS c2 ON c1.Id=c2.ParentId
    LEFT JOIN Category AS c3 ON c2.Id=c3.ParentId
    LEFT JOIN Category AS c4 ON c3.Id=c4.ParentId
    LEFT JOIN Product ON 
        c4.Id=Product.Category 
        OR c3.Id=Product.Category 
        OR c2.Id=Product.Category 
        OR c1.Id=Product.Category 
        OR Category.Id=Product.Category
    INNER JOIN Cod ON Cod.Product=Product.Id   
WHERE
    Category.ParentId is null 
    AND Cod.Hidden!= 1  
    AND 
    (
        cod.Stock>0 
        OR (cod.CodBare= 0  AND Product.ProdType=8)) 
        AND Cod.Price>0
    )
ORDER BY Category.COrder

my query looks like this, but it is not a solution because it is very very slow... Can someone give me a suggestion on how to do this?

Answer 1

This is a common challenge. Creating hierarchical data from a relational database isn t always elegant. If this data isn t updated constantly, one option is to spit it out as XML and cache that for the application.

If you want to keep it in the DB, this is a common solution: Managing Hierarchical Data in MySQL.

Answer 2

There are various solutions to represent trees in databases. I really recommend you to take a look to this presentation, Trees in the database. I usually work with a database with multiple levels and 1.5 millions leafs and the nested interval model was really illuminating.

Answer 3

I don t know if you are stuck with this data structure or not but you should really look at the article Managing Hierarchical Data in MySQL for hierarchical data management.

I have used the solution described here (in Oracle and MS SQL Server) and it is very fast.

友情链接