I am building a basic forum with CodeIgniter and I am having trouble with two MySQL queries.
Here are my tables:
mysql> describe forum_posts;
+----------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+-------------------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| topic_id | int(11) | YES | MUL | NULL | |
| user_id | int(11) | YES | MUL | NULL | |
| post | text | YES | | NULL | |
| date | timestamp | YES | | CURRENT_TIMESTAMP | |
| ip | varchar(15) | YES | | NULL | |
+----------+------------------+------+-----+-------------------+----------------+
6 rows in set (0.01 sec)
mysql> describe forum_topics;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| category_id | int(11) | YES | MUL | NULL | |
| user_id | int(11) | YES | MUL | NULL | |
| name | varchar(100) | YES | | NULL | |
| importance | tinyint(2) | YES | | 0 | |
+-------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe forum_categories;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| order | tinyint(2) | YES | | 0 | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
1) I want to select all categories and their respective latest posts
2) I want to select all topics from category with id X and show the last post whitin this topic.
This is what I've done so far
(1):
SELECT
C.*,
T.name AS topic_name,
T.id AS topic_id,
P.date AS post_date
FROM forum_categories AS C
LEFT JOIN
(
SELECT id, category_id, name
FROM forum_topics
ORDER BY id DESC
)
T ON C.id = T.category_id
LEFT JOIN
(
SELECT id, topic_id, date
FROM forum_posts
ORDER BY DATE(date) DESC
)
P ON T.id = P.topic_id
GROUP BY C.id
ORDER BY DATE(P.date) DESC
(2):
SELECT
T.*,
P.topic_id,
P.id AS post_id,
P.user_id,
P.date,
users.username
FROM forum_topics AS T
LEFT JOIN
(
SELECT id, topic_id, user_id, date
FROM forum_posts
ORDER BY id DESC
)
P ON P.topic_id = T.id
LEFT JOIN
users ON P.user_id = users.id
WHERE T.category_id = '1'
GROUP BY P.topic_id
ORDER BY
T.id DESC,
P.id DESC
LIMIT 10
Basically my problem is that I cannot first ORDER the subqueries and then GROUP them.
Also I am not sure if these quesries are efficient enough. I am working about the subqueries. Are they gonna be slow?
After many hours of breaking my head into the wall I think I found the solution for (1) and now it will be easier to find solution for (2).
SELECT
C.id,
C.name AS category_name,
C.order AS category_order,
T.*
FROM forum_categories AS C
LEFT JOIN
(
SELECT
T.category_id,
T.id AS topic_id,
T.name AS topic_name,
P.user_id,
P.username,
P.date
FROM forum_topics AS T
LEFT JOIN
(
SELECT
P2.id AS post_id,
P2.topic_id,
P2.user_id,
P2.date,
U.username
FROM forum_posts AS P2
LEFT JOIN users AS U ON P2.user_id = U.id
ORDER BY P2.id DESC
)
P ON P.topic_id = T.id
ORDER BY P.post_id DESC
)
T ON T.category_id = C.id
GROUP BY C.id
Edit: Fixed a little bug in the (1) query and here is the (2) for those which want to accomplish the same as me:
SELECT
T.*,
P.*
FROM forum_topics AS T
LEFT JOIN
(
SELECT
P2.id AS post_id,
P2.topic_id,
P2.user_id,
P2.date,
U.username
FROM forum_posts AS P2
LEFT JOIN users U ON U.id = P2.user_id
ORDER BY P2.id DESC
)
P ON P.topic_id = T.id
WHERE T.category_id = '1'
GROUP BY P.topic_id
ORDER BY P.topic_id DESC