bildirgec.org

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

"SELECT forum_topics.*,
  COALESCE(threads_table.hits,'0') as hits, COALESCE(posts_table.post_hits,'0') as post_hits,
  last_thread.date_updated, last_thread.username as updater_username

  FROM forum_topics

  LEFT JOIN (
    SELECT COUNT(*) AS hits, topic_id
    FROM forum_threads GROUP BY topic_id) AS threads_table
   ON threads_table.topic_id=forum_topics.id

  LEFT JOIN (
    SELECT SUM(posts_table_.hits) as post_hits, posts_table_.topic_id
    FROM (
      SELECT COUNT(*) AS hits, forum_posts.thread_id, forum_threads.topic_id
      FROM forum_posts
      LEFT JOIN forum_threads
      ON forum_threads.id=forum_posts.thread_id
      GROUP BY thread_id) AS posts_table_
    GROUP BY posts_table_.topic_id) AS posts_table
  ON posts_table.topic_id=forum_topics.id

  LEFT JOIN (
    SELECT last_thread_.topic_id, last_thread_.date_updated, users.username
    FROM
      (SELECT tt.topic_id, tt.date_updated, tt.last_updater
       FROM (
          SELECT forum_threads.topic_id, forum_threads.date_updated, forum_threads.last_updater
          FROM forum_threads
          ORDER BY date_updated DESC) AS tt
       GROUP BY tt.topic_id) as last_thread_
       LEFT JOIN users
       ON users.id=last_thread_.last_updater) as last_thread
  ON last_thread.topic_id=forum_topics.id

  WHERE is_active=1 AND is_deleted=0 ORDER BY forum_topics.id";