Untitled query #88907

SQL

select
  user2.user_name,
  user2.user_editcount,
  logging2.log_timestamp as log_timestamp1,
  actor2.actor_name as actor_name1,
  logging2.log_action as log_action1,
  logging2.log_params as log_params1,
  comment2.comment_text as comment_text1/*,
  query1.log_timestamp as log_timestamp2,
  query1.actor_name as actor_name2,
  query1.log_action as log_action2,
  query1.comment_text as comment_text2,
  query1.log_params as log_params2*/

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

/*left join (
  -- this is the query that fetches the ^(un)block$ events
  select
    user1.user_id as user_id,
    logging1.log_timestamp as log_timestamp,
    actor1.actor_name as actor_name,
    logging1.log_action as log_action,
    comment1.comment_text as comment_text,
    logging1.log_params as log_params
  
  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 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
  on query1.user_id = user2.user_id
  and query1.log_timestamp > logging2.log_timestamp
*/

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';
SQL

Checking query status...
