Untitled query #88919

SQL

select
  query1.user_name as user_name,
  query1.user_editcount as user_editcount,
  
  query2.log_timestamp as log1_timestamp,
  query2.actor_name as log1_admin,
  query2.log_action as log1_action,
  query2.log_params as log1_params,
  query2.comment_text as log1_comment,
  
  query1.log_timestamp as log2_timestamp,
  query1.actor_name as log2_admin,
  query1.log_action as log2_action,
  query1.comment_text as log2_comment,
  query1.log_params as log2_params,
  
  row_number() over (partition by user1.user_id order by logging1.log_timestamp asc) as row_number

from (
  select * from logging_logindex 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 as user1
    on user1.user_name = replace(logging1.log_title, '_', ' ')
  
  where logging1.log_timestamp > '2024'
  and logging1.log_type = 'block'
  
  -- searching for both unblocks *and* new blocks:
  -- if there is no unblock action (ie., because the block was time-limited), then
  -- searching for unblock actions alone could potentially return an unblock summary
  -- from a potential *different future block* (which could be for an unrelated reason).
  -- therefore, if there's a `block` event before an `unblock` event, we know to filter
  -- that unblock event out.
  and logging1.log_action regexp '^(un)?block$'
) as query1


-- use a right join, so we (hopefully) get all of the original block events, even if there wasn't
-- a specific unblock for it (ie., if the block auto-expired)
right join (
  select user_id, log_timestamp, actor_name, comment_text, log_action, log_params
  
  from ( 
    select
      *,
      row_number() over (partition by user2.user_id order by logging2.log_timestamp desc) as row_number
    
    from logging_logindex 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
    inner join user as user2
      on user2.user_name = replace(logging2.log_title, '_', ' ')
    left join block_target as block_target2
      on block_target2.bt_user = user2.user_id
    left join block as block2
      on block2.bl_target = block_target2.bt_id
    
    where logging2.log_timestamp > '2024'
    and block2.bl_id is null
    and logging2.log_type = 'block'
    and logging2.log_action regexp '^(re)?block$'
    and logging2.log_params not like '%noautoblock%'
    and convert(comment2.comment_text using utf8) regexp 'spam|promo|advert'
  ) as query1
  
  where query1.row_number = 1
  
) as query2
  on query2.user_id = query1.user_id
  and query1.log_timestamp > query2.log_timestamp

limit 1;



-- order by logging1.log_timestamp desc
SQL

Checking query status...
