Since today (November 10 2006) I can't search for unsolved problems anymore. When I select -Unsolved- I just get 0 problems as a result (no matter what the other settings are) and I'm pretty sure I've left some problems unsolved
{Posted by Erwin de Groot}
-Problem Search- problem
I have the same problem
and some more info:
This night someone added new problems which seem to appear when I search for unsovled problems...
{Posted by tom111}
This night someone added new problems which seem to appear when I search for unsovled problems...
{Posted by tom111}
thanks for bringing this to my attention. turns out the solution isn't trivial. i'm going to have to think about it.
for those curious, here's the technical issue:
up to now, i've been manually constructing the list of problems for unsolved or whatever by doing one SQL query for the list of what has been solved, then another to select for id's not in the first list. the problem is that this list is now too large, and something craps out.
the real way to do this query is with a subselect (something not available when i first wrote this part.) however, a bug in mysql makes the query take minutes instead of a fraction of a second like it should:
http://bugs.mysql.com/bug.php?id=9021
so, i could possibly break things up into multiple query parts, but it's a big hack and a pain.
adum
{Posted by admin}
for those curious, here's the technical issue:
up to now, i've been manually constructing the list of problems for unsolved or whatever by doing one SQL query for the list of what has been solved, then another to select for id's not in the first list. the problem is that this list is now too large, and something craps out.
the real way to do this query is with a subselect (something not available when i first wrote this part.) however, a bug in mysql makes the query take minutes instead of a fraction of a second like it should:
http://bugs.mysql.com/bug.php?id=9021
so, i could possibly break things up into multiple query parts, but it's a big hack and a pain.
adum
{Posted by admin}
Obviously I don't know the details of your schema, but could you do something like:
SELECT * FROM problems LEFT JOIN user_problems ON problems.id = user_problems.problem_id WHERE user_problems.user_id IS NULL OR (user_problems.user_id = AND NOT user_problems.solved)
Edit: Oops, that should have been:
SELECT * FROM problems LEFT JOIN user_problems ON problems.id = user_problems.problem_id AND user_problems.user_id = WHERE user_problems.solved IS NULL OR user_problems.solved = 0
{Posted by oden}
SELECT * FROM problems LEFT JOIN user_problems ON problems.id = user_problems.problem_id WHERE user_problems.user_id IS NULL OR (user_problems.user_id = AND NOT user_problems.solved)
Edit: Oops, that should have been:
SELECT * FROM problems LEFT JOIN user_problems ON problems.id = user_problems.problem_id AND user_problems.user_id = WHERE user_problems.solved IS NULL OR user_problems.solved = 0
{Posted by oden}
oden -- thanks for the left join suggestion. i've never used that before. i had a little trouble getting it to work properly, but i think i've found a way to speed up the subselect so that it's fast enough to be useable, and that's what's live now. hopefully this works for everyone...
adum
{Posted by admin}
adum
{Posted by admin}