fetching a records from binary tree in mysql

Question

I need to know that how to fetch a record from table which consist of certain records varies with different id named[sponser_id]

from the above img i give you a simple scenario..say for ex: the rounded person is said to be the person A.

when Person A loggin into his/her acc. It should show how many members(count) are coming under his/her control.
Mysql table has columns like
sponser_id refers to the parent user_id.
sponser_id varies with parent_user who referred them.
Here my question is, How to retrieve the count of members under this particular person..
I have only user_id & sponser_id columns alone in my table.

Mysql doesnt have support for hierarchial queries,your best bet is to self join as many times as needed — Mihai
– Mihai, Commented Oct 17, 2015 at 16:15
MySQL basically has no support for hierarchical or recursive queries. You can do this with a stored procedure, by changing the data structure (to include the full path to the top), or by using a different database. — Gordon Linoff
– Gordon Linoff, Commented Oct 17, 2015 at 16:15
Gordon just gave you a suggestion. What is the text output of the table structure. It is not simple, so the word just doesn't apply :) — Drew
– Drew, Commented Oct 17, 2015 at 16:21

Drew · Accepted Answer · 2015-10-17 16:49:28Z

1

Here is an MLM tree I once implemented here, I think I deleted the answer cuz it was not appreciated :)

I always do these with Stored Procedures. A member can have a parent, like your setup.

The output showed downline sales and a commission at 5%

Schema

-- drop table member;
create table member
(   memberId int not null auto_increment primary key,
    handle varchar(255) not null,
    parentId int null,
    key (parentId)
);


-- drop table sales
create table sales
(   -- sales of Products abbreviated
    id int auto_increment primary key,
    memberId int not null,
    amount decimal(10,2) not null,
    saleDate datetime not null,
    CONSTRAINT `fk_member`
        FOREIGN KEY (memberId) 
        REFERENCES member(memberId)
);

insert member(handle,parentId) values ('Johnny Two-Thumbs',null); -- 1
insert member(handle,parentId) values ('Jake the Enforcer',null); -- 2
insert member(handle,parentId) values ('Innocent Kim',2); -- 3
insert member(handle,parentId) values ('Karen',2); -- 4
insert member(handle,parentId) values ('Henry',2); -- 5
insert member(handle,parentId) values ('Shy Sales-less Sam',5); -- 6
insert member(handle,parentId) values ('Pete',5); -- 7
insert member(handle,parentId) values ('Iowa Mom',7); -- 8
insert member(handle,parentId) values ('Albuquerque Agoraphobiac',7); -- 9

insert sales (memberId,amount,saleDate) values (2,1,'2015-01-01');
insert sales (memberId,amount,saleDate) values (5,10,'2015-01-20');
insert sales (memberId,amount,saleDate) values (5,15.50,'2015-01-22');
insert sales (memberId,amount,saleDate) values (7,101.12,'2015-02-01');
insert sales (memberId,amount,saleDate) values (7,201.12,'2015-03-01');
insert sales (memberId,amount,saleDate) values (7,109,'2015-04-01');
insert sales (memberId,amount,saleDate) values (7,45,'2015-05-01');
insert sales (memberId,amount,saleDate) values (8,111,'2015-04-20');
insert sales (memberId,amount,saleDate) values (8,99.99,'2015-05-22');
insert sales (memberId,amount,saleDate) values (9,0.04,'2015-06-20');
insert sales (memberId,amount,saleDate) values (9,1.23,'2015-06-24');

Stored Procedure

drop procedure if exists showAllDownlineSales;
DELIMITER $$
create procedure showAllDownlineSales 
(
theId int
)
BEGIN
    -- theId parameter means i am anywhere in hierarchy of membership
    -- and i want all downline sales
    -- return 1 row: sales amt for downlines, and that amt * 5%, and total children (including children-of-children)
    declare bDoneYet boolean default false;
    declare working_on int;
    declare theCount int;
    declare downlineSales decimal(10,2);
    declare commish decimal(10,2);

    CREATE temporary TABLE xxFindSalesxx
    (
        memberId int not null,
        processed int not null, -- 0 for not processed, 1 for processed
        salesTotal decimal(10,2) not null
    );

    set bDoneYet=false;
    insert into xxFindSalesxx (memberId,processed,salesTotal) select theId,0,0;
    while (!bDoneYet) do
        select count(*) into theCount from xxFindSalesxx where processed=0;

        if (theCount=0) then 
            -- found em all
            set bDoneYet=true;
        else
            -- one not processed yet, insert its children for processing
            SELECT memberId INTO working_on FROM xxFindSalesxx where processed=0 limit 1;

            insert into xxFindSalesxx (memberId,processed,salesTotal)
            select memberId,0,0 from member
            where parentId=working_on;

            -- update xxFindSalesxx
            -- join sales
            -- on sales.memberId=xxFindSalesxx.memberId
            -- set salesTotal=sum(sales.amount)
            -- where xxFindSalesxx.memberId=working_on;

            update xxFindSalesxx
            set salesTotal=(select ifnull(sum(sales.amount),0) from sales where memberId=working_on)
            where xxFindSalesxx.memberId=working_on;

            -- mark the one we "processed for children" as processed
            update xxFindSalesxx set processed=1 where memberId=working_on;
        end if;
    end while;

    delete from xxFindSalesxx where memberId=theId;
    select sum(salesTotal),count(*) into downlineSales,theCount from xxFindSalesxx;
    drop table xxFindSalesxx;
    select downlineSales,round(downlineSales*0.05,2) as commission,theCount;    -- there is your answer, 1 row
END
$$
DELIMITER ;

Test it

call showAllDownlineSales(2); -- 693.00     34.69    7
call showAllDownlineSales(1); -- null       null     0
call showAllDownlineSales(5); -- 668.50     33.43    4

edited Oct 17, 2015 at 16:49

answered Oct 17, 2015 at 16:33

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Drew Over a year ago

note, edit performed, to show total children (which includes children-of-children) count as last output column

parthiban Over a year ago

since you were worked in mlm proj..am asking you how to retrieve count(*) for loggined user who are all comes under him. even though his/her referrence_id changes dynamically

Drew Over a year ago

not quite sure I understand the question. But here are some thoughts. (1) any branch can be re-hung as you know, under another parent, (2) if a new user logs in, that is a different row in the member table such as row mmm and not row nnn

Drew Over a year ago

the reason I even have commission residue in this thing is because there were different commission %'s based on level (level 1 under, 2,3 etc), so I was expanding the thing for that guy that time

Drew Over a year ago

so if Johnny is under Kim and Kim is under Kate, and Kim no longer is part of the MLM, then Johnny's branch can be re-hung under Kate as now level 1 under Kate and not level 2

|

Collectives™ on Stack Overflow

fetching a records from binary tree in mysql

1 Answer 1

Schema

Stored Procedure

Test it

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Schema

Stored Procedure

Test it

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related