[OSM-Photos] "There are no pictures to moderate"?

[John McKerrell]

The problem is that it assigns photos to you by retrieving a batch of  
photos and then trying to assign each one to you in turn, if you've  
already moderated it then it tries the next one, currently it's  
pulling a batch of 30 but when you've moderated so many photos the  
chances are high that the entire batch of 30 will be photos you've  
already moderated. Note that this is because those photos are still in  
moderation status, if other people were moderating them and pushing  
them through to available this would happen less (and I'll be the  
first to admin I haven't been moderating much recently).

The query doing this could be improved but it's a fairly complex  
query, though I haven't really sat down and tried to work it out yet.  
I'll start doing that now, anyone technically minded feel free to chip  
in a good solution. Basically we have the photos table and the  
moderators table with the following fields (amongst others):

photos: id, status (moderation, unavailable, ...), filename, etc...
moderators: id, user_id, photo_id, status (pending, safe, unsafe),  
etc...

Currently I just do:
	SELECT * FROM photos WHERE status = 'moderation';

Which can bring back photos that you've already moderated. I need to  
change this to "get me photos that haven't been moderated by me", but  
that query is complicated by the fact that other people *might* have  
moderated them, so it's more like "get me photos with no attached  
moderator or where any of the moderators are not me". This makes it  
more complicated than a simple join, I think.

	SELECT DISTINCT p.id FROM photos p LEFT OUTER JOIN moderators m ON  
m.photo_id = p.id WHERE p.status = 'moderation' AND ( m.id IS NULL OR  
m.user_id <> ?)

I didn't think that would work because it will always link to  
moderators if there's any available but I've realised that so long as  
I put the DISTINCT bit in to make sure that it's not weighted towards  
photos that are already part moderated, I should be ok. Anyone else  
like to confirm or disagree? I did another version of the query using  
subselects that I thought would be slower, and does appear to be. That  
query is getting slightly fewer results which is a little confusing  
but it's probably something simple:

	SELECT DISTINCT p.id FROM photos p WHERE status = 'moderation' AND id  
NOT IN ( SELECT photo_id FROM moderators WHERE status = 'pending' AND  
user_id = 1 );

John

On 31 Oct 2009, at 09:40, malenki wrote:

> Right now as I lowered my counter to ~260 images quite often I get the
> message mentioned in subject.
> Also mostly I am served 1-3 pictures instead of the ~10 it used to be.
> Is this just for having enlonged the fun to moderate the  
> photographs? ;)
>
> regards
> malenki
>
>
> _______________________________________________
> Photos mailing list
> Photos at openstreetmap.org
> http://lists.openstreetmap.org/listinfo/photos