Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+6 votes
849 views
in Plugins by
edited by

Hello,

I am working on a plugin and I'm stuck.

I want to remodel the related Questions Plugin to a Related Ads Plugin

How it works is this.

Admins Create a category called Ads or Any name

Questions that are ads will be put under that category.

When a normal question is viewed, A query will be done to select all questions that are related to the the question being viewed from the Ads Category.

This works like a keyword ads system which will later be improved .

My problem is,

How do I modify the the related questions query seen in qa-db-selects.php

to search for title words and tags related to a particular question but from a specific category say Ads?

The full related questions function is below

    function qa_db_related_qs_selectspec($voteuserid, $questionid, $count=null)
/*
    Return the selectspec to retrieve the most closely related questions to $questionid, with the corresponding vote
    made by $voteuserid (if not null). Return $count (if null, a default is used) questions. This works by looking for
    other questions which have title words, tag words or an (exact) category in common.
*/
    {
        $count=isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

        $selectspec=qa_db_posts_basic_selectspec($voteuserid);

        $selectspec['columns'][]='score';

        // added LOG(postid)/1000000 here to ensure ordering is deterministic even if several posts have same score

//Query to be modified is this

        $selectspec['source'].=" JOIN (SELECT postid, SUM(score)+LOG(postid)/1000000 AS score FROM ((SELECT ^titlewords.postid, LOG(#/titlecount) AS score FROM ^titlewords JOIN ^words ON ^titlewords.wordid=^words.wordid JOIN ^titlewords AS source ON ^titlewords.wordid=source.wordid WHERE source.postid=# AND titlecount<#) UNION ALL (SELECT ^posttags.postid, 2*LOG(#/tagcount) AS score FROM ^posttags JOIN ^words ON ^posttags.wordid=^words.wordid JOIN ^posttags AS source ON ^posttags.wordid=source.wordid WHERE source.postid=# AND tagcount<#) UNION ALL (SELECT ^posts.postid, LOG(#/^categories.qcount) FROM ^posts JOIN ^categories ON ^posts.categoryid=^categories.categoryid AND ^posts.type='Q' WHERE ^categories.categoryid=(SELECT categoryid FROM ^posts WHERE postid=#) AND ^categories.qcount<#)) x WHERE postid!=# GROUP BY postid ORDER BY score DESC LIMIT #) y ON ^posts.postid=y.postid";

        array_push($selectspec['arguments'], QA_IGNORED_WORDS_FREQ, $questionid, QA_IGNORED_WORDS_FREQ, QA_IGNORED_WORDS_FREQ,
            $questionid, QA_IGNORED_WORDS_FREQ, QA_IGNORED_WORDS_FREQ, $questionid, QA_IGNORED_WORDS_FREQ, $questionid, $count);

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

        return $selectspec;
    }


Any help will be appreciated. Im now learning q2a system

 

Q2A version: 1.7.1
by
Any help on modifying? the   query?
by
Your Idea is really nice but I am not much into coding :/ . Anyways all the best & if you developed please let me know via the comments or message.

Please log in or register to answer this question.

...