2

i have 2 table one node and relation and Second store buy each node. my code work fine but when sum multiple buy for each node result multiplied by number of purchases .

table nodes

+-------------+---------------+-------------+
| ancestor_id | descendant_id | path_length |
+-------------+---------------+-------------+
|           1 |             1 |           0 |
|           1 |             2 |           1 |
|           1 |             3 |           2 |
|           1 |             4 |           1 |
|           1 |             5 |           2 |
|           1 |             6 |           3 |
|           1 |             7 |           4 |
|           2 |             2 |           0 |
|           2 |             3 |           1 |
|           2 |             5 |           1 |
|           2 |             6 |           2 |
|           2 |             7 |           3 |
|           3 |             3 |           0 |
|           4 |             4 |           0 |
|           5 |             5 |           0 |
|           5 |             6 |           1 |
|           5 |             7 |           2 |
|           6 |             6 |           0 |
|           6 |             7 |           1 |
|           7 |             7 |           0 |
+-------------+---------------+-------------+

table buy

 +-------------+---------------+-------------+
   |      userid |        amount |
   +-------------+---------------+-------------+
   |           2 |          1500 |
   |           7 |          2000 |
   +-------------+---------------+-------------+

mysql code

SELECT 

    DISTINCT users.descendant_id  ,
    SUM(CASE WHEN ances.ancestor_id = buys_ances.userid THEN 1 ELSE 0 END) level_compress  


    FROM webineh_prefix_nodes_paths as users 

    join webineh_user_buys as buys on (users.descendant_id = buys.userid  )

    join webineh_prefix_nodes_paths as ances on (users.descendant_id = ances.descendant_id )

    join webineh_user_buys as buys_ances on (buys_ances.userid = ances.ancestor_id )



    WHERE  users.ancestor_id = 1 

     and
    (SELECT SUM(g2.amount) as amount FROM webineh_user_buys g2 where  g2.userid = ances.ancestor_id     group by  g2.userid ) >= 1000  

     and
    (SELECT SUM(g1.amount) as amount FROM webineh_user_buys g1 where  g1.userid = users.descendant_id   group by  g1.userid ) >= 1000  


group by buys.userid ,ances.ancestor_id 

result when in curent purchases data

users.descendant_id   |  users.ancestor_id   |      level_compress

2                     |                      |         1
6                     |                      |         2

i need to show ancestor_id compressed id

4

The issue of the amounts that are multiplied is easy to solve. You just have to use a derived table that does the calculation (group by) first and then join that (derived table) to the other one, that stores the hierarchical structure.

The derived table to use:

( SELECT userid,
         SUM(amount) AS sele_descendant_amount
  FROM webineh_user_buys 
  GROUP BY userid
  HAVING SUM(amount) >= 1000
) AS buys 

Then we can join it to the other table. You also need to join it twice and that's another issue that may lead to a less efficient query. Unfortunately MySQL doesn't yet have CTEs, so the derived table code has to be duplicated. But you'll get at least a start with this code.

Tested at SQLfiddle:

SELECT
    buys_d.userid, 
    buys_d.sele_descendant_amount, 
    COUNT(*) AS level
FROM
    ( SELECT userid,
             SUM(amount) AS sele_descendant_amount
      FROM webineh_user_buys 
      GROUP BY userid
      HAVING SUM(amount) >= 1000
    ) AS buys_d 
  JOIN
    webineh_prefix_nodes_paths AS users 
      ON  users.descendant_id = buys_d.userid 
  JOIN
    ( SELECT userid,
             SUM(amount) AS sele_descendant_amount
      FROM webineh_user_buys 
      GROUP BY userid
      HAVING SUM(amount) >= 1000
    ) AS buys_a
      ON  users.ancestor_id = buys_a.userid 
GROUP BY
    buys_d.userid, buys_d.sele_descendant_amount ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.