Mysql filter query based on favorited tags - problematic issue

Question

I'm looking at updating function qa_db_qs_selectspec so that questions/answers on lists can be displayed that reflect or are filtered according to the user's favorite tags.

The problem I'm having is how do I use the query in the function to test if any given post has any of the tags favourited by the user? The tags appear comma separated in the qa_posts table, which makes it difficult to do a comparison in the mysql...

I've played around with the following with no joy so far:

            $selectspec['source'].=" JOIN (SELECT postid FROM ^posts WHERE tags IN (SELECT word FROM ^words WHERE w.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 ";

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 :)