Untitled query #88909

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, '_', ' ')


-- logging2 fetches the [un]block event that comes soonest
-- after the event fetched by logging1
left join logging_userindex as logging2
  on user.user_name = replace(logging2.log_title, '_', ' ')
  and logging2.log_timestamp > logging1.log_timestamp
  and logging2.log_timestamp > '2024'
  and logging2.log_type = 'block'
  and logging2.log_action regexp '^(un)?block$'
left join actor_logging as actor2
  on actor2.actor_id = logging2.log_actor
left join comment_logging as comment2
  on comment2.comment_id = logging2.log_comment_id


-- 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

-- filtering the [re]block event
where logging1.log_timestamp > '2024'
and logging1.log_type = 'block'
SQL

Checking query status...
