goproblems.com

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}

Hmm, I just logged in with my wife's account and everything seems to work just fine. But when I log in with my username (edegroot) it still doesn't work. Does anybody else have this problem?

{Posted by Erwin de Groot}

and some more info:
This night someone added new problems which seem to appear when I search for unsovled problems...

{Posted by tom111}

I get the same issue too (with "Untried problems" too). Started around the same date, and problems added since this date *do* show up, but only those ones.

{Posted by postpostglock}

Ditto to all the above

{Posted by BostonGo}

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}

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}

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}

Thanks Adum!

{Posted by Erwin de Groot}

Thanks, works great now!

{Posted by postpostglock}