0

This table is a questions and answers CMS and i want to know how data is fetched from these tables.

+------+----------+-------+
|  id  | parentid | type  |
+------+----------+-------+
|  1   |     0    |   Q   |
+------+----------+-------+
|  2   |     1    |   A   |
+------+----------+-------+
|  3   |     0    |   Q   |
+------+----------+-------+
|  4   |     2    |   C   |
+------+----------+-------+
|  5   |     2    |   C   |
+------+----------+-------+
|  6   |     1    |   A   |
+------+----------+-------+
|  7   |     3    |   C   |
+------+----------+-------+
|  8   |     1    |   C   |
+------+----------+-------+
|  9   |     0    |   Q   |
+------+----------+-------+
|  10  |     9    |   C   |
+------+----------+-------+
|  11  |     9    |   A   |
+------+----------+-------+
|  12  |    11    |   C   |
+------+----------+-------+

For example, the results like below.

+------+----------+-------+
|  id  | parentid | type  |
+------+----------+-------+
|  1   |     0    |   Q   |
+------+----------+-------+
|  8   |     1    |   C   |
+------+----------+-------+
|  2   |     1    |   A   |
+------+----------+-------+
|  4   |     2    |   C   |
+------+----------+-------+
|  5   |     2    |   C   |
+------+----------+-------+
|  6   |     1    |   A   |
+------+----------+-------+

This query are not results sorted as above.and in many tutorials advised that the JOIN is better than it used to be for this table.

SELECT * FROM qa_posts WHERE postid=1
UNION
SELECT * FROM qa_posts WHERE parentid=1
UNION
SELECT * FROM qa_posts WHERE parentid IN (SELECT postid parentid FROM qa_posts WHERE parentid=1)
ORDER BY parentid;
  • How many levels does your hierarchy have? Is i arbitrary or is there a well defined maximum? – Vérace Aug 27 '14 at 19:43
  • Maximum of two levels i.stack.imgur.com/GAN5d.jpg – majid Aug 28 '14 at 7:30
  • If that's the case, you could (don't have time to test or anything) maybe try and reorganise your table and include a grandparent id - that might make your queries easier? RDBMSs are not optimal for hierarchical queries. – Vérace Aug 28 '14 at 7:40
  • This table has many problems, I just want to know how the above results are fetchedThis table is CMS question2answer. – majid Aug 28 '14 at 15:36
  • You should lookover my stored procedures for preorder tree traversal ( dba.stackexchange.com/a/7161/877 ) and see if it gives you any ideas. I use a queue. If you change the paradigm to a stack, it could work. – RolandoMySQLDBA Aug 28 '14 at 20:19
2

Your structure i commonly known as an Adjacency List (should perhaps be Set) Model. If you don't want to change the structure of your model, you basically have to do something similar to what you are currently doing. You can rewrite your query using joins like:

SELECT q1.* FROM qa_posts q1
WHERE q1.postid=1
UNION
SELECT q2.* FROM qa_posts q1
            JOIN qa_posts q2
                on q1.postid=q2.parentid
WHERE q1.postid=1
UNION
SELECT q3.* FROM qa_posts q1
            JOIN qa_posts q2
                on q1.postid=q2.parentid
            JOIN qa_posts q3
                on q2.postid=q3.parentid
WHERE q1.postid=1;

If you are willing to change the model you can search for Nested Set (example http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/), Materialized Path (example http://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/) and Transitive Closure Table (at the end of http://www.slideshare.net/billkarwin/models-for-hierarchical-data, at the beginning the other two models are described)

Edit: Adding DFS with different weights according to type:

select postid, parentid, type 
from (
    SELECT q1.*     
        , lpad(q1.parentid,8,case q1.type when 'Q' then 'a' 
                                          when 'C' then 'b' 
                                          when 'A' then 'c' 
                             end)         
        || lpad(q1.postid,8,case q1.type when 'Q' then 'a' 
                                         when 'C' then 'b' 
                                         when 'A' then 'c' 
                             end) as padded_path 
    FROM qa_posts q1 
    WHERE q1.postid=1 
    UNION 
    SELECT q2.*     
       , lpad(q1.parentid,8,case q1.type when 'Q' then 'a' 
                                         when 'C' then 'b' 
                                         when 'A' then 'c' 
                            end)         
        || lpad(q1.postid,8,case q1.type when 'Q' then 'a' 
                                         when 'C' then 'b' 
                                         when 'A' then 'c' 
                            end)         
        || lpad(q2.postid,8,case q2.type when 'Q' then 'a' 
                                         when 'C' then 'b' 
                                         when 'A' then 'c' 
                            end) as padded_path 
    FROM qa_posts q1 
    JOIN qa_posts q2     
        on q1.postid=q2.parentid 
    WHERE q1.postid=1 
    UNION 
    SELECT q3.*     
         , lpad(q1.parentid,8,case q1.type when 'Q' then 'a' 
                                           when 'C' then 'b' 
                                           when 'A' then 'c' 
                              end)         
         || lpad(q1.postid,8,case q1.type when 'Q' then 'a' 
                                          when 'C' then 'b' 
                                          when 'A' then 'c' 
                              end)         
         || lpad(q2.postid,8,case q2.type when 'Q' then 'a' 
                                          when 'C' then 'b' 
                                          when 'A' then 'c' 
                              end)         
         || lpad(q3.postid,8,case q3.type when 'Q' then 'a' 
                                          when 'C' then 'b' 
                                          when 'A' then 'c' 
                              end) as padded_path 
    FROM qa_posts q1 
    JOIN qa_posts q2     
        on q1.postid=q2.parentid 
    JOIN qa_posts q3     
        on q2.postid=q3.parentid 
    WHERE q1.postid=1
) as x 
order by padded_path;

+--------+----------+------+
| postid | parentid | type |
+--------+----------+------+
|      1 |        0 | Q    |
|      8 |        1 | C    |
|      2 |        1 | A    |
|      4 |        2 | C    |
|      5 |        2 | C    |
|      6 |        1 | A    |
+--------+----------+------+
6 rows in set (0.00 sec)

Edit: The other Questions result in (padded_path included):

+--------+----------+------+--------------------------+
| postid | parentid | type | padded_path              |
+--------+----------+------+--------------------------+
|      3 |        0 | Q    | aaaaaaa0aaaaaaa3         |
|      7 |        3 | C    | aaaaaaa0aaaaaaa3bbbbbbb7 |
+--------+----------+------+--------------------------+
2 rows in set (0.00 sec)


+--------+----------+------+----------------------------------+
| postid | parentid | type | padded_path                      |
+--------+----------+------+----------------------------------+
|      9 |        0 | Q    | aaaaaaa0aaaaaaa9                 |
|     10 |        9 | C    | aaaaaaa0aaaaaaa9bbbbbb10         |
|     11 |        9 | A    | aaaaaaa0aaaaaaa9cccccc11         |
|     12 |       11 | C    | aaaaaaa0aaaaaaa9cccccc11bbbbbb12 |
+--------+----------+------+----------------------------------+
4 rows in set (0.00 sec)

Changing the predicate q1.postid=1 to q1.type='Q' results in:

+--------+----------+------+----------------------------------+
| postid | parentid | type | padded_path                      |
+--------+----------+------+----------------------------------+
|      1 |        0 | Q    | aaaaaaa0aaaaaaa1                 |
|      8 |        1 | C    | aaaaaaa0aaaaaaa1bbbbbbb8         |
|      2 |        1 | A    | aaaaaaa0aaaaaaa1ccccccc2         |
|      4 |        2 | C    | aaaaaaa0aaaaaaa1ccccccc2bbbbbbb4 |
|      5 |        2 | C    | aaaaaaa0aaaaaaa1ccccccc2bbbbbbb5 |
|      6 |        1 | A    | aaaaaaa0aaaaaaa1ccccccc6         |
|      3 |        0 | Q    | aaaaaaa0aaaaaaa3                 |
|      7 |        3 | C    | aaaaaaa0aaaaaaa3bbbbbbb7         |
|      9 |        0 | Q    | aaaaaaa0aaaaaaa9                 |
|     10 |        9 | C    | aaaaaaa0aaaaaaa9bbbbbb10         |
|     11 |        9 | A    | aaaaaaa0aaaaaaa9cccccc11         |
|     12 |       11 | C    | aaaaaaa0aaaaaaa9cccccc11bbbbbb12 |
+--------+----------+------+----------------------------------+
12 rows in set (0.00 sec)
  • This query does not work properly and fetch an incomplete results. – majid Aug 28 '14 at 15:32
  • 1
    Your secret is safe with us, what is wrong with it? – Lennart Aug 28 '14 at 16:13
  • I need the results like table2,but it gives an incomplete query results.For example, the results of the field that contains A is not fetch. – majid Aug 28 '14 at 16:47
  • I have updated the query – Lennart Aug 28 '14 at 17:27
  • The main problem is sorting out the results. My query also provided this results . See table2,First question and comments then answer the following and comments next other answer and comments and ... – majid Aug 28 '14 at 18:16

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.