Beria, Andre, It does, but not exactly that URL. Try this one:
http://toolserver.org/~ntavares/patrimonio/api/api.php?action=statisticsct&format=html&ctscope=user&ctitem=user|n_wlm_ids|n_images&ctcountry=the_Netherlands The column n_wlm_ids is the value Andre is seeking for. Andre, the limitation you ran across was (one of) why ranking.php shouldn't be used.. I found 2 solutions: 1) going through revisions to extract the user of the *first* revision in an image - this is very very heavy, it was implemented and optimized by means of TEMPORARY tables, I still might have the code somewhere; 2) In the end I came up with the following: keep the search base limited and simple as possible (which lets me optimize it my way, instead of relying in inefficient 'commons' VIEWs), which is the statisticsct table. The, the api module 'statistictsct&ctscope=user' will just have to do the second query. Note that, in the second query, the JOIN is just to get a traversal "user participation" (that is to say, "in all countries"). $sql = 'SELECT /* SLOW_OK */ img_name , rev_user AS img_user_id , rev_user_text AS img_user_name , page_id AS img_page_id , "'.$country.'" AS img_wlm_country , TRIM(SUBSTR(c2.cl_sortkey, 1, INSTR(c2.cl_sortkey, "\n")-1)) AS img_wlm_id , rev_timestamp AS img_timestamp , IF (user_registration IS NULL,20051222000000,user_registration) AS user_first_rev /* there is no user_registration for users older than this */ , IF (c3.cl_to IS NULL,0,1) AS img_is_valid FROM image JOIN page ON page_namespace = 6 AND page_title = img_name JOIN categorylinks c1 ON page_id = c1.cl_from AND c1.cl_to = "Images_from_Wiki_Loves_Monuments_2011_in_'.$country.'" LEFT JOIN categorylinks c2 ON c1.cl_from = c2.cl_from AND c2.cl_to = "'.$category.'" /* not all have */ JOIN revision ON rev_page = page_id AND rev_parent_id = 0 JOIN user ON user_id = rev_user LEFT JOIN categorylinks c3 ON c3.cl_from = page_id and c3.cl_to = "Reviewed_images_from_Wiki_Loves_Monuments_2011_in_'.$country.'" '; $sql = 'SELECT st1.img_user_name AS user , COUNT(DISTINCT st1.img_name) AS n_images , SUM(img_is_valid) AS n_images_accepted , GROUP_CONCAT(DISTINCT st1.img_name SEPARATOR "[,]") AS images , COUNT(DISTINCT st1.img_wlm_id) AS n_wlm_ids , GROUP_CONCAT(DISTINCT st1.img_wlm_id SEPARATOR "[,]") AS wlm_ids , st2.n_countries AS n_countries , st2.countries AS countries , st2.n_images_all AS n_images_all , st2.images_all AS images_all , st2.n_wlm_ids_all AS n_wlm_ids_all , st2.wlm_ids_all AS wlm_ids_all , st2.n_images_accepted_all , IF(st1.user_first_rev<20110831230000,0,1) AS user_is_new FROM statisticsct2 st1 JOIN ( SELECT img_user_name , COUNT(DISTINCT img_wlm_country) AS n_countries , GROUP_CONCAT(DISTINCT img_wlm_country SEPARATOR "[,]") AS countries , COUNT(DISTINCT img_name) AS n_images_all , GROUP_CONCAT(DISTINCT img_name SEPARATOR "[,]") AS images_all , COUNT(DISTINCT img_wlm_id) AS n_wlm_ids_all , GROUP_CONCAT(DISTINCT img_wlm_id SEPARATOR "[,]") AS wlm_ids_all , SUM(img_is_valid) AS n_images_accepted_all FROM statisticsct2 GROUP BY img_user_name) st2 ON st1.img_user_name = st2.img_user_name WHERE st1.img_wlm_country IN ( '.$countries.' ) GROUP BY st1.img_user_name'; -- Nuno Tavares Wikimedia Portugal http://www.wikimedia.pt Imagine um mundo onde é dada a qualquer pessoa a possibilidade de ter livre acesso ao somatório de todo o conhecimento humano. É isso o que estamos a fazer. Participe também: http://www.wikimedia.pt Em 26-10-2011 22:10, Béria Lima escreveu: > I might be giving a dumb answer (apologies for that) but this don't > solve your problem? > > http://www.wikilovesmonuments.org.pt/tools/stats/api-bridge.php?ctscope=user&ctitem=&ctcountry=the_Netherlands&limit=1000 > <http://www.wikilovesmonuments.org.pt/tools/stats/api-bridge.php?ctscope=user&ctitem=&ctcountry=the_Netherlands&limit=1000> > _____ > /Béria Lima/ > Wikimedia Portugal <http://wikimedia.pt> > (351) 963 953 042 > > /Imagine um mundo onde é dada a qualquer pessoa a possibilidade de ter > livre acesso ao somatório de todo o conhecimento humano. É isso o que > estamos a fazer./ > > > On 26 October 2011 21:24, Andre Koopal <[email protected] > <mailto:[email protected]>> wrote: > > Hi, > > For the WLM in the Netherlands I want to make statistics from how many > monuments somebody made a picture. > > In principle the sql for this is simple: > > SELECT img_user_text, COUNT(img_user) FROM image > JOIN page ON img_name=page_title > JOIN categorylinks AS clA ON page_id=clA.cl_from > WHERE page_namespace=6 > AND page_is_redirect=0 > AND clA.cl_to='Images_from_Wiki_Loves_Monuments_2011_in_the_Netherlands' > and img_timestamp > '20110831220000' > and img_timestamp < '20110930220000' > GROUP BY img_user_text > ORDER BY COUNT(img_user_text) DESC; > > However, the uploader selected here is the last uploader, not the > first. Due > to this, one of the uploaders in this list is 'rotatebot'. > > I know the other uploaders are in a different table, but I don't see > an easy > way to build the query, certainly because there is not always something > in that table. > > Does anybody know a good way to solve this? > > Part of the reason I was looking for this is because of our quantity > price, > but fortunately the number of pictures of rotatebot is low enough not to > have effect on the top 3. > > Regards, > > Andre > > _______________________________________________ > Wiki Loves Monuments mailing list > [email protected] > <mailto:[email protected]> > https://lists.wikimedia.org/mailman/listinfo/wikilovesmonuments > http://www.wikilovesmonuments.eu > > > > > _______________________________________________ > Wiki Loves Monuments mailing list > [email protected] > https://lists.wikimedia.org/mailman/listinfo/wikilovesmonuments > http://www.wikilovesmonuments.eu _______________________________________________ Wiki Loves Monuments mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/wikilovesmonuments http://www.wikilovesmonuments.eu
