Beria, Andre,
It does, but not exactly that URL. Try this one:
http://toolserver.org/~ntavares/patrimonio/api/api.php?action=statisticsct&…
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=use…
<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 <andre(a)molens.org
<mailto:andre@molens.org>> 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
WikiLovesMonuments(a)lists.wikimedia.org
<mailto:WikiLovesMonuments@lists.wikimedia.org>
https://lists.wikimedia.org/mailman/listinfo/wikilovesmonuments
http://www.wikilovesmonuments.eu
_______________________________________________
Wiki Loves Monuments mailing list
WikiLovesMonuments(a)lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikilovesmonuments
http://www.wikilovesmonuments.eu