Ordering result over non-trivial expression

Question

I have a posts/comments database that I am unable to order correctly.

I need it to be ordered primarily by its id but if its parent_id does not equal its id, it is placed after its parent and also these children would ordered by id.

Here is my current database.

CREATE TABLE `questions` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) NOT NULL,
  `entry_type` varchar(8) NOT NULL,
  `entry_content` varchar(1024) NOT NULL,
  `entry_poster_id` varchar(10) NOT NULL,
  `entry_status` varchar(1) NOT NULL,
  `entry_score` varchar(10) NOT NULL,
  `time_posted` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `questions`
--

INSERT INTO `questions` VALUES(1, 1, 'question', 'How do I does SQL?', 'CodyC', '0', '2', '1308641965');
INSERT INTO `questions` VALUES(2, 1, 'answer', 'Easy, you eat cheese!', 'PatrickS', '0', '-4', '1308641965');
INSERT INTO `questions` VALUES(3, 2, 'comment', 'WTF are you on noobass?!', 'FraserK', '0', '100', '1308641965');
INSERT INTO `questions` VALUES(4, 1, 'answer', 'blah', '5', '0', '0', '1308642204');
INSERT INTO `questions` VALUES(5, 4, 'comment', 'blah2', '4', '0', '0', '1308642247');
INSERT INTO `questions` VALUES(6, 2, '2', '3', '3', '3', '3', '3');

and my current query

SELECT *
FROM questions
WHERE parent_id =1
OR parent_id
IN (
    SELECT id
    FROM questions
    WHERE parent_id =1
    AND parent_id != id
)

how do I order so that order id to that each object comes after its parent, where the id = parent_id means is a base level and has no parent?

Answer 1

This seems to work:

SELECT *
FROM questions
order by case when parent_id != id then parent_id else id end, id;

But it depends whether you want grandchildren before children etc. Your question doesn't specify.

However, if you use this technique you can make your ordering term(s) as complicated as you like - it doesn't need to be a selected column - just make up what you need.

Answer 2

Looks a bit complicated with mysql, but you can use PHP for it. Use recursive function. This will be easy to handle.

here is a function from code bank. It simply creates a unorder list tree. You can modify it to suit your requirments

function output_lis_pages($parentID = 0)
{
    $stack = array(); //create a stack for our <li>'s 

    $arr = array();
    $sql = "select pageid, pagetitle, pagelink, parentid
        from pages
        where parentid =  $parentID
        order by orderid";

    $crs = mysql_query($sql);

    if(mysql_num_rows($crs)==0)
    {
            // no child menu exists for this page
            return false;
    }   
    else
    {
            while($crow = mysql_fetch_array($crs))
            {
                $arr [] =  array(
                    'pagetitle'=> stripslashes($crow["pagetitle"]),
                    'pagelink'=> $crow["pagelink"],
                    'parentid'=>$crow["parentid"],
                    'pageid'=>$crow["pageid"]
                    );
            }
    }

    foreach($arr as $a)
    { 
            $str = '';
                    //if the item's parent matches the parentID we're outputting...
            if($a['parentid']==$parentID)
            { 
                if($a['pagelink']=="")
                        $tmplink = "page.php?pageid=".$a['pageid'];
                else
                        $tmplink = $a['pagelink'];


                $str.='<li><a href="'.$tmplink.'">'.$a['pagetitle']."</a>";

                $subStr = output_lis_pages($a['pageid']);

                if($subStr){
                        $str.="\n".'<ul>'.$subStr.'</ul>'."\n";
                }

                $str.='</li>'."\n";
                $stack[] = $str;
            }
    }
    //If we have <li>'s return a string 
    if(count($stack)>0)
    {
            return  join("\n",$stack);
    }

    //If no <li>'s in the stack, return false 
    return false;
}

Answer 3

    SELECT *
         , CASE WHEN parent_id = 1 THEN id ELSE parent_id END AS sort_level
    FROM questions
    WHERE parent_id = 1
       OR parent_id
          IN (
              SELECT id
              FROM questions
              WHERE parent_id = 1
                AND parent_id != id
             )
    ORDER BY sort_level 
           , id

Answer 4

You've run into the old bugbear of relational database systems. They aren't fun to work with when your data is hierarchic. You have the issue of trying to produce what is really a particular walk of a graph from database records. That is tough without recursive features in your SQL dialect. Here is a link that might help: http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

See also, on StackOverflow: What are the options for storing hierarchical data in a relational database?

Answer 5

looking into your question and reading your comment - "i need to get ONE specific question, with ALL the answers and comments", I think you are looking to show every question followed by its answer followed by its comments. Right?

And if so, this is your query:

SELECT `id`,
(CASE 
    WHEN `entry_type` = 'question' THEN CONCAT(`id`, '-', `parent_id`)
    WHEN `entry_type` = 'answer' THEN CONCAT(`id`, '-', `parent_id`)
    WHEN `entry_type` = 'comment' THEN CONCAT(`parent_id`, '-', `id`)
END) `sort_order`,
`entry_type`, `entry_content`
FROM `questions`
ORDER BY `sort_order`;

The above query will give you every question, followed by its first answer, followed by the comments to its first answer; then the second answer, followed by the comments to the second answer and so on.

So for the INSERTs that you had given, this will be the output:

+----+------------+------------+--------------------------+
| id | sort_order | entry_type | entry_content            |
+----+------------+------------+--------------------------+
|  1 | 1-1        | question   | How do I does SQL?       |
|  2 | 2-1        | answer     | Easy, you eat cheese!    |
|  3 | 2-3        | comment    | WTF are you on noobass?! |
|  6 | 2-6        | comment    | 3                        |
|  4 | 4-1        | answer     | blah                     |
|  5 | 4-5        | comment    | blah2                    |
+----+------------+------------+--------------------------+

Hope it helps.

EDIT: Updated query to fetch answers and comments for only ONE question

SELECT `id`, 
(CASE
    WHEN (`entry_type` IN ('question', 'answer')) THEN `id`
    WHEN `entry_type` = 'comment' THEN `parent_id`
END) `sort_order_1`, 
(CASE
    WHEN (`entry_type` IN ('question', 'answer')) THEN `parent_id`
    WHEN `entry_type` = 'comment' THEN `id`
END) `sort_order_2`, 
(CASE
    WHEN (`entry_type` IN ('question', 'answer')) THEN `parent_id`
    WHEN `entry_type` = 'comment' THEN (SELECT `Q1`.`parent_id` FROM `questions` `Q1` WHERE `Q1`.`id` = `Q`.`parent_id`)
END) `question_id`,
`entry_type`, `entry_content` 
FROM `questions` `Q` 
HAVING `question_id` = 1 
ORDER BY `sort_order_1`, `sort_order_2`;

OUTPUT:

+----+--------------+--------------+-------------+------------+--------------------------+
| id | sort_order_1 | sort_order_2 | question_id | entry_type | entry_content            |
+----+--------------+--------------+-------------+------------+--------------------------+
|  1 | 1            | 1            |           1 | question   | How do I does SQL?       |
|  2 | 2            | 1            |           1 | answer     | Easy, you eat cheese!    |
|  3 | 2            | 3            |           1 | comment    | WTF are you on noobass?! |
|  6 | 2            | 6            |           1 | comment    | 3                        |
|  4 | 4            | 1            |           1 | answer     | blah                     |
|  5 | 4            | 5            |           1 | comment    | blah2                    |
+----+--------------+--------------+-------------+------------+--------------------------+

You can change the HAVING part to fetch answers and comments for a specific question. Hope this helps!

EDIT 2: another possible implementation might be (but I think it might have some performance implications for large tables):

SELECT `a`.`id` AS `question_id`, `a`.`entry_content` AS `question`,
    `b`.`id` AS `answer_id`, `b`.`entry_content` AS `answer`,
    `c`.`id` AS `comment_id`, `c`.`entry_content` AS `comment`
FROM `questions` `a`
LEFT JOIN `questions` `b` ON (`a`.`id` = `b`.`parent_id` AND `b`.`entry_type` = 'answer')
LEFT JOIN `questions` `c` ON (`b`.`id` = `c`.`parent_id` AND `c`.`entry_type` = 'comment')
WHERE `a`.`entry_type` = 'question'
AND `a`.`id` = 1
ORDER BY `a`.`id`, `b`.`id`, `c`.`id`;

OUTPUT:

+----+--------------------+------+-----------------------+------+--------------------------+
| id | question           | id   | answer                | id   | comment                  |
+----+--------------------+------+-----------------------+------+--------------------------+
|  1 | How do I does SQL? |    2 | Easy, you eat cheese! |    3 | WTF are you on noobass?! |
|  1 | How do I does SQL? |    2 | Easy, you eat cheese! |    6 | 3                        |
|  1 | How do I does SQL? |    4 | blah                  |    5 | blah2                    |
+----+--------------------+------+-----------------------+------+--------------------------+

Answer 6

After a week of trying i could not get it to work with the query, so i decided just to do it in PHP, this will also reduce load off the MySQL engine. Here is my php for anyone that wishes to reference it.

$question_id = $database->escape_string($question_id); //escape input
$q = "SELECT * FROM questions WHERE parent_id = $question_id OR parent_id IN (SELECT id FROM questions  WHERE parent_id = $question_id AND parent_id != id) ORDER BY parent_id , id";
$database->dbquery($q);//query the DB
while($row = $database->result->fetch_assoc()){//Process results to standard array.
    //other irrelevant stuff happens here
    $unsorted[] = $row;
}
$question = array_shift($unsorted);//take the question off the array
$sorted[] = $question;//add it to the start of the sorted array
$qusetion_id = $question['id'];
foreach($unsorted as $row){//this creates a multidimensional hierarchy of the answers->comments
    if($row['parent_id'] == $question_id){//if its an answer
        $sorted_multi[$row['id']] = array();//create a new answer sub-array
        $sorted_multi[$row['id']][] = $row;//append it
    }else{
        $sorted_multi[$row['parent_id']][] = $row;//append the answer to the correct sub-array
    }
}
foreach($sorted_multi as $temp){//converts the multidimensional into a single dimension appending it to the sorted array.
    foreach($temp as $row){
        $sorted[] = $row;
    }
}

Tedious yes, but it works out better in the end because of other unforeseen processing that needs to be done post-mysql.

Thanks for all the responses though :):):)

Answer 7

Simply use the "ORDER BY" clause to select the ordering you want!

SELECT *
    FROM questions
    WHERE parent_id =1
    OR parent_id
    IN (
        SELECT id
        FROM questions
        WHERE parent_id =1
        AND parent_id != id
    )
    ORDER BY Parent_id , id