(broken) Retrieve patrolled edits

SQL

use nlwiki_p;

#SELECT a.actor_name, l.* FROM logging l INNER JOIN actor a ON l.log_actor = a.actor_id

SELECT VERSION()

# Raw list
/*SELECT loguser.actor_name as loguser, log_timestamp, CONCAT(SUBSTRING(log_timestamp, 1, 8), ' ', SUBSTRING(log_timestamp, 9, 2), ':', SUBSTRING(log_timestamp, 11, 2), ':', SUBSTRING(log_timestamp, 13, 2)) as timestamp_human, log_title, edituser.actor_name as edituser, r.rev_timestamp, r.rev_id
FROM logging l
INNER JOIN revision r ON SUBSTRING(l.log_params, 26, 8) = r.rev_id # edit_id = rev_id
INNER JOIN actor loguser ON loguser.actor_id = log_actor
INNER JOIN actor edituser ON edituser.actor_id = rev_actor
WHERE log_type = 'patrol' #Enkel markeringen
AND log_namespace = '0' #Enkel hoofdnaamruimte
AND log_deleted = '0'
AND (edituser.actor_user IS NULL AND (edituser.actor_name LIKE '%:' OR edituser.actor_name LIKE '%.%')) #anonieme bewerkingen
ORDER BY log_timestamp DESC #De meest recente bovenaan*/


# Let's make some stats    
SELECT luser.actor_name, COUNT(1) as aantal
FROM logging as l
  #INNER JOIN revision as r ON SUBSTRING(l.log_params, 26, 8) = r.rev_id # edit_id = rev_id
  INNER JOIN actor as luser ON luser.actor_id = l.log_actor
  #INNER JOIN actor as euser ON euser.actor_id = r.rev_actor
  WHERE l.log_type = 'patrol' #Enkel markeringen
  AND l.log_namespace = '0' #Enkel hoofdnaamruimte
  AND l.log_deleted = '0'
  #AND (euser.actor_user IS NULL AND (euser.actor_name LIKE '%:' OR euser.actor_name LIKE '%.%')) #anonieme bewerkingen
GROUP BY luser.actor_name
ORDER BY COUNT(1) DESC
                  
/*Difference with the one below?
SELECT log_user_text, COUNT(1) as aantal
FROM logging
WHERE log_type = 'patrol' #Enkel markeringen
AND log_namespace = '0' #Enkel hoofdnaamruimte
AND log_deleted = '0'
GROUP BY log_user_text
ORDER BY COUNT(1) DESC*/


/*SELECT a.actor_name, COUNT(1) as aantal
FROM logging l INNER JOIN actor a ON l.log_actor = a.actor_id
WHERE l.log_type = 'patrol' #Enkel markeringen
AND l.log_namespace = '0' #Enkel hoofdnaamruimte
AND l.log_deleted = '0'
GROUP BY a.actor_name
ORDER BY COUNT(1) DESC*/

/*SELECT l.log_title, COUNT(1)
FROM logging l
WHERE l.log_timestamp LIKE '2019%'
AND l.log_type = 'patrol' #Enkel markeringen
AND l.log_namespace = '0' #Enkel hoofdnaamruimte
AND l.log_deleted = '0'
GROUP BY l.log_title
ORDER BY COUNT(1) DESC*/
SQL

Checking query status...
