Geek Answers Handbook

Dynamic SQL order support

Hey guys, I looked around, but had no luck solving this problem.

I have a message / comment database that I cannot order correctly.

I need him to be ordered first by his identifier, but if his parent_id is not equal to his id, he is placed after his parent, and also these children will be 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 request

 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 the order identifier so that every object comes after its parent, where id = parent_id means the base level and has no parent!

Thanks in advance.

Fraser

+4
source share
7 answers

It works:

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

But it depends on whether you want grandchildren in front of children, etc. Your question is not indicated.

However, if you use this method, you can make your collation term (s) as complex as you like - it does not have to be the selected column - just create what you need.

+1
source

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

here is a function from the code bank. It simply creates a list tree of unordered items. You can change it according to your requirements.

 function output_lis_pages($parentID = 0) { $stack = array(); //create a stack for our <li> $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 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> return a string if(count($stack)>0) { return join("\n",$stack); } //If no <li> in the stack, return false return false; }
0
source 
  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
0
source

You have encountered an old relational database system error. It is not very convenient to work with their data when your data is hierarchical. You have a problem trying to create what really represents a particular plot move from database records. This is difficult without recursive functions in your SQL dialect. Here is a link that might help: http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

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

0
source

looking into your question and reading your comment - “I need to get ONE specific question, with ALL answers and comments”, I think you want to show each question, followed by his answer, and then his comments. Right?

And if so, this is your request:

 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 request will give you every question, followed by his first answer, and then comments on his first answer; then the second answer, then comments on the second answer, etc.

So, for the INSERT you entered, 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 this helps.

EDIT : Updated request for answers and comments for ONE question only

 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 get answers and comments on a specific issue. Hope this helps!

EDIT 2 : another possible implementation is possible (but I think this may have some 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 | +----+--------------------+------+-----------------------+------+--------------------------+
0
source

After a week of trying, I could not get her to work with the query, so I decided to just do it in PHP, this will also reduce the load on the MySQL engine. Here is my php for those who want 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; } }

Pensive yes, but in the end it works better due to other unexpected processing that should be done after mysql.

Thanks for all the answers though :) :) :)

0
source 
 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
-1
source

More articles:


All Articles