Re: Recursive queries
Hi, Steffan A. Cline wrote: I am trying to set up a forum where there are main posts with replies and replies to replies. Rather than using nested queries in my middleware I am thinking there has to be some kind of recursive query where I can output the results in a format like so: MAIN Reply to main reply to reply to main reply to main reply to 2nd reply to main MAIN Reply reply The table structure is like so: +---+--+--+-+---+--- -+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+---+--- -+ | forum_id | bigint(11) | NO | PRI | NULL | auto_increment | | project_id| bigint(11) | YES | MUL | 0 | | | forum_reply_id| bigint(11) | YES | MUL | 0 | | | forum_dev_id | bigint(11) | YES | MUL | 0 | | | forum_type| varchar(255) | YES | | | | | forum_subject | varchar(255) | YES | | | | | forum_message | longtext | YES | | NULL | | | forum_date_posted | timestamp| NO | | CURRENT_TIMESTAMP | | +---+--+--+-+---+--- -+ Test data is like so mysql select * from forums; +--+++--++-- -+-+ -+ | forum_id | project_id | forum_reply_id | forum_dev_id | forum_type | forum_subject | forum_message | forum_date_posted | +--+++--++-- -+-+ -+ |1 | 42 | 0 |1 || First Post| I am the First! | 2007-07-08 15:09:41 | |2 | 42 | 1 |1 || | I am a reply to the first | 2007-07-08 15:30:36 | |3 | 42 | 0 |1 || sample data | this is some sample data in a new thread| 2007-07-08 15:10:03 | |4 | 42 | 2 |1 || | this is a reply to the reply of the first post. | 2007-07-08 15:33:54 | +--+++--++-- -+-+ -+ 4 rows in set (0.00 sec) I am figuring that if the reply_to_id is 0 then it is a parent thread otherwise it is a child or child of a child etc. There are many ways to model hierarchies and trees in SQL. The one I think lends itself best to this query is nested sets. Joe Celko's book is the classic on it, though there are good descriptions of it in many places (Pro MySQL, and even for free online, for example the sample chapters in http://www.artfulsoftware.com/). Celko and others also give examples of other methods and the kinds of queries needed for them. I'd have to check, but offhand I don't think there's any way to write the query you want with the data structure you have, unless you use recursive Common Table Expressions, which MySQL doesn't support. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recursive queries
On 7/8/07, Steffan A. Cline [EMAIL PROTECTED] wrote: I am trying to set up a forum where there are main posts with replies and replies to replies. Rather than using nested queries in my middleware I am thinking there has to be some kind of recursive query where I can output the results in a format like so: MAIN Reply to main reply to reply to main reply to main reply to 2nd reply to main MAIN Reply reply As another poster said, there are various ways to represent trees in databases. You may want to consult some of these references. In this particular case, nothing comes to mind that will give you both: a)The ability to represent an arbitrarily-deep hierarchy of responses, AND b)Will let you get the entire result set ordered the way you want in ONE query. I don't immediately see how to get both at the same time. If, for example, you were willing to sacrifice (a), then just set up integers (maybe 3 of them) allowing you to represent a nesting 3 deep then order by these integers on the query. But 3 is not arbitrarily-deep. If you were willing to sacrifice (b), then you could just represent the tree by a table of links that relate parent and child. Finding all the children for a parent is just select * from links where parent=29331 or something like this. Problem is, you would need to issue queries to traverse the tree. I can't immediately see a way to get both (a) and (b) simultaneously. But you can definitely get EITHER (a) or (b). Dave
Recursive queries
I am trying to set up a forum where there are main posts with replies and replies to replies. Rather than using nested queries in my middleware I am thinking there has to be some kind of recursive query where I can output the results in a format like so: MAIN Reply to main reply to reply to main reply to main reply to 2nd reply to main MAIN Reply reply The table structure is like so: +---+--+--+-+---+--- -+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+---+--- -+ | forum_id | bigint(11) | NO | PRI | NULL | auto_increment | | project_id| bigint(11) | YES | MUL | 0 | | | forum_reply_id| bigint(11) | YES | MUL | 0 | | | forum_dev_id | bigint(11) | YES | MUL | 0 | | | forum_type| varchar(255) | YES | | | | | forum_subject | varchar(255) | YES | | | | | forum_message | longtext | YES | | NULL | | | forum_date_posted | timestamp| NO | | CURRENT_TIMESTAMP | | +---+--+--+-+---+--- -+ Test data is like so mysql select * from forums; +--+++--++-- -+-+ -+ | forum_id | project_id | forum_reply_id | forum_dev_id | forum_type | forum_subject | forum_message | forum_date_posted | +--+++--++-- -+-+ -+ |1 | 42 | 0 |1 || First Post| I am the First! | 2007-07-08 15:09:41 | |2 | 42 | 1 |1 || | I am a reply to the first | 2007-07-08 15:30:36 | |3 | 42 | 0 |1 || sample data | this is some sample data in a new thread| 2007-07-08 15:10:03 | |4 | 42 | 2 |1 || | this is a reply to the reply of the first post. | 2007-07-08 15:33:54 | +--+++--++-- -+-+ -+ 4 rows in set (0.00 sec) I am figuring that if the reply_to_id is 0 then it is a parent thread otherwise it is a child or child of a child etc. Any way of doing this? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recursive queries
Hello. Though I haven't read this article, but its title looks promicing: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Duncan Miller wrote: I am looking to do a query on a self join table that returns the parent records. Obviously there are ways to do this, but just wondered if there are any functions in MySQL that make this easier, or specific functions available to stored procedures. I have read the manual and couldn't find anything, but thought I would ask. I notice that the new MS SQL Server has support for recursive queries and oracle also, so I thought maybe... Thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recursive queries
I believe that some time in the future mysql will support oracle style connect by prior syntax but it's not implemented yet. Gleb Paharenko wrote: Hello. Though I haven't read this article, but its title looks promicing: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Duncan Miller wrote: I am looking to do a query on a self join table that returns the parent records. Obviously there are ways to do this, but just wondered if there are any functions in MySQL that make this easier, or specific functions available to stored procedures. I have read the manual and couldn't find anything, but thought I would ask. I notice that the new MS SQL Server has support for recursive queries and oracle also, so I thought maybe... Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recursive queries
Title: DIY MTB Is there any built in or other support for recursive queries . I suspect not, but wonder what is considered the best approach. With stored procedures being supported in 5.0 is that the way to handle these. Thanks -- Duncan Miller [EMAIL PROTECTED] www.diymtb.com.au 0429 923 962
Re: recursive queries
Duncan Miller [EMAIL PROTECTED] wrote on 12/08/2005 06:40:55 AM: Is there any built in or other support for recursive queries . I suspect not, but wonder what is considered the best approach. With stored procedures being supported in 5.0 is that the way to handle these. Thanks -- [image removed] Duncan Miller [EMAIL PROTECTED] www.diymtb.com.au 0429 923 962 Limited recursion is being developed (recursion is allowed but limited to a certain recursion depth). I forget if it is supposed to be in 5.0.16 or 5.0.17 but it is coming. Normally, SQL queries do not need to be recursive and many that are designed that way end up running slower than equivalent, non-recursive queries. I am not saying that non-recursive is preferable to EVERY situation, just that it sometimes provides greater performance to rejigger your schema to avoid the need for recursion than it is to build a recursive database. What is it you are trying to do that requires recursion? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: recursive queries
I am looking to do a query on a self join table that returns the parent records. Obviously there are ways to do this, but just wondered if there are any functions in MySQL that make this easier, or specific functions available to stored procedures. I have read the manual and couldn't find anything, but thought I would ask. I notice that the new MS SQL Server has support for recursive queries and oracle also, so I thought maybe... Thanks
Recursive queries
I have a table that represents a tree structure via a self-join. I'd like to get hold of all parent records in a single query - is such a thing possible? e.g. given idparentid 10 21 32 42 51 64 If I was starting with record 4, I would want it to return records 2 and 1 (probably in that order), starting from 5 would just give me 1 etc. It needs to support arbitrary depth, hence the need for recursion. Can I do this in one go, or do I have to query iteratively until I encounter a zero reference? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recursive queries
Hi, in oracle we have connect by prior for hierachical data traversal. in mysql, you can use group_concat like this : mysql select parentid, group_concat(id) from ids - group by parentid; +--+--+ | parentid | group_concat(id) | +--+--+ |0 | 1| |1 | 2,5 | |2 | 3,4 | |4 | 6| +--+--+ 4 rows in set (0.13 sec) unless your need is more complex. if you use php, you can look at http://www.sitepoint.com/article/hierarchical-data-database Mathias Selon Marcus Bointon [EMAIL PROTECTED]: I have a table that represents a tree structure via a self-join. I'd like to get hold of all parent records in a single query - is such a thing possible? e.g. given idparentid 10 21 32 42 51 64 If I was starting with record 4, I would want it to return records 2 and 1 (probably in that order), starting from 5 would just give me 1 etc. It needs to support arbitrary depth, hence the need for recursion. Can I do this in one go, or do I have to query iteratively until I encounter a zero reference? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recursive queries
On 13 May 2005, at 10:02, [EMAIL PROTECTED] wrote: if you use php, you can look at http://www.sitepoint.com/article/hierarchical-data-database That's just what i needed, great article. Thanks, Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recursive queries
Am Freitag, 13. Mai 2005 10.32 schrieb Marcus Bointon: I have a table that represents a tree structure via a self-join. I'd like to get hold of all parent records in a single query - is such a thing possible? e.g. given idparentid 10 21 32 42 51 64 There is an alternative way of modelling tree structures in a relational db, nested sets, it's more complex, but your requirements (and others) can be reached with a single statement. I'm just evaluating this thing, so I have no experience with it, but it sounds very good. Here is a link: http://www.intelligententerprise.com/001020/celko1_1.jhtml There are also perl modules (where you could get statements for specific task): http://search.cpan.org/~djcp/DBIx-Tree-NestedSet-0.16/lib/DBIx/Tree/NestedSet.pm === from the first link: CREATE TABLE Personnel (emp CHAR(10) NOT NULL PRIMARY KEY, lft INTEGER NOT NULL UNIQUE CHECK (lft 0), rgt INTEGER NOT NULL UNIQUE CHECK (rgt 1), CONSTRAINT order_okay CHECK (lft rgt) ); 1. Find an employee and all his/her supervisors, no matter how deep the tree. SELECT P2.* FROM Personnel AS P1, Personnel AS P2 WHERE P1.lft BETWEEN P2.lft AND P2.rgt AND P1.emp = :myemployee; joe If I was starting with record 4, I would want it to return records 2 and 1 (probably in that order), starting from 5 would just give me 1 etc. It needs to support arbitrary depth, hence the need for recursion. Can I do this in one go, or do I have to query iteratively until I encounter a zero reference? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recursive queries
Quick searches of Google and the MySQL page didn't turn up anything, so I'll ask here.. Here's what I have: I have a table that among others, have fields called 'section_id' and 'parent_id'. The section_id is the id of the "self" section, and the parent_id is the id of the section that "owns" the self section. (It's sort of like a filesystem where one directory has subdirectories, and so on) Here's what I'm trying to do: I want to start with one section, and go down the tree of sub-sections, picking up information about them on the way. Example: I want to start at the "root" section, and follow down one of it's sub-sections. (Root) | |--- Sports Products || ||--- Golf :: Item1, Item2, Item3, Item4 || ||--- Fishing :: Item5, Item6, Item7, Item8 So it will start at Root, find that Sports Products is attached to Root, get the information I want from Sports Products, then it will find that Golf and Fishing are attached to Sports Products, and get the information from them respectively. (And so on) As it stands, it won't go any further than 2 levels deep from the root section, so I won't need to find a more effecient way to organize the relationships for now. What do you all feel would be the best way to do this? -- Isaac Force [EMAIL PROTECTED] (503)656-2489 http://gorgonous.dhs.org Real Users find the one combination of bizarre input values that shuts down the system for days. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Recursive queries
Well, if you have everything setup right.. this is easy to do in SQL. Lets say you have your root table: create table root( id integer unsigned not null auto_increment, description varchar(50), key root_key (id) ); and your sub categories: create table subcats( id integer unsigned not null auto_increment, root_id integer unsigned not null, description varchar(50), key subcat_key (id) ); and your final category: create table subsections( id integer unsigned not null auto_incremnt, parent_id integer unsigned not null, description varchar(50), key subsec_key (id) ); now you can simply join all these tables.. SELECT subsection.description,subcats.description,root.description FROM subsection,subcats,root WHERE ( (subcats.root_id=root.id) AND (subsections.parent_id=subcats.id) AND (root.id=N) ); (where N is the root.id number you are looking for) that way, in root you could have 'Sports' In subcats you would have 'Sports Equipment', 'Sports Stories', etc.. in subsection, you could have 'Golf Clubs' etc.. and so on or you could divide it up more and go from Sports Equipment, to Golf to Golf Equipment.. hope this helps.. - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.954.9133 -Original Message- From: Isaac Force [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 04, 2001 5:03 PM To: [EMAIL PROTECTED] Subject: Recursive queries Quick searches of Google and the MySQL page didn't turn up anything, so I'll ask here.. Here's what I have: I have a table that among others, have fields called 'section_id' and 'parent_id'. The section_id is the id of the "self" section, and the parent_id is the id of the section that "owns" the self section. (It's sort of like a filesystem where one directory has subdirectories, and so on) Here's what I'm trying to do: I want to start with one section, and go down the tree of sub-sections, picking up information about them on the way. Example: I want to start at the "root" section, and follow down one of it's sub-sections. (Root) | |--- Sports Products || ||--- Golf :: Item1, Item2, Item3, Item4 || ||--- Fishing :: Item5, Item6, Item7, Item8 So it will start at Root, find that Sports Products is attached to Root, get the information I want from Sports Products, then it will find that Golf and Fishing are attached to Sports Products, and get the information from them respectively. (And so on) As it stands, it won't go any further than 2 levels deep from the root section, so I won't need to find a more effecient way to organize the relationships for now. What do you all feel would be the best way to do this? -- Isaac Force [EMAIL PROTECTED] (503)656-2489 http://gorgonous.dhs.org Real Users find the one combination of bizarre input values that shuts down the system for days. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Recursive queries
create table root( create table subcats( create table subsections( And now I need 230 levels, huh :). How about a simple tree structure ? hth pa - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Recursive queries
On Mon, 5 Mar 2001 10:13:29 -0600 "Johnny Withers" [EMAIL PROTECTED] wrote: Well, if you have everything setup right.. this is easy to do in SQL. Lets say you have your root table: [...] and your sub categories: [...] and your final category: [...] now you can simply join all these tables.. I have a single table that contains all the sections, so I would not have a limit to how many sections and subsections that I can create. Yes, it would be easy to do a join on those three tables, but that doesn't scale very well since I would have to add a table if I wanted the ability to go deeper than two subsections. Here is the current structure of the table: ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | section_id | tinyint(3) unsigned | | PRI | NULL| auto_increment | | parent_id | tinyint(3) unsigned | | | 0 || | name | varchar(100)| | | || | descript | mediumtext | YES | | NULL|| | is_hidden | tinyint(4) | | | 0 || | modified | timestamp(14) | YES | | NULL|| ++-+--+-+-++ They link together by section_id's and parent_id's. With what I described below, the section record would contain: (Root) | |== section_id: 0 | |== parent_id: 0 | | | +Sports Products| || | ||== section_id: 2 ---|| ||== parent_id: 0 || || | |+Golf | ||| | |||== section_id: 3 | |||== parent_id: 2 | || | |+Fishing Products | ||| | |||== section_id: 4 | |||== parent_id: 2 | Here's what I have: I have a table that among others, have fields called 'section_id' and 'parent_id'. The section_id is the id of the "self" section, and the parent_id is the id of the section that "owns" the self section. (It's sort of like a filesystem where one directory has subdirectories, and so on) Here's what I'm trying to do: I want to start with one section, and go down the tree of sub-sections, picking up information about them on the way. Example: I want to start at the "root" section, and follow down one of it's sub-sections. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Recursive queries
On Mon, 5 Mar 2001 21:50:42 + Pierre-Alain Joye [EMAIL PROTECTED] wrote: create table root( create table subcats( create table subsections( And now I need 230 levels, huh :). How about a simple tree structure ? If our thinking of a tree structure is the same, I believe that this is what I have.. (See previous post with table specs. and whatnot) What I'm trying to figure out is an efficient way to go down the tree grabbing information from records that are associated with sections. (Items in a store) I'm doing this in PHP, btw. -- Isaac Force [EMAIL PROTECTED] (503)656-2489 http://gorgonous.dhs.org Real Users find the one combination of bizarre input values that shuts down the system for days. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Recursive queries
Quick searches of Google and the MySQL page didn't turn up anything, so I'll ask here.. Here's what I have: I have a table that among others, have fields called 'section_id' and 'parent_id'. The section_id is the id of the "self" section, and the parent_id is the id of the section that "owns" the self section. (It's sort of like a filesystem where one directory has subdirectories, and so on) Here's what I'm trying to do: I want to start with one section, and go down the tree of sub-sections, picking up information about them on the way. Example: I want to start at the "root" section, and follow down one of it's sub-sections. (Root) | |--- Sports Products || ||--- Golf :: Item1, Item2, Item3, Item4 || ||--- Fishing :: Item5, Item6, Item7, Item8 So it will start at Root, find that Sports Products is attached to Root, get the information I want from Sports Products, then it will find that Golf and Fishing are attached to Sports Products, and get the information from them respectively. (And so on) As it stands, it won't go any further than 2 levels deep from the root section, so I won't need to find a more effecient way to organize the relationships for now. What do you all feel would be the best way to do this? -- Isaac Force [EMAIL PROTECTED] (503)656-2489 http://gorgonous.dhs.org I think that Joe Celko and his publisher should set up some sort of affiliate program so that I earn a little money every time I post the following: 'SQL for Smarties' by Joe Celko, two chapters covering two different approaches to dealing with tree structures. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
