select
t.Id,
t.Slug,
t.Caption,
[case:[special:variant]|0 as Pinned|t.Pinned],
strftime('%d.%m.%Y %H:%M:%S', t.LastChanged, 'unixepoch') as TimeChanged,
t.PostCount,
t.ReadCount,
t.Limited,
(select
group_concat('<li><a href="/!userinfo/'||url_encode(nick)||'">'||html_encode(nick)||'</a></li>','')
from (
select nick, firstpost from ThreadPosters left join Users on userID = id where threadID = s.threadid order by firstPost
)
) as Posters,
[case:[special:limited]|
NULL
|
(select group_concat('<li><a href="/!userinfo/' ^|^| url_encode(nick) ^|^| '">' ^|^| html_encode(nick) ^|^| '</a></li>','') from LimitedAccessThreads left join Users on id = userid where threadID = s.threadid)
] as Invited,
(select group_concat('<li><a href="/' || url_encode(TT.tag) || '/" title="^[' || TT.tag || '^] ' || ifnull(html_encode(TG.description),'') || '">' || TT.tag || '</a></li>','')
from ThreadTags tt left join tags tg on tg.tag = tt.tag where TT.threadid = s.threadid
) as ThreadTags,
ifnull(Unread,0) as Unread,
ifnull(FirstUnread,0) as FirstUnread
from (
[case:[special:variant]|
select
id as threadid
from
threads
where
limited = 0
order by
LastChanged desc
|
select
threadid
from
LimitedAccessThreads
where
userid = ?3
order by
LastChanged desc
|
select
threadid
from
threadtags tt
where
tag = ?4 and limited = 0
order by
Pinned desc, LastChanged desc
|
select
LA.threadid
from
LimitedAccessThreads LA
left join
ThreadTags TT on TT.threadid = LA.threadid
where
userid = ?3 and TT.tag = ?4
order by
LA.LastChanged desc
]
limit ?1
offset ?2 ) as S
left join threads t on t.id = s.threadid
left join (select count() as Unread, min(UP7.PostID) as FirstUnread, UP7.threadid as ti from unreadposts UP7 where userid=?3 group by ti) on ti = s.threadid;