Question

Could someone provide a "Best Questions"-Plugin example?

Update:

Like the guide in this post (http://www.question2answer.org/qa/626/most-popular-questions-tab#a630) I tried to create a best question feature.
The folllowing sqlstatement works (SQL Client only) and all question are sorted by up and downvotes.

But in my q2a webpage the results are not (!) sorted by up and downvotes.

Any Ideas?

qa-db-selects.php
       function qa_db_best_qs_selectspec($voteuserid, $start, $categoryslug=null, $createip=null, $hidden=false, $fullanswers=false, $count=QA_DB_RETRIEVE_QS_AS)
        {
                $selectspec=qa_db_posts_basic_selectspec($voteuserid, null);

                $selectspec['source'].=" JOIN (SELECT postid FROM ^posts WHERE ".(isset($categoryslug) ? "categoryid=(SELECT categoryid FROM ^categories WHERE tags=$ LIMIT 1) AND " : "")."type=$ ORDER BY CONVERT(^posts.upvotes, SIGNED) - CONVERT(^posts.downvotes, SIGNED) DESC  LIMIT #,#) y ON ^posts.postid=y.postid";

                if (isset($categoryslug))
                        $selectspec['arguments'][]=$categoryslug;

                array_push($selectspec['arguments'], $hidden ? 'Q_HIDDEN' : 'Q', $start, $count);

                $selectspec['sortdesc']='created';

                return $selectspec;

        }

qa-page-home.php

                 case 'bestquestions':
                        $categoryqcount=true;

                        if (!qa_home_load_ifcategory(
                                'page_size_qs', 'feed_for_questions', 'cache_qcount', 'main/most_answered_qs_title', 'main/no_questions_found', 'main/most_answered_qs_title', 'main/no_questions_found',
                                qa_db_best_qs_selectspec($qa_login_userid, $qa_start, $categoryslug)
                        ))
                                return $qa_content;

                        if (isset($categoryid)) {
                                $count=$categories[$categoryid]['qcount'];
                                $suggest=qa_html_suggest_qs_tags(qa_using_tags());

                        } else
                                $suggest=qa_html_suggest_ask($categoryid);
                        break;

 

BTW: I now that this sql statement will be slow for big site. In case of my small site I don't care about ;)

Thx

Oliver

Answer 1

The problem is that the query results are sorted afterwards in PHP by the 'created' column, because of this line:

$selectspec['sortdesc']='created';

And also they're probably sorted again by other code in qa-page-home.php.

So instead I suggest doing this with a page plug-in (so others can later enjoy it) and modifying the selectspec to do something like this:

$selectspec['columns']['netvotes']='CONVERT(^posts.upvotes, SIGNED) - CONVERT(^posts.downvotes, SIGNED)';

And then use ORDER BY netvotes in the other part of your query, and set $selectspec['sortdesc']='netvotes';

The process_request(...) method of your page plug-in should do exactly the same sort of things as a qa-page-*.php file. Other methods are hopefully clear enough, if you look at the example page plugin.

Of course plugins will be properly documented shortly.

Comments

I agree that a plugin would be the best way to share. I am looking forward to create a own plugin. But for own I do not unterstand how to do. Basic plugins seems to be easy. Perhaps there should be a sample plugin with some sql in future (Perhaps a best question feature ;) ).  

In my testexample above I added the following line:
$selectspec['sortdesc']='netvotes';
And change my Order Clause to "ORDER BY netvotes".

My Q2A Webpage shows me an error: Error 1054: Unknown column 'netvotes' in 'order clause'

When I change back the Order Clause to [CONVERT(^posts.upvotes, SIGNED) - CONVERT(^posts.downvotes, SIGNED)] my result page is not sorted in the way I want.

---

Hi I did the trick,

you have to change one line in qa-page-home.php to get "rid" of the php order functions.

qa-include/qa-page-home.php
<        $questions=qa_any_sort_and_dedupe($questions);
>        if ($qa_request_0_lc != 'bestquestions')
>               $questions=qa_any_sort_and_dedupe($questions);

Now I habe a "best question" feature.

Perhaps it is an idea to implement this diretly in the main source, because a lot of people asked for this feature?