Best Practice on SQL??

[darknailblue]

Hey guys!! Here's a weird question...

I am designing a site that has categories and subcategories for products. Being that the site is new, there needs to be some function that only displays the categories (or subcategories) if there are actually products that belong to them...

I know how to accomplish this but what I am trying to avoid is querying the DB too often. Basically it would have to query the entire product catalog for every category and then for every sub category. So all in all. If I had 6 categories and 10 subcategories in each main category, that's 60 queries.

How can I avoid a crazy query like this? Any advice is appreciated....


Thanks in advance,

[Shadowfiend]

Does it nest indefinitely? That is to say, can you have a subcategory with subcategories? If not, then you can do it all in a single query using a join to the same table. If so, then it gets more complicated.

[darknailblue]

Does it nest indefinitely? That is to say, can you have a subcategory with subcategories? If not, then you can do it all in a single query using a join to the same table. If so, then it gets more complicated.

Nope... Just categories and subcategories...

How would the join help me in this situation?

And what would an example query look like?

[Shadowfiend]

Tell me really quick how your database is structured -- i.e., how you represent products and categories and their relation. We can go from there.

[smoseley]

Categories should be in a recursive table (references itself)

Main categories have NULL parent_id, subcategories reference the parent category.

Products then reference the category they belong to.

CREATE TABLE category (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    parent_id INT NOT NULL REFERENCES category(id),
    name VARCHAR(255) NOT NULL,
    description TEXT NULL
);

INSERT INTO category (id, parent_id, name)
VALUES
    (1, NULL 'main category 1'),
    (2, NULL 'main category 2'),
    (3, 1 'sub category 1-1'),
    (4, 1 'sub category 1-2'),
    (5, 2 'sub category 2-1'),
    (6, 2 'sub category 2-2');

CREATE TABLE product (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL REFERENCES category(id),
    name VARCHAR(255) NOT NULL,
    description TEXT NULL
);

To select only categories that have products in them, use aggregation:

SELECT c.*, COUNT(p.id)
FROM category AS c
INNER JOIN product AS p ON c.id = p.category_id
GROUP BY c.id
HAVING COUNT(p.id) > 0;