Skip to content

Instantly share code, notes, and snippets.

@vetalt

vetalt/gist:2200c0e3b3c7d61d108b

Created February 18, 2016 19:02
Show Gist options
  • Save vetalt/2200c0e3b3c7d61d108b to your computer and use it in GitHub Desktop.
Save vetalt/2200c0e3b3c7d61d108b to your computer and use it in GitHub Desktop.
Download ZIP
getLatestByCategories
Raw
gistfile1.txt
(
select
ft1.parent_category_id,
ft1.forum_topic_id,
ft1.subject,
ft1.url,
ft1.post_count,
ft1.last_forum_topic_create_date,
case when ft1.is_locked then 'locked' when tft.track_forum_topic_id is not null then 'tracked' else 'none' end as status,
if (
ft1.create_date >= ifnull(
'2014-07-30 07:13:46',
date_sub(
curdate(),
interval 7 day
)
)
and ft1.forum_topic_id not in (0),
1,
0
) as new
from
forum_topic ft1
join user u1 on ft1.user_id = u1.user_id
and u1.is_content_hidden = 0
join forum_topic ft2 on ft1.last_forum_topic_id = ft2.forum_topic_id
join user u2 on ft2.user_id = u2.user_id
and u2.is_content_hidden = 0
and (
u2.main_image_hash is not null
or u2.create_date < date_sub(
curdate(),
interval 30 day
)
)
left join track_forum_topic tft on ft1.forum_topic_id = tft.forum_topic_id
and tft.user_id = ifnull(41, 0)
where
ft1.parent_category_id = 10
and ft1.is_parent_topic = 1
and ft1.post_count > 3
and ft1.is_live = 1
order by
ft1.last_forum_topic_create_date desc
limit
3
)
union all
(
select
ft1.parent_category_id,
ft1.forum_topic_id,
ft1.subject,
ft1.url,
ft1.post_count,
ft1.last_forum_topic_create_date,
case when ft1.is_locked then 'locked' when tft.track_forum_topic_id is not null then 'tracked' else 'none' end as status,
if (
ft1.create_date >= ifnull(
'2014-07-30 07:13:46',
date_sub(
curdate(),
interval 7 day
)
)
and ft1.forum_topic_id not in (0),
1,
0
) as new
from
forum_topic ft1
join user u1 on ft1.user_id = u1.user_id
and u1.is_content_hidden = 0
join forum_topic ft2 on ft1.last_forum_topic_id = ft2.forum_topic_id
join user u2 on ft2.user_id = u2.user_id
and u2.is_content_hidden = 0
and (
u2.main_image_hash is not null
or u2.create_date < date_sub(
curdate(),
interval 30 day
)
)
left join track_forum_topic tft on ft1.forum_topic_id = tft.forum_topic_id
and tft.user_id = ifnull(41, 0)
where
ft1.parent_category_id = 6
and ft1.is_parent_topic = 1
and ft1.post_count > 3
and ft1.is_live = 1
order by
ft1.last_forum_topic_create_date desc
limit
3
)
union all
(
select
ft1.parent_category_id,
ft1.forum_topic_id,
ft1.subject,
ft1.url,
ft1.post_count,
ft1.last_forum_topic_create_date,
case when ft1.is_locked then 'locked' when tft.track_forum_topic_id is not null then 'tracked' else 'none' end as status,
if (
ft1.create_date >= ifnull(
'2014-07-30 07:13:46',
date_sub(
curdate(),
interval 7 day
)
)
and ft1.forum_topic_id not in (0),
1,
0
) as new
from
forum_topic ft1
join user u1 on ft1.user_id = u1.user_id
and u1.is_content_hidden = 0
join forum_topic ft2 on ft1.last_forum_topic_id = ft2.forum_topic_id
join user u2 on ft2.user_id = u2.user_id
and u2.is_content_hidden = 0
and (
u2.main_image_hash is not null
or u2.create_date < date_sub(
curdate(),
interval 30 day
)
)
left join track_forum_topic tft on ft1.forum_topic_id = tft.forum_topic_id
and tft.user_id = ifnull(41, 0)
where
ft1.parent_category_id = 13
and ft1.is_parent_topic = 1
and ft1.post_count > 3
and ft1.is_live = 1
order by
ft1.last_forum_topic_create_date desc
limit
3
)
union all
(
select
ft1.parent_category_id,
ft1.forum_topic_id,
ft1.subject,
ft1.url,
ft1.post_count,
ft1.last_forum_topic_create_date,
case when ft1.is_locked then 'locked' when tft.track_forum_topic_id is not null then 'tracked' else 'none' end as status,
if (
ft1.create_date >= ifnull(
'2014-07-30 07:13:46',
date_sub(
curdate(),
interval 7 day
)
)
and ft1.forum_topic_id not in (0),
1,
0
) as new
from
forum_topic ft1
join user u1 on ft1.user_id = u1.user_id
and u1.is_content_hidden = 0
join forum_topic ft2 on ft1.last_forum_topic_id = ft2.forum_topic_id
join user u2 on ft2.user_id = u2.user_id
and u2.is_content_hidden = 0
and (
u2.main_image_hash is not null
or u2.create_date < date_sub(
curdate(),
interval 30 day
)
)
left join track_forum_topic tft on ft1.forum_topic_id = tft.forum_topic_id
and tft.user_id = ifnull(41, 0)
where
ft1.parent_category_id = 9
and ft1.is_parent_topic = 1
and ft1.post_count > 3
and ft1.is_live = 1
order by
ft1.last_forum_topic_create_date desc
limit
3
)
union all
(
select
ft1.parent_category_id,
ft1.forum_topic_id,
ft1.subject,
ft1.url,
ft1.post_count,
ft1.last_forum_topic_create_date,
case when ft1.is_locked then 'locked' when tft.track_forum_topic_id is not null then 'tracked' else 'none' end as status,
if (
ft1.create_date >= ifnull(
'2014-07-30 07:13:46',
date_sub(
curdate(),
interval 7 day
)
)
and ft1.forum_topic_id not in (0),
1,
0
) as new
from
forum_topic ft1
join user u1 on ft1.user_id = u1.user_id
and u1.is_content_hidden = 0
join forum_topic ft2 on ft1.last_forum_topic_id = ft2.forum_topic_id
join user u2 on ft2.user_id = u2.user_id
and u2.is_content_hidden = 0
and (
u2.main_image_hash is not null
or u2.create_date < date_sub(
curdate(),
interval 30 day
)
)
left join track_forum_topic tft on ft1.forum_topic_id = tft.forum_topic_id
and tft.user_id = ifnull(41, 0)
where
ft1.parent_category_id = 4
and ft1.is_parent_topic = 1
and ft1.post_count > 3
and ft1.is_live = 1
order by
ft1.last_forum_topic_create_date desc
limit
3
)
union all
(
select
ft1.parent_category_id,
ft1.forum_topic_id,
ft1.subject,
ft1.url,
ft1.post_count,
ft1.last_forum_topic_create_date,
case when ft1.is_locked then 'locked' when tft.track_forum_topic_id is not null then 'tracked' else 'none' end as status,
if (
ft1.create_date >= ifnull(
'2014-07-30 07:13:46',
date_sub(
curdate(),
interval 7 day
)
)
and ft1.forum_topic_id not in (0),
1,
0
) as new
from
forum_topic ft1
join user u1 on ft1.user_id = u1.user_id
and u1.is_content_hidden = 0
join forum_topic ft2 on ft1.last_forum_topic_id = ft2.forum_topic_id
join user u2 on ft2.user_id = u2.user_id
and u2.is_content_hidden = 0
and (
u2.main_image_hash is not null
or u2.create_date < date_sub(
curdate(),
interval 30 day
)
)
left join track_forum_topic tft on ft1.forum_topic_id = tft.forum_topic_id
and tft.user_id = ifnull(41, 0)
where
ft1.parent_category_id = 3
and ft1.is_parent_topic = 1
and ft1.post_count > 3
and ft1.is_live = 1
order by
ft1.last_forum_topic_create_date desc
limit
3
)
union all
(
select
ft1.parent_category_id,
ft1.forum_topic_id,
ft1.subject,
ft1.url,
ft1.post_count,
ft1.last_forum_topic_create_date,
case when ft1.is_locked then 'locked' when tft.track_forum_topic_id is not null then 'tracked' else 'none' end as status,
if (
ft1.create_date >= ifnull(
'2014-07-30 07:13:46',
date_sub(
curdate(),
interval 7 day
)
)
and ft1.forum_topic_id not in (0),
1,
0
) as new
from
forum_topic ft1
join user u1 on ft1.user_id = u1.user_id
and u1.is_content_hidden = 0
join forum_topic ft2 on ft1.last_forum_topic_id = ft2.forum_topic_id
join user u2 on ft2.user_id = u2.user_id
and u2.is_content_hidden = 0
and (
u2.main_image_hash is not null
or u2.create_date < date_sub(
curdate(),
interval 30 day
)
)
left join track_forum_topic tft on ft1.forum_topic_id = tft.forum_topic_id
and tft.user_id = ifnull(41, 0)
where
ft1.parent_category_id = 11
and ft1.is_parent_topic = 1
and ft1.post_count > 3
and ft1.is_live = 1
order by
ft1.last_forum_topic_create_date desc
limit
3
)
union all
(
select
ft1.parent_category_id,
ft1.forum_topic_id,
ft1.subject,
ft1.url,
ft1.post_count,
ft1.last_forum_topic_create_date,
case when ft1.is_locked then 'locked' when tft.track_forum_topic_id is not null then 'tracked' else 'none' end as status,
if (
ft1.create_date >= ifnull(
'2014-07-30 07:13:46',
date_sub(
curdate(),
interval 7 day
)
)
and ft1.forum_topic_id not in (0),
1,
0
) as new
from
forum_topic ft1
join user u1 on ft1.user_id = u1.user_id
and u1.is_content_hidden = 0
join forum_topic ft2 on ft1.last_forum_topic_id = ft2.forum_topic_id
join user u2 on ft2.user_id = u2.user_id
and u2.is_content_hidden = 0
and (
u2.main_image_hash is not null
or u2.create_date < date_sub(
curdate(),
interval 30 day
)
)
left join track_forum_topic tft on ft1.forum_topic_id = tft.forum_topic_id
and tft.user_id = ifnull(41, 0)
where
ft1.parent_category_id = 8
and ft1.is_parent_topic = 1
and ft1.post_count > 3
and ft1.is_live = 1
order by
ft1.last_forum_topic_create_date desc
limit
3
)
union all
(
select
ft1.parent_category_id,
ft1.forum_topic_id,
ft1.subject,
ft1.url,
ft1.post_count,
ft1.last_forum_topic_create_date,
case when ft1.is_locked then 'locked' when tft.track_forum_topic_id is not null then 'tracked' else 'none' end as status,
if (
ft1.create_date >= ifnull(
'2014-07-30 07:13:46',
date_sub(
curdate(),
interval 7 day
)
)
and ft1.forum_topic_id not in (0),
1,
0
) as new
from
forum_topic ft1
join user u1 on ft1.user_id = u1.user_id
and u1.is_content_hidden = 0
join forum_topic ft2 on ft1.last_forum_topic_id = ft2.forum_topic_id
join user u2 on ft2.user_id = u2.user_id
and u2.is_content_hidden = 0
and (
u2.main_image_hash is not null
or u2.create_date < date_sub(
curdate(),
interval 30 day
)
)
left join track_forum_topic tft on ft1.forum_topic_id = tft.forum_topic_id
and tft.user_id = ifnull(41, 0)
where
ft1.parent_category_id = 14
and ft1.is_parent_topic = 1
and ft1.post_count > 3
and ft1.is_live = 1
order by
ft1.last_forum_topic_create_date desc
limit
3
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment