473,847 Members | 1,558 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

basic recursive query (but not for me obviously)

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
www.risteys.net
www.onnenliinat.com
www.palvelukaupunki.fi
Jul 20 '05 #1
2 7035
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_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.
Jul 20 '05 #2
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_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.

Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3135
by: replace-this-with-my-name | last post by:
Hi. How do I return a string containing an entire menu-tree from a recursive function? Here is my current recursive function: function page_tree( $_i ){ //Call global mysql connection variable
19
3232
by: Leif K-Brooks | last post by:
Has anyone ever tried implementing a simple unstructured BASIC dialect in Python? I'm getting interested in language implementation, and looking at a reasonably simple example like that could be pretty interesting.
19
2303
by: Carlos Ribeiro | last post by:
Hello all, Here I am using some deeply nested, tree-like data structures. In some situations I need to traverse the tree; the old-style way to do it is to write a recursive method on the node class, as in: def walk(self): """old-style recursive tree traversal""" child.do_something for child in childs:
7
2667
by: aurora | last post by:
I love generator and I use it a lot. Lately I've been writing some recursive generator to traverse tree structures. After taking closer look I have some concern on its performance. Let's take the inorder traversal from http://www.python.org/peps/pep-0255.html as an example. def inorder(t): if t: for x in inorder(t.left):
3
4179
by: Vincenzino | last post by:
Hi, I have some problem in using SQL3 recursive queries on DB2 database system (8.1 and 8.2 UDB). I need to compute the transitive closure of a (possibly) ciclic graph using SQL3 on DB2. I MUST use pure SQL3 and I cannot use cursors, indexes and the like. The graph is represented by a set of arcs with the table "arc" having two attributes "a1" and "a2" for source and target nodes resp. The SQL3 query I'm using is the following:
6
4559
by: Einar ?rn | last post by:
Hi all, is there a good way to detect recursive C code in large systems? A method or a free tool? Best regards, E
18
4744
by: Just Another Victim of the Ambient Morality | last post by:
Is pyparsing really a recursive descent parser? I ask this because there are grammars it can't parse that my recursive descent parser would parse, should I have written one. For instance: from pyparsing import * grammar = OneOrMore(Word(alphas)) + Literal('end') grammar.parseString('First Second Third end')
2
5551
by: Jim Devenish | last post by:
I have a table named StockItems with field: StockItemID QuantityInStock I am creating assemblies of stock items and have another table named StockItemAssemblies with fields: StockItemAssemblyID AssemblyID StockItemID Quantity
3
4251
by: from.future.import | last post by:
Hi, I encountered garbage collection behaviour that I didn't expect when using a recursive function inside another function: the definition of the inner function seems to contain a circular reference, which means it is only collected by the mark-and-sweep collector, not by reference counting. Here is some code that demonstrates it: === def outer():
0
9735
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10658
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10718
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10347
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7889
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7062
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4541
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4133
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3171
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.