I have 3 simple tables with
votes. I'd like to show all polls ordered by clean count of votes (sometimes either
user_id don't get inserted).
Furthermore I'd like to show a list of users voted, similar to fb (for now my solution is to get that list with new query fetched by ajax call.)
would it be better to create one query to get all results and read them separatelly from array or is it best practice to create separate requests?
My query bellow does get the ordered list but includes the empty or null rows and is very slow for table with >10000 results. any suggestions?
SELECT id, title, pollid, COUNT(pollid) AS clean_count FROM ( SELECT DISTINCT user_id, pollid FROM tbl_votes ) AS tmp_tbl JOIN tbl_polls ON tbl_polls.id = tmp_tbl.pollid WHERE not isnull(tmp_tbl.user_id) GROUP BY pollid ORDER BY clean_count DESC
Code to match results of votes with voters:
SELECT DISTINCT fbid, COALESCE(username, 'User not found.') AS username FROM tbl_votes JOIN tbl_users ON tbl_users.fbid = tbl_votes.user_id WHERE pollid = $band_id AND not isnull(username)