Untitled query #88871

SQL

select
  user1.user_name as username,
  query2.log_timestamp as block1_timestamp,
  query2.actor_name as block1_admin,
  (query2.log_params like '%infinity%') as block1_indef,
  (query2.log_params like '%"sitewide";b:1%') as block1_sitewide,
  (query2.log_params like '%nocreate%') as block1_acb,
  query2.comment_text as block1_comment,
  query2.log_params as block1_params,
  logging1.log_timestamp as block2_timestamp,
  actor1.actor_name as block2_admin,
  comment1.comment_text as block2_comment,
  logging1.log_params as block2_params,
  user1.user_editcount as user_editcount-- ,
 -- row_number() over (partition by 

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

inner join (
  select user_id, log_timestamp, actor_name, comment_text, log_params
  
  from ( 
    select
      *,
      row_number() over (partition by log_id order by logging2.log_timestamp desc) as row_number
    
    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
    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 query3
  
  where query3.row_number = 1
  
) as query2
  on query2.user_id = user1.user_id
  and logging1.log_timestamp > query2.log_timestamp
  

where logging1.log_timestamp > '2024'
and logging1.log_type = 'block'
and logging1.log_action = 'block';

-- limit 1;



-- order by logging1.log_timestamp desc
SQL

Checking query status...
