basic recursive query (but not for me obviously)

[Perttu Pulkkinen]

I need to find toplevel image categories and a) number of images directly in
them and b) number of subcategories directly in those topcategories. In
toplevel image categories "icat_parent_id IS NULL". Below is also current
erraneous draft but it has ambigous filed name problem.

CREATE TABLE icategories (
icat_id int(11) NOT NULL auto_increment,
icat_parent_id int(11) default NULL,
icat_name char(100) default NULL,
PRIMARY KEY (icat_id),
KEY icat_parent_id (icat_parent_id),
KEY icat_name (icat_name)
) TYPE=MyISAM;
CREATE TABLE images (
img_id int(11) NOT NULL auto_increment,
img_icat_id int(11) default NULL,
img_data mediumblob,
PRIMARY KEY (img_id),
KEY img_icat_id (img_icat_id)
) TYPE=MyISAM;
SELECT
icat_id AS parent_id,
icat_name AS parent_name,
count( img_id ) AS img_count,
IF (count( children.icat_id ) >0, count( children.icat_id ) , 0) AS
cat_count
FROM icategories
LEFT OUTER JOIN images ON img_icat_id = parent_id
LEFT OUTER JOIN icategories AS children ON icat_parent_id = parent_id
WHERE icategories.icat_parent_id IS NULL
GROUP BY parent_id
ORDER BY parent_name ASC

--
Perttu Pulkkinen
www.risteys.net
www.onnenliinat.com
www.palvelukaupunki.fi

[Bill Karwin]

Perttu Pulkkinen wrote:

I need to find toplevel image categories and a) number of images directly in
them and b) number of subcategories directly in those topcategories.

I liked Joe Celko's book "SQL for Smarties", in which he talks about
representing recursive relationships in SQL.

The method he describes is to create another table to record paths in
the "tree":

CREATE TABLE pathenum (
icat_ancestor_id int(11) NOT NULL,
icat_descendant_id int(11) NOT NULL,
pathlength int(11) NOT NULL,
KEY icat_ancestor_id (icat_ancestor_id),
KEY icat_descendant_id (icat_descendant_id),
);

This is instead of recording parent_id in your icategories table.

Populate all paths in the tree, for instance if A -> B -> C, then
you record (A,B,1), (B,C,1), and (A,C,2).

Then you can find toplevel category ids with:

SELECT DISTINCT P.icat_ancestor_id
FROM pathenum P LEFT OUTER JOIN pathenum P2
ON (P.icat_ancestor_id = P2.icat_descendant_id)
WHERE P2.icat_descendant_id IS NULL

You can find all the children of a given toplevel category with:

SELECT P.icat_descendant_id
FROM pathenum P
WHERE P.icat_ancestor_id = ?

Regards,
Bill K.

[Paul Bramscher]

I've handled a number of recursive/tree situations with a LAMP-based
project (http://libdata.sourceforge.net/). This project builds
data-driven web pages with parent-child relationships. I do it this
way. Here's a subset of the fields I use to draw the pages:

* element_id
* page_id
* parent_id (references another element_id)
* generation (denormalized, represents how deep the recursion would go
if conducted)
* element_order (unique integer 1-N for each element on a given page)
* other fields to store the node data...

Distincts and complex joins are your enemy with large recordsets.
Recursive queries, likewise, are no-go for many applications. Want to
hit your database in hundreds of times to generate a single dataset?

This method will assemble all elements and build the complete tree with
a single query, and any element can be examined -- if needed -- for
individual genealogy if desired (the model supports recursion if you
insist on it).

You don't really need a pointer to descendants, since you should build a
SELECT statement based on the element_id as "root", and conduct extra
queries for it as a parent, then descend into those for which they
(themselves are parents, etc.).

Too many pointers and too many resursive techniques are not very
practical on high-volume sites, and large datasets.

Paul Bramscher
Bill Karwin wrote:

Perttu Pulkkinen wrote:

I need to find toplevel image categories and a) number of images
directly in
them and b) number of subcategories directly in those topcategories.

I liked Joe Celko's book "SQL for Smarties", in which he talks about
representing recursive relationships in SQL.

The method he describes is to create another table to record paths in
the "tree":

CREATE TABLE pathenum (
icat_ancestor_id int(11) NOT NULL,
icat_descendant_id int(11) NOT NULL,
pathlength int(11) NOT NULL,
KEY icat_ancestor_id (icat_ancestor_id),
KEY icat_descendant_id (icat_descendant_id),
);

This is instead of recording parent_id in your icategories table.

Populate all paths in the tree, for instance if A -> B -> C, then
you record (A,B,1), (B,C,1), and (A,C,2).

Then you can find toplevel category ids with:

SELECT DISTINCT P.icat_ancestor_id
FROM pathenum P LEFT OUTER JOIN pathenum P2
ON (P.icat_ancestor_id = P2.icat_descendant_id)
WHERE P2.icat_descendant_id IS NULL

You can find all the children of a given toplevel category with:

SELECT P.icat_descendant_id
FROM pathenum P
WHERE P.icat_ancestor_id = ?

Regards,
Bill K.