CODEWITHSUNDEEP
mysqlsqlmariadb
Gregory R.
Gregory R.

Reputation: 1935

MySQL, MariaDB: How do I create a child-parent hierarchical recursive query?

I have the following MySQL query:

SELECT * FROM (SELECT * FROM paper ORDER BY id DESC) paper_sorted,
        (SELECT @pv := 26) initialisation
        WHERE find_in_set(id, @pv)
        AND length(@pv := concat(@pv, ',', last_id))
        ORDER BY @pv ASC LIMIT 0,15

http://sqlfiddle.com/#!9/9e2df/24

This works perfectly fine in MySQL; however, I am working with a MariaDB database and this query is not working. Anyway to rewrite this for it to work for both MySQL and MariaDB?

Database schema:

CREATE TABLE paper (
    id INT,
    last_id INT
);

INSERT INTO paper VALUES (19, 0);
INSERT INTO paper VALUES (20, 19);
INSERT INTO paper VALUES (21, 20);
INSERT INTO paper VALUES (22, 21);
INSERT INTO paper VALUES (23, 19);
INSERT INTO paper VALUES (24, 23);
INSERT INTO paper VALUES (25, 23);
INSERT INTO paper VALUES (26, 24);

MariaDB v. 10.3.18; MySQL v. 5.7.27

This SQL query should get all the Parent IDs. As in the example query, @pv variable is 26, and thus gets 26->24->23->19.

However, this is not working for MariaDB. It's only returning one row, which is 26 in MariaDB. How to rewrite this query to work for both MariaDB and MySQL?

Upvotes: 0

Views: 780

Answers (1)

markusjm
markusjm

Reputation: 2562

You should be able to rewrite the query as a Recursive CTE. These were added into MariaDB 10.2 and allow querying hierarchical data structures with SQL.

Something along these lines should work:

WITH RECURSIVE p AS (
    SELECT * FROM paper WHERE id = 26
    UNION
    SELECT c.* FROM paper AS c, p WHERE p.last_id = c.id
) SELECT * FROM p ORDER BY id ASC LIMIT 15;

Edit: If you need the SQL to be backwards compatible with older versions, you can use a stored procedure to achieve the same result.

DELIMITER // ;

CREATE OR REPLACE PROCEDURE p1(IN id_in INT)
BEGIN
    DECLARE i INT DEFAULT id_in;
    CREATE OR REPLACE TEMPORARY TABLE results LIKE paper;

    WHILE i IS NOT NULL DO
          INSERT INTO results SELECT p.id, p.last_id FROM paper AS p WHERE p.id = i;
          SET i = (SELECT r.last_id FROM results AS r WHERE r.id = i LIMIT 1);
    END WHILE;

    SELECT * FROM results;
    DROP TEMPORARY TABLE results;
END;
//

DELIMITER ; //

Upvotes: 1

Related Questions