2

in local env (MariaDB 10.2.10) I'm using function 'no_of_descendants' to count how many descendants has current user:

DELIMITER //

CREATE FUNCTION no_of_descendants (f_id INT)
RETURNS INTEGER
BEGIN
DECLARE numberOf INT;

WITH RECURSIVE descendant_users(id,parent_id) AS
(
    SELECT id,parent_id
    FROM app_users
    WHERE id = f_id
    UNION
    Select sub.id, sub.parent_id
    FROM app_users sub, descendant_users au
    WHERE  sub.parent_id = au.id
)
select count(*) INTO numberOf from descendant_users;

RETURN numberOf;
END; //

DELIMITER ;

It works perfect, but on client's server, there is MySQL in version 5.7, which don't support WITH RECURSIVE statement. I can't update it to 8.0, also I cant install other tools. It will be possible to remake this query in a way that will be runnable with MySQL v5.7?

ucMedia
  • 4,105
  • 4
  • 38
  • 46
jdoe
  • 161
  • 3
  • 14
  • 1
    MySQL v5.7 and below are not particularly great at handling hierarchical data. If the maximum number of child nodes is fixed, then you can use a series of self left joins. If not, then you have to use a stored procedure or migrate to the nested set model. I linked duplicate topics that provide solutions to all of the above. – Shadow May 18 '18 at 09:49

0 Answers0