Untitled query #75408

SQL

#show tables;
/*
abuse_filter
abuse_filter_action
abuse_filter_history
abuse_filter_log
*/
/*
select af_id,af_public_comments from abuse_filter
where af_enabled = 1 and af_hidden = 0 and af_deleted = 0 and af_hit_count > 0
;
*/


/*
select   subquery.hit_count ,subquery.afl_filter_id,subquery.af_public_comments from (
  select count(afl_filter_id) as hit_count , afl_filter_id,abuse_filter.af_public_comments from abuse_filter_log 
  inner join abuse_filter on abuse_filter.af_id = abuse_filter_log.afl_filter_id
  WHERE afl_timestamp >= NOW() - INTERVAL 30 DAY
  # abuse_filter is enabled not hidden and not deleted and has hit 
  and abuse_filter.af_enabled = 1 and abuse_filter.af_hidden = 0 and abuse_filter.af_deleted = 0 and abuse_filter.af_hit_count > 0
  group by afl_filter_id
)  AS subquery;
*/


SELECT
  subquery.hit_count,
  subquery.afl_filter_id,
  subquery.af_public_comments,
  GROUP_CONCAT(example) AS custom_example_list
FROM (
  SELECT
    COUNT(afl_filter_id) AS hit_count,
    afl_filter_id,
    abuse_filter.af_public_comments
  FROM
    abuse_filter_log 
  INNER JOIN
    abuse_filter ON abuse_filter.af_id = abuse_filter_log.afl_filter_id
  WHERE
    afl_timestamp >= NOW() - INTERVAL 30 DAY
    AND abuse_filter.af_enabled = 1
    AND abuse_filter.af_hidden = 0
    AND abuse_filter.af_deleted = 0
    AND abuse_filter.af_hit_count > 0
  GROUP BY
    afl_filter_id, abuse_filter.af_public_comments
) AS subquery
LEFT JOIN (
  SELECT 
    afl_filter_id,
    af_public_comments AS example
  FROM 
    abuse_filter_log
  WHERE 
    afl_timestamp >= NOW() - INTERVAL 30 DAY
  ORDER BY 
    RAND() 
  LIMIT 10
) AS custom_examples ON subquery.afl_filter_id = custom_examples.afl_filter_id
GROUP BY
  subquery.hit_count,
  subquery.afl_filter_id,
  subquery.af_public_comments;


select * from abuse_filter_log
WHERE afl_timestamp >= NOW() - INTERVAL 30 DAY
limit 10;
SQL

Checking query status...
