MySQL Forums
Forum List  »  Newbie

Re: Please help
Posted by: Peter Brawley
Date: July 11, 2015 01:10PM

> I wanted to achieve the result without creating a familytree table
> since I am not having the permission to create an extra table

Let's look at the sketchy spec you provided ...

Quote

dealer_id
url
dlr_type
fk_parent_dealerid

1. "dlr_type" can contain the types like "GROUP", "DEALER".
2. For the Main Group there can be any number of sub-groups with the same dlr_type "GROUP"
3. For the dlr_type "GROUP" the fk_parent_dealerid is always NULL for the top most "GROUP" (for the main Group) in the hierarchy.

Either this table is a proper tree---ie it has one row where fk_parent_dealerid is null (the root of the tree) and no dealer_id has more than one fk_parent_dealerid---or it isn't.

If it isn't a proper tree, but has multiple proper trees that you propose to query one at a time, then a query that scopes on the root dealer_id of the desired tree will return a proper tree, so you're ok; otherwise, to get a hierarchical report you will have to write a table that is a proper tree.

If your table is a tree, Listing 7a will efficiently produce a hierarchical report, but it needs to write a working `subtree` table to do so. Listing 7 (just above it in the chapter) implements the same logic, except that table & column names are hardwired, so an alternative for you would be to write your own version of listing 7.

Another alternative is to write a Nested Sets version of your tree (see later in the chapter), but that'll also require writing a table.

A recursive algorithm that doesn't require table writing is in listing 7b: it's about 100 times slower than listings 7 and 7a, perhaps 200 times slower (depending on the tree) than a Nested Sets algorithm.

So, most possible solutions require writing a table, but your employer demands a hierarchical report that writes no tables? When an employer so stupidly ties your hands, you can try to persuade her otherwise, or failing that use a slower algorithm like the one in listing 7b, or find a smarter employer.

> Even if I get a permission to create a familytree table, how I can map the family tree for all those records?

If your table isn't a proper tree, you have to write a new table anyway. Then querying it efficiently requires another table.

Three ways at least to get your results:

1. Use genericTree() as is:

set @tbl = 'yourtablename';
set @edgecol = 'dealer_id';
set @parentcol = 'fk_parent_dealerid';
set @dealer_root = 102863;
call genericTree( @tbl, @edgecol, @parentcol, @dealer_root );

select s.dealer_id, s.fk_parent_dealerid, t.url, t.group
from subtree
join `yourtablename` using(dealer_id);

2. Write your own version of the listing 7 sproc, using the names of your table & columns, so everythjing is in the result table, no need to write your own reporting query after it runs.

3. Write a Nested Sets tree from your table, and use queries from that section of the chapter.

Options: ReplyQuote


Subject
Written By
Posted
July 10, 2015 12:52PM
July 10, 2015 12:58PM
July 10, 2015 09:01PM
July 11, 2015 03:30AM
July 11, 2015 04:00AM
July 11, 2015 08:37AM
July 11, 2015 11:24AM
Re: Please help
July 11, 2015 01:10PM
July 11, 2015 01:24PM
July 12, 2015 12:28PM
July 12, 2015 12:46PM
July 12, 2015 04:08PM
July 13, 2015 02:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.