CODEWITHSUNDEEP
phpmysqlsqlcodeignitersql-order-by
matthiasdv
matthiasdv

Reputation: 1176

SQL: Forum Order by behaviour (in Codeigniter)

Here's the situation:

In my database i have a table with threads, and a table with replies. Both have a Timestamp field.

Now i am developing a forum and wish to order threads in the following manner:

I do not know how to combine these 2 in one statement.

My query as it is now:

SELECT `PK_ThreadID`, `Title`, `tblUsers`.`Username`, `tblThread`.`Date`, count(tblReply.FK_ThreadID) AS number_replies FROM (`tblThread`) 
JOIN `tblUsers` ON `tblUsers`.`PK_UserID` = `tblThread`.`FK_UserID` 
LEFT JOIN `tblReply` ON `tblReply`.`FK_ThreadID` = `tblThread`.`PK_ThreadID` 
WHERE `isExpertQuestion` = 0 AND `isPublic` = 1 
GROUP BY `PK_ThreadID` 
ORDER BY max(tblReply.Date)` desc   

//Here it only orders by reply date, so threads with no replies appear at the bottom

How do i achieve the ordering i want in this query?

Upvotes: 0

Views: 222

Answers (1)

Rahul
Rahul

Reputation: 77876

Like this probably:

SELECT `PK_ThreadID`, `Title`, 
`tblUsers`.`Username`, 
`tblThread`.`Date`, 
count(tblReply.FK_ThreadID) AS number_replies 
FROM (`tblThread`) 
JOIN `tblUsers` ON `tblUsers`.`PK_UserID` = `tblThread`.`FK_UserID` 
LEFT JOIN `tblReply` ON `tblReply`.`FK_ThreadID` = `tblThread`.`PK_ThreadID` 
WHERE `isExpertQuestion` = 0 AND `isPublic` = 1 
GROUP BY `PK_ThreadID` 
ORDER BY 
CASE WHEN COUNT(tblReply.FK_ThreadID) > 0 THEN tblReply.Timestamp 
     WHEN COUNT(tblReply.FK_ThreadID) = 0 OR tblReply.FK_ThreadID IS NULL
THEN tblThread.Timestamp 
END DESC

Upvotes: 1

Related Questions