2

I have two tables articles and topics and I am using MySQL 5.6

Articles

id    topic(fk on Topics.id)   featured
1     1                        1
2     2                        0
3     3                        1
4     4                        1
5     5                        1

Topics

id    parent(fk on Topics.id)
1     NULL
2     1
3     2
4     3
5     NULL

My question is, I need to get all featured articles in specific root topic

i.e when I query about featured articles in topic id 1, I get article 1, 3, 4

  • how your tables relate with each other.. – Shahid Iqbal Feb 12 '14 at 12:09
  • Articles.topic points to Topics.id ; Topics.parent points to Topics.id – Ayman Feb 12 '14 at 12:17
  • Get your hands on Joe Celko's "Trees and Hierarchies in SQL" (also a chapter of his "SQL for Smarties" book). Also, you could google Vadim Tropashko who has written about this topic. HTH, Paul... – Vérace Feb 12 '14 at 12:55
  • This not possible in MySQL using a single SQL query. You need to write a stored procedure for this (or handle the looping in your application) or change your data model MySQL still doesn't support recursive queries. – a_horse_with_no_name Feb 12 '14 at 17:28
1

MySQL does not feature recursive queries. In fact, Bill Karwin just wrote a blog post about how MySQL is the only major RDBMS without recursive queries. Notwithstanding, what you need is a Stored Procedure that performs the query as a preorder tree traversal using a queue.

YOUR SAMPLE DATA

mysql> drop database if exists ayman;
Query OK, 2 rows affected (0.03 sec)

mysql> create database ayman;
Query OK, 1 row affected (0.00 sec)

mysql> use ayman
Database changed
mysql> create table topics
    -> (id int not null primary key,
    -> parent int default null
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into topics (id) values (1),(5);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into topics (id,parent) values (2,1),(3,2),(4,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table articles
    -> (id int not null,
    -> topic int not null,
    -> featured int not null
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into articles values
    -> (1,1,1),(2,2,0),(3,3,1),(4,4,1),(5,5,1);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Here it is loaded

mysql> select * from articles order by id;
+----+-------+----------+
| id | topic | featured |
+----+-------+----------+
|  1 |     1 |        1 |
|  2 |     2 |        0 |
|  3 |     3 |        1 |
|  4 |     4 |        1 |
|  5 |     5 |        1 |
+----+-------+----------+
5 rows in set (0.00 sec)

mysql> select * from topics order by id;
+----+--------+
| id | parent |
+----+--------+
|  1 |   NULL |
|  2 |      1 |
|  3 |      2 |
|  4 |      2 |
|  5 |   NULL |
+----+--------+
5 rows in set (0.00 sec)

mysql>

STORED PROCEDURE

DELIMITER $$

DROP FUNCTION IF EXISTS `ayman`.`GetFeaturedArticles` $$
CREATE FUNCTION `ayman`.`GetFeaturedArticles` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN

    DECLARE rv,rvlist,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';
    SET queue = GivenID;
    SET queue_length = 1;

    WHILE queue_length > 0 DO
        SET front_id = FORMAT(queue,0);
        IF queue_length = 1 THEN
            SET queue = '';
        ELSE
            SET pos = LOCATE(',',queue) + 1;
            SET q = SUBSTR(queue,pos);
            SET queue = q;
        END IF;
        SET queue_length = queue_length - 1;

        SELECT IFNULL(qc,'') INTO queue_children FROM
        (
            SELECT GROUP_CONCAT(AA.id) qc
            FROM articles AA INNER JOIN topics BB
            ON AA.topic = BB.id
            WHERE BB.parent = front_id
        ) A;
        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                SET rv = queue_children;
            ELSE
                SET rv = CONCAT(rv,',',queue_children);
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;

    SET rvlist = CONCAT(GivenID,',',rv);
    SET rv = '';
    SET @comma = '';

    #
    # Strip Out Non-Featured Articles
    #
    SET @searching = 1;
    WHILE @searching  = 1 DO
        SET @searching = (LOCATE(',',rvlist) > 1);
        SET front_id = FORMAT(rvlist,0);
        SELECT COUNT(1) INTO @ItsFeatured FROM articles WHERE id = front_id AND featured = 1;
        IF @ItsFeatured = 1 THEN
            SET rv = CONCAT(rv,@comma,front_id);
            SET @comma = ',';
        END IF;
        SET pos = LOCATE(',',rvlist) + 1;
        SET q = SUBSTR(rvlist,pos);
        SET rvlist = q;
    END WHILE;

    RETURN rv;

END $$

DELIMITER ;

Here is the execution of it

mysql> select * from articles;
+----+-------+----------+
| id | topic | featured |
+----+-------+----------+
|  1 |     1 |        1 |
|  2 |     2 |        0 |
|  3 |     3 |        1 |
|  4 |     4 |        1 |
|  5 |     5 |        1 |
+----+-------+----------+
5 rows in set (0.00 sec)

mysql> select * from topics;
+----+--------+
| id | parent |
+----+--------+
|  1 |   NULL |
|  5 |   NULL |
|  2 |      1 |
|  3 |      2 |
|  4 |      2 |
+----+--------+
5 rows in set (0.00 sec)

mysql> select GetFeaturedArticles(1);
+------------------------+
| GetFeaturedArticles(1) |
+------------------------+
| 1,3,4                  |
+------------------------+
1 row in set (0.03 sec)

mysql>

I borrowed this code from my earlier post : Find highest level of a hierarchical field: with vs without CTEs

The Stored Procedure first collects all the descendants of a given article's topic hierarchy. Then, it iterates through the return list and strips non-featured articles.

Give it a Try !!!

  • when I executed the query on my machine, it worked the first couple of time, but then It is taking infinite time until MySQL generates an error saying "Lost connection to MySQL server during query 600.495 sec" – Ayman Feb 13 '14 at 15:26
  • How many rows are in the articles and topic tables ? I would suggest added some debugging code to see if there is an infinite loop somewhere. You cannot run a SELECT to display anything since it is a function. You will have to convert it to a Procedure rather than a function and then debug ti. – RolandoMySQLDBA Feb 13 '14 at 15:35
  • Actually it doesn't have much records (4 in the articles and 5 in the topics), but as I said before, the first couple of times it worked correctly. It seems I'll have to debug it to locate the error. – Ayman Feb 13 '14 at 15:44

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.