Untitled query #88911

SQL

select
  *


-- 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 * from logging_userindex as logging2
  inner join actor_logging as actor2
    on actor2.actor_id = logging2.log_actor
  inner join comment_logging as comment2
    on comment2.comment_id = logging2.log_comment_id
  where logging2.log_timestamp > '2024'
  and logging2.log_type = 'block'
  and logging2.log_action regexp '^(un)?block$'
) as query2 on user.user_name = replace(query2.log_title, '_', ' ')
  and query2.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 1;
SQL

Checking query status...
