CODEWITHSUNDEEP
phpmysqlforum
Kenny
Kenny

Reputation: 51

Get last entry from 2 combines tables

I'm building my own forum and I am stuck on one thing:

I got two tables:

  1. Forum_Topics
  2. Forum_Replies

Now I want a row with the 5 latest replies OR new generated topic.

How should I start/make my query?

EDIT: I should also see the topic name of the latest topic WITHOUT any replies. Getting the last 5 topics with replies is simple, but getting the topics without any, i can't seem to find a good solution.

Thanks for any help!

Upvotes: 1

Views: 64

Answers (2)

Theraot
Theraot

Reputation: 40285

For example if you have the table Forum_Topics that has the fields:

Id
Timestamp
Name

Where Id is the primary key and Timestamp is a filed (of type timestamp) that has the date and time of the entry.

And the table Forum_Replies that has the fields:

Id
Id_User
Text
Id_Forum_Topics
Timestamp

Where Id is the primary key, Timestamp is a filed (of type timestamp) that has the date and time of the entry.

Also Id_Forum_Topics would have a relationship with Forum_Topic.Id by means of a foreign key constraint. The fields Id_User and Text are not used in my answer and are present only for demonstration purposes.

Note: The value of the Timestamp can be either last update or creation time depending or your needs

Newest topics with no replies

By your comment I understand you want the latest topic created that has no replies.

To get the lastest topic that has no replies, you need to query for topics with no replies and then get order the results to get the newests.

This is hard to implement via COUNT and JOIN due to limitations of the engine. The best solution I know is to nest queries, like this:

SELECT Id, Name FROM Forum_Topics
WHERE Id NOT IN
(
    SELECT Id_Forum_Topics AS Id FROM Forum_Replies
)

Here we are asking for the topics that do not appear in the list of the topics of the replies. That is the list of the topics that do not appear in the list of the topics that has replies. That is the topics that has no replies.

Of course you can then use ORDER BY and LIMIT to get only the more recent ones:

SELECT Id, Name FROM Forum_Topics
WHERE Id NOT IN
(
    SELECT Id_Forum_Topics AS Id FROM Forum_Replies
)
ORDER BY Timestamp DESC
LIMIT 5

Note: The following is the answer to the question as I understood it before the comment.

Newest replies to a given topic

Then you can do this Query:

SELECT Id, Id_User, Text, Timestamp FROM Forum_Replies
WHERE Id_Forum_Topics = THE_ID_YOU_ARE_LOOKING_AT
ORDER BY Timestamp DESC
Limit 5

The query above will return the 5 newest replies to an entry in Forum_Topics identified by the Id THE_ID_YOU_ARE_LOOKING_AT.

You can also make a Join:

SELECT Id, Id_User, Text, Timestamp FROM Forum_Replies
JOIN Formun_Topics ON Forum_Replies.Id_Forum_Topics = Forum_Topics_Id

And of course query over that join:

SELECT Id, Id_User, Text, Timestamp FROM Forum_Replies
JOIN Formun_Topics ON Forum_Replies.Id_Forum_Topics = Forum_Topics_Id
WHERE Forum_Topics.Id = THE_ID_YOU_ARE_LOOKING_AT
ORDER BY Forum_Replies.Timestamp DESC
Limit 5

Note that this method depends entirely in the correctness of the values of the field Timestamp [you as developer are entitled to ensure that] And to provide the latest entries we use ORDER BY over that field in descending order (because newer means higher value) and the use LIMIT to set the maximum number of results we want.

Without having a field with the date and time of the entries there is no reliable way to assert what entries are newer [There are things you can do, but having the field is easier to develop and understand].

Topics with the newest replies

This is very similar the first query I presented, just query the field Id_Forum_Topics and don't restrict its value (don't put the WHERE clause):

SELECT Id_Forum_Topics FROM Forum_Replies
ORDER BY Timestamp DESC
Limit 5

How many replies has a topic

You can query like this:

SELECT (*) FROM Forum_Replies
WHERE Id_Forum_Topics = THE_ID_YOU_ARE_LOOKING_AT

Since you said "So if i make a new topic and nobody replies it doesn't show up as a new topic" I guess this can be useful, because you can use it to tell if a topic has no replies (or only one, or less than 5... etc).

Newest topics (regardless of replies)

By having a Timestamp in Forum_Topics, you can use in a similar way to get the newest topics, for example: 

SELECT Id, Name FROM Forum_Topics ORDER BY Timestamp DESC LIMIT 20

The query above would give you the newest 20 topics.

Finally, I suspect you want this to implement pagination, if so, you can:

1) Use the timestamp to indicate the start point from where to query:

SELECT Id, Id_User, Text FROM Forum_Replies
WHERE Id_Forum_Topics = THE_ID_YOU_ARE_LOOKING_AT
AND Timestamp > START_TIMESTAMP
ORDER BY Timestamp DESC
LIMIT 5

Where START_TIMESTAMP is the date and time from which the results are to be retrieved.

2) Set the offset to LIMIT:

SELECT Id, Id_User, Text FROM Forum_Replies
WHERE Id_Forum_Topics = THE_ID_YOU_ARE_LOOKING_AT
ORDER BY Timestamp DESC
LIMIT 10, 5

In this case we are saying we want the 5 newest entries (as usual), but after the 10 newest ones.

Upvotes: 1

Prateek Shukla
Prateek Shukla

Reputation: 595

Select * from Forum_Replies left join Forum_Topics on Forum_Replies.topicId = Forum_Topics.id order by Forum_Replies.datefield desc limit 5.

Upvotes: 0

Related Questions