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_paren t_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_i d ) >0, count( children.icat_i d ) , 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.ica t_parent_id IS NULL
GROUP BY parent_id
ORDER BY parent_name ASC

--
Perttu Pulkkinen

https://www.risteys.net

https://www.onnenliinat.com

https://www.palvelukaupunki.fi

[Bill Karwin]

Re: basic recursive query (but not for me obviously)

Perttu Pulkkinen wrote:[color=blue]
> I need to find toplevel image categories and a) number of images directly in
> them and b) number of subcategories directly in those topcategories.[/color]

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_i d int(11) NOT NULL,
icat_descendant _id int(11) NOT NULL,
pathlength int(11) NOT NULL,
KEY icat_ancestor_i d (icat_ancestor_ id),
KEY icat_descendant _id (icat_descendan t_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_ancesto r_id = P2.icat_descend ant_id)
WHERE P2.icat_descend ant_id IS NULL

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

SELECT P.icat_descenda nt_id
FROM pathenum P
WHERE P.icat_ancestor _id = ?

Regards,
Bill K.

[Paul Bramscher]

Re: basic recursive query (but not for me obviously)

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:[color=blue]
> Perttu Pulkkinen wrote:
>[color=green]
>> I need to find toplevel image categories and a) number of images
>> directly in
>> them and b) number of subcategories directly in those topcategories.[/color]
>
>
> 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_i d int(11) NOT NULL,
> icat_descendant _id int(11) NOT NULL,
> pathlength int(11) NOT NULL,
> KEY icat_ancestor_i d (icat_ancestor_ id),
> KEY icat_descendant _id (icat_descendan t_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_ancesto r_id = P2.icat_descend ant_id)
> WHERE P2.icat_descend ant_id IS NULL
>
> You can find all the children of a given toplevel category with:
>
> SELECT P.icat_descenda nt_id
> FROM pathenum P
> WHERE P.icat_ancestor _id = ?
>
> Regards,
> Bill K.[/color]