Filtering/controlling questions on homepage based on tags

Question

Hi

I would like to exclude questions based on the tags favorited by the user. I've updated the q_list($q_list) function as follows to delete questions that I don't need:

                    if (qa_is_logged_in() && $this->template != "tag") {
                        $delete = 1;
                        foreach ($question['q_tags'] as $indice=>$tag) {
                            if ($this->rec_in_array(strip_tags($tag),$favorites)) {
                                $thispost=@$postinfo[$question['raw']['postid']];
                                if (isset($thispost)) {
                                    $text=qa_viewer_text($thispost['content'], $thispost['format'], array('blockwordspreg' => $blockwordspreg));
                                    $text=qa_shorten_string_line($text, $maxlength);
                                    $q_list['qs'][$index]['content']='<SPAN >'.qa_html($text).'***</SPAN>';
                                }
                                $delete = 0;
                                break;
                            }
                        }

                        // If tags do not appear on user topics, exclude from list
                        if ($delete) unset($q_list['qs'][$index]);
                        }
                    }
                }

It sees to work to a degree where questions are now filtered - however pagination has stopped working properly as it sees to keep count of all questions including those excluded.

The number of questions that come through become very variable on pages as I scroll down (on both infinite ajax or page numbers depending on whichever I look at) - 2 on some pages, 4 on other pages etc

Can anyone help me to figure out how to fix this?

Answer 1

OK I've finally cracked it - here's the solution for anyone else who needs this key functionality.

You need to update qa-include/db/selects.php. 

Search for the function qa_db_qs_selectspec and update the query line at around line 325 to:

            $selectspec['source'].=" JOIN (SELECT postid FROM ^posts WHERE postid IN (SELECT postid FROM ^posttags pt WHERE pt.wordid IN (SELECT entityid FROM ^userfavorites uf WHERE uf.userid='".qa_get_logged_in_userid()."')) AND ".
            qa_db_categoryslugs_sql_args($categoryslugs, $selectspec['arguments']).
            (isset($createip) ? "createip=INET_ATON($) AND " : "")."type=$ ".$sortsql." LIMIT #,#) y ON ^posts.postid=y.postid ";

A similar change to the function below this one for unanswered questions can easily be made too:

        $selectspec['source'].=" JOIN (SELECT postid FROM ^posts WHERE 
        postid IN (SELECT postid FROM ^posttags pt WHERE pt.wordid IN (SELECT entityid FROM ^userfavorites uf WHERE uf.userid='".qa_get_logged_in_userid()."')) 
        AND " . qa_db_categoryslugs_sql_args($categoryslugs, $selectspec['arguments'])."type=$ AND ".$bysql.
        " AND closedbyid IS NULL ORDER BY ^posts.created DESC LIMIT #,#) y ON ^posts.postid=y.postid"; 

 
If any mysql guys can improve on the efficiency of the query feel free to let me know. I'm not the best in mysql as you can probably see :)

Comments

Good job!
it isn't displaying  favorited questions  in "All activity" page. Is it possible to implement this?

And more importantly, how to make users favorite tags?? Have you done something about this? Like after registration, message shows up saying that you need to favorite n number of tags.