Untitled query #88909

SQL

select
  user.user_name                                   as 'username',
  user.user_editcount                              as 'edits',
  
  date_format(
    logging1.log_timestamp, '%Y-%m-%d %H:%i:%s'
  )                                                as 'block_timestamp',
  actor1.actor_name                                as 'block_admin',
  (logging1.log_action = 'reblock')                as 'reblock?',
  (logging1.log_params like '%infinity%')          as 'indef?',
  (logging1.log_params like '%"sitewide";b:1%')    as 'sitewide?',
  (logging1.log_params like '%nocreate%')          as 'ACB?',
  (logging1.log_params like '%nousertalk%')        as 'TPD?',
  (logging1.log_params like '%noemail%')           as 'EMD?',
  comment1.comment_text                            as 'block_comment',
  logging1.log_params                              as 'block_params',
  
  if(
    logging_subquery.log_action = 'unblock',
    date_format(
      logging_subquery.log_timestamp,
      '%Y-%m-%d %H:%i:%s'
    ), null
  )                                                as 'unblock_timestamp',
  if(
    logging_subquery.log_action = 'unblock',
    logging_subquery.actor_name, null
  )                                                as 'unblock_admin',
  if(
    logging_subquery.log_action = 'unblock',
    logging_subquery.comment_text, null
  )                                                as 'unblock_comment',
  if(
    logging_subquery.log_action = 'unblock',
    logging_subquery.log_params, null
  )                                                as 'unblock_params'


-- logging1 fetches the most recent [re]block event
-- that matches the given filters
from logging_userindex as logging1
inner join actor_logging as actor1
  on actor1.actor_id = logging1.log_actor
inner join comment_logging as comment1
  on comment1.comment_id = logging1.log_comment_id
inner join user
  on user.user_name = replace(logging1.log_title, '_', ' ')


-- left join on the block table
-- so we can filter out users that are currently blocked
left join block_target
  on block_target.bt_user = user.user_id
left join block
  on block.bl_target = block_target.bt_id


-- logging2 fetches the [un]block event that comes soonest
-- after the event fetched by logging1
left join (
  select
    log_timestamp, log_title, log_action, log_params, actor_name,
    comment_text
  
  from logging_userindex /* as logging2 */
  
  inner join actor_logging
    on actor_id = log_actor
  inner join comment_logging
    on comment_id = log_comment_id
  
  where log_timestamp > '2024'
  and log_type = 'block'
  and log_action regexp '^(un)?block'
  
) as logging_subquery  
  on user.user_name = replace(logging_subquery.log_title, '_', ' ')
  and logging_subquery.log_timestamp > logging1.log_timestamp


-- filtering the [re]block event
where logging1.log_timestamp > '2024'
and block.bl_id is null
and logging1.log_type = 'block'
and logging1.log_action regexp '^(re)?block$'
and logging1.log_params not like '%noautoblock%'
and convert(comment1.comment_text using utf8) regexp 'spam|promo|advert'


limit 10;
SQL

Checking query status...
