Forums

select 
  `security_blacklisted_country_hits`.*, 
  (
    select 
      display_name 
    from 
      account 
    where 
      account.id_account = security_blacklisted_country_hits.added_by
  ) as added_by_display_name, 
  (
    select 
      name 
    from 
      countries 
    where 
      countries.alpha_2 = security_blacklisted_country_hits.country_code
  ) as country_name 
from 
  `security_blacklisted_country_hits` 
where 
  `country_code` = 'us' 
limit 
  1 offset 0

select 
  * 
from 
  security_hosts_blacklist 
where 
  'ec2-54-80-8-44.compute-1.amazonaws.com' regexp replace(
    replace(
      replace(host_pattern, '-', '\\-'), 
      '.', 
      '\\.'
    ), 
    '*', 
    '.+'
  )

select 
  * 
from 
  security_ips_blacklist 
where 
  '54.80.8.44' regexp replace(
    replace(
      replace(ip, '-', '\\-'), 
      '.', 
      '\\.'
    ), 
    '*', 
    '.+'
  )

select 
  `categories`.*, 
  (
    select 
      slug 
    from 
      categories c2 
    where 
      c2.id_category = categories.parent_category
  ) as parent_category_slug, 
  (
    select 
      title 
    from 
      categories c2 
    where 
      c2.id_category = categories.parent_category
  ) as parent_category_title, 
  (
    select 
      group_concat(
        concat(c2.slug, '  ', c2.title) separator '
'
      ) 
    from 
      categories c2 
    where 
      c2.parent_category = categories.id_category 
      and (c2.visibility = 'public')
  ) as child_categories_list, 
  (
    select 
      count(p.id_post) 
    from 
      posts p 
    where 
      p.main_category = categories.id_category 
      and p.status = 'published' 
      and p.visibility = 'public'
  ) as posts_count, 
  (
    select 
      count(c.id_comment) 
    from 
      comments c, 
      posts p 
    where 
      c.status = 'published' 
      and c.id_post = p.id_post 
      and p.main_category = categories.id_category 
      and p.status = 'published' 
      and p.visibility = 'public'
  ) as comments_count 
from 
  `categories` 
where 
  `parent_category` = 'C11499905917613' 
  and visibility = 'public' 
order by 
  title

select 
  count(*) as total_rows 
from 
  posts 
where 
  status = 'published' 
  and visibility <> 'private' 
  and publishing_date <= '2018-04-23 00:38:00' 
  and (
    expiration_date = '0000-00-00 00:00:00' 
    or expiration_date > '2018-04-23 00:38:00'
  ) 
  and visibility = 'public' 
  and (
    posts.main_category in (
      select 
        id_category 
      from 
        categories 
      where 
        visibility = 'public' 
        or visibility = 'users' 
        or (
          visibility = 'level_based' 
          and '1' >= min_level
        )
    )
  ) 
  and (
    main_category = 'C11499905917613' 
    or id_post in (
      select 
        id_post 
      from 
        post_categories 
      where 
        post_categories.id_category = 'C11499905917613'
    )
  )

select 
  posts.id_post, 
  posts.parent_post, 
  posts.id_author, 
  posts.slug, 
  posts.title, 
  posts.excerpt, 
  posts.content, 
  posts.main_category, 
  posts.visibility, 
  posts.status, 
  posts.password, 
  posts.allow_comments, 
  posts.pin_to_home, 
  posts.pin_to_main_category_index, 
  posts.creation_date, 
  posts.creation_ip, 
  posts.creation_host, 
  posts.creation_location, 
  posts.publishing_date, 
  posts.expiration_date, 
  posts.comments_count, 
  posts.last_update, 
  posts.id_featured_image, 
  (
    select 
      views 
    from 
      post_views 
    where 
      post_views.id_post = posts.id_post
  ) as views, 
  (
    select 
      last_viewed 
    from 
      post_views 
    where 
      post_views.id_post = posts.id_post
  ) as last_viewed, 
  (
    select 
      concat(
        user_name, '\t', display_name, '\t', 
        email, '\t', level
      ) 
    from 
      account 
    where 
      account.id_account = posts.id_author
  ) as _author_data, 
  (
    select 
      concat(
        slug, '\t', title, '\t', visibility, 
        '\t', min_level
      ) 
    from 
      categories 
    where 
      categories.id_category = posts.main_category
  ) as _main_category_data, 
  (
    select 
      path 
    from 
      media 
    where 
      media.id_media = posts.id_featured_image
  ) as featured_image_path, 
  (
    select 
      thumbnail 
    from 
      media 
    where 
      media.id_media = posts.id_featured_image
  ) as featured_image_thumbnail, 
  (
    select 
      type 
    from 
      media 
    where 
      media.id_media = posts.id_featured_image
  ) as featured_media_type, 
  (
    select 
      group_concat(
        tag 
        order by 
          date_attached asc, 
          order_attached asc separator ','
      ) 
    from 
      post_tags 
    where 
      post_tags.id_post = posts.id_post
  ) as tags_list, 
  (
    select 
      group_concat(
        id_media 
        order by 
          date_attached asc, 
          order_attached asc separator ','
      ) 
    from 
      post_media 
    where 
      post_media.id_post = posts.id_post
  ) as media_list, 
  (
    select 
      group_concat(
        id_category 
        order by 
          date_attached asc, 
          order_attached asc separator ','
      ) 
    from 
      post_categories 
    where 
      post_categories.id_post = posts.id_post
  ) as categories_list, 
  (
    select 
      group_concat(
        id_account 
        order by 
          date_attached asc, 
          order_attached asc separator ','
      ) 
    from 
      post_mentions 
    where 
      post_mentions.id_post = posts.id_post
  ) as mentions_list, 
  (
    select 
      title 
    from 
      posts p2 
    where 
      p2.id_post = posts.parent_post
  ) as parent_post_title, 
  (
    select 
      slug 
    from 
      posts p2 
    where 
      p2.id_post = posts.parent_post
  ) as parent_post_slug, 
  (
    select 
      count(id_post) 
    from 
      posts p2 
    where 
      p2.parent_post = posts.id_post
  ) as children_count, 
  (
    select 
      c.creation_date 
    from 
      comments c 
    where 
      c.id_post = posts.id_post 
      and c.status = 'published' 
    order by 
      c.creation_date desc 
    limit 
      1
  ) as last_commented 
from 
  `posts` 
where 
  status = 'published' 
  and visibility <> 'private' 
  and publishing_date <= '2018-04-23 00:38:00' 
  and (
    expiration_date = '0000-00-00 00:00:00' 
    or expiration_date > '2018-04-23 00:38:00'
  ) 
  and visibility = 'public' 
  and (
    posts.main_category in (
      select 
        id_category 
      from 
        categories 
      where 
        visibility = 'public' 
        or visibility = 'users' 
        or (
          visibility = 'level_based' 
          and '1' >= min_level
        )
    )
  ) 
  and (
    main_category = 'C11499905917613' 
    or id_post in (
      select 
        id_post 
      from 
        post_categories 
      where 
        post_categories.id_category = 'C11499905917613'
    )
  ) 
order by 
  pin_to_main_category_index desc, 
  publishing_date desc 
limit 
  30 offset 0

select 
  '' as id_post 
union 
  (
    select 
      id_post 
    from 
      posts 
    where 
      main_category = 'C11499916345761' 
      and visibility = 'public' 
      and status = 'published' 
      and password = '' 
    order by 
      publishing_date desc 
    limit 
      1
  ) 
union 
  (
    select 
      id_post 
    from 
      posts 
    where 
      main_category = 'C11499906020341' 
      and visibility = 'public' 
      and status = 'published' 
      and password = '' 
    order by 
      publishing_date desc 
    limit 
      1
  ) 
union 
  (
    select 
      id_post 
    from 
      posts 
    where 
      main_category = 'C11499916087675' 
      and visibility = 'public' 
      and status = 'published' 
      and password = '' 
    order by 
      publishing_date desc 
    limit 
      1
  )